Tip of the Week: Explaining Excel Formulas

Just under its surface, Microsoft Excel offers a massive variety of functions and features…far more than we could ever realistically cover in this blog. However, there are a few that stand out as relatively essential, particularly to businesses. Let’s go through these functions, starting with how to use them.

How Excel Formulas Work

Once you have a cell selected, you can type one of these formulas into the cell or the formula bar. Keep in mind that all Excel formulas begin with an equals sign, before the type of formula is specified and the appropriate data points are identified. If properly formatted, your spreadsheet can thereby be automated to do a huge variety of tasks based on the data you’ve input.

Here are some common formulas that you could find useful in your business’ operations.

=SUM()

This allows you to total up the value of the cells you’ve selected, making it far simpler to add up long lists of numbers. Let’s say you had a list made up in Column B, beginning at Row 2 and ending with Row 46. To total these numbers, you’d type up =SUM(B2:B46), and that cell would display the total as a result.

=AVERAGE()

In the same vein, =AVERAGE does the same thing, except it produces the average amongst those selected cells. =AVERAGE(X1,Y2,Z3) would give you the average of X1, Y2, and Z3.

=CEILING() and =FLOOR()

These functions allow you to round numbers to the nearest multiple of significance that you’ve established. Dictating =CEILING(F2,5) with F2 being populated with 368 would round up to 370. On the other end of the spectrum, =FLOOR(F2,5) would round down to 365.

=CONCATENATE()

Excel can also track changes in text too, with =CONCATENATE allowing you to tie different text strings together. For instance, if G7 read The 3-2-1 Backup Rule and K12 read helps to ensure your data backup is safe, entering =CONCATENATE(G7,” “,K12) would tie the phrases together and give you The 3-2-1 Backup Rule helps to ensure your data backup is safe. You can also format the formula as =CONCATENATE(G7&” “&K12) for the same results.

=NOW()

This formula will populate a cell with the current date and time, which helps you keep track of when your data was put into the spreadsheet.

=TODAY(), et alia.

There are a number of functions that do a similar thing to =NOW(), such as:

• =TODAY(), which populates a cell with the current date
• =DAY(TODAY()), which populates a cell with the current day of the month
• =MONTH(TODAY()), which populates a cell with the current month, represented as a numerical value between 1 and 12

=TIME(), et alia.

In a similar vein, various functions allow you to track the exact time that data has been added to your spreadsheet.

• =TIME() inserts a set time, so long as you format it as a serial number with the hours, minutes, and seconds, which will be converted into a standard format. So, =TIME(13,41,28) would produce 1:41 PM.
• =HOUR(NOW()) populates a cell with the current hour as a value between 0 and 23 (12 AM to 11 PM).
• =MINUTE(NOW()) does the same with the current minute, between 0 to 59.
• =SECOND(NOW()) does the same with the current second, again, between 0 to 59.

=VLOOKUP() and =HLOOKUP()

Vertical lookup and horizontal lookup enable you to seek out a specific value in one column or row and produce the data that is associated with it. Let’s say that you have a table that outlines your business’ assorted costs, perhaps giving you the total, how much is invested into different aspects, and such. These Excel functions allow you to select certain data points as a reference so you can ascertain the relationship between different values.

=IF()

Using =IF() gives you a quick and easy reference to check whether a set condition is true or false in reference to your data. Let’s say you’ve calculated out the returns on investment that some of your business initiatives have produced. Using =IF(), you can generate a clear yes or no answer to whether or not an initiative has seen the returns you wanted it to. Maybe you wanted to weed out any initiatives that saw less than 2% ROI, with the ROI of each occupying a cell in Column E. Using =IF(E1>0.02,"Yes","No") and dragging that formula down to match the cells in Column E will give you an at-a-glance reference of whether or not one of your efforts was worth it.

=COUNTIF() and =SUMIF()

The more data you have, the more challenging even simple tasks—like taking a tally or adding up the values—can be. =COUNTIF and =SUMIF simplify this process for you.

• =COUNTIF() allows you to set conditions by which selected cells will be counted. For instance, if you wanted to count how many months you had sold widgets out of the data present in Column J of the sheet, =COUNTIF(J1:J12, “widget”) would give you that total.
• =SUMIF() operates in a similar way, although it adds any numbers that match the conditions you’ve set, rather than just counting the instances. So, we could determine the total number of widgets sold during our established timeframe by using =SUMIF(J1:J12, “widget”, K1:K12), provided that Column K was populated with total widget sales for each month.

Excel Can Do a Lot, Lot More Than This

What are some of your go-to Excel functions? Are there any you’ve heard about that you’d like to learn how to use? Let us know in the comments, or give us a call at (314)828-1234!