Microsoft Excel offers essential functions to help businesses excel. Unknown to many, this highly capable piece of software offers countless capabilities that can be extremely useful to companies. Continue reading to learn more about various formulas that can be utilized to help your business grow.
How to Use Excel Formulas
As a cell is selected, a function can be typed into the actual cell itself or into the formula bar found at the top of the sheet. Please note that every formula will start with an equals sign. Following that, coordinates of the cells can be selected or typed to create the formula required. When used correctly, these functions can effectively create spreadsheets that can be updated automatically as inputted data changes.
Below are a few of the more commonly used formulas that may be useful to you and your business operations.
=SUM
This formula gives the total value of a selected range of cells, allowing ease to add up long lists of values. For example, if there was a list of numbers inserted in Column C, beginning at Row 3 and ending at Row 35, one can type =SUM(C3:C35) to calculate the total, which would then be displayed in the cell that the formula was assigned to.
=AVERAGE
This function will result in the average of the cells that are listed in the formula. For instance, =AVERAGE(A1,B2,C3) will provide the average of values found in cells A1, B2, and C3.
=CEILING and =FLOOR
These functions will round numbers up (ceiling) or down (floor) to the nearest multiple of significance that is inputted. For instance, if one inserted 51.06 in cell D8, utilizing =CEILING(D8,5) would round up to 55 in the cell the formula was applied to. Similarly, =FLOOR(D8,5) would round down to 50.
=CONCATENATE
A feature of Excel that is unknown to many is the use of text-based data. The =CONCATENATE function allows the text in selected cells to be inputted together. For example, if A1 read Comprehensive data backup and B1 read is a necessity for a modern business, the function =CONCATENATE(A1, “ “, B1) would produce a cell that reads Comprehensive data backup is a necessity for a modern business. The same result will be accomplished if =CONCATENATE(A1&” “&B1) is utilized.
=NOW()
By entering this formula into a cell, the system’s current date and time will be inputted, allowing one to keep track of when data was entered.
=TODAY(), et alia.
There are additional functions that may be entered using the system’s current information:
- =TODAY() provides the current date
- =DAY(TODAY()) provides the current day of the month
- =MONTH(TODAY()) provides the month, represented as a number between 1 and 12
=TIME, et alia.
There are also various functions that allow one to track the time:
- =TIME() allows the time to be entered, formatted as a serial number in the hours, minutes, and seconds, that will be converted into a typical time format. For example, =TIME(18,28,45) would read as 6:28 PM.
- =HOUR(NOW()) produces the current hour as a value between 0 and 23 (12 AM to 11 PM).
- =MINUTE(NOW()) produces the current minute as a value between 0 to 59.
- =SECOND(NOW()) produces the current second as a value between 0 to 59.
=VLOOKUP() and =HLOOKUP()
These formulas, short for vertical lookup and horizontal lookup, allow one to identify specific values in one column or row within the sheet. For example, a table can be created outlining the top selling products or services, the gross sales of each, the total cost that it can be, the employee with the highest sales, and the least selling employee. By utilizing =VLOOKUP() or =HLOOKUP(), you can select the range of cells to identify the relationship between the data points.
=IF
This function can be utilized to determine if a condition is true or false as it relates to the data. If one wants to determine if the operating threshold has been reached, =IF(D6>5,000,”Yes”,”No”) with your current revenue in D6 can be inserted, allowing an easier way of identifying information at a glance.
=COUNTIF and =SUMIF
There may be times where a tally or sum is needed. =COUNTIF and =SUMIF make it a lot easier to accomplish this task.
- =COUNTIF() allows one to tally cells that match the conditions set by selecting the range to be examined and providing the condition that needs to be met. If one needed to tally up the number of customers in certain cities, =COUNTIF(A1:A100, “Smallville”) can be inputted to get the answer.
- =SUMIF() adds the cells up. Continuing with the previous example, one could enter =SUMIF(A1:A:100,”Smallville”, E:E) to get the total.
To learn more about Microsoft and their business solutions, give us a call at 954.474.2204
