Working With Common Excel Functions

Microsoft Excel can do the math for us as long as we know how to use Excel’s formulas and functions. In this article, you may get to know the difference between a formula and a function, the method to find the function you need, and the most commonly used functions in Excel. 🫡

Photo by ThisisEngineering RAEng on Unsplash

Functions and formulas are not technically the same. A formula is an expression that uses cell references or hard-coded numbers to calculate the value of a cell. For example, =B1+B2 and =328*4 are formulas.

Meanwhile, a function is a formula established in Excel that could make calculations a lot easier. For example, if you wanted to add up to 100 cells using a function, you would need to write =SUM(A1:A100) instead of =A1+A2+…+A100.

If you are not sure what the function you want is called, you could perform the following steps. 🔓

1. Click the Formulas tab. Then, click the Insert Function button or use the shortcut Shift+F3 to access the Insert Function dialog box.
2. Type a description of what you want to do in the Search for a Function text box, and then click Go. A list of functions appears in the Select a Function box. *Note: You may also select a category of functions from the Select a Category drop-down list to narrow the list.
3. Select the function you want from the Select a Function box and click OK.

Method to open Insert Function dialog box

I have created an Excel workbook and you may feel free to download it or make a copy in your Google drive so that you could work along with the following examples. Let’s have fun working with Excel functions. 🎈

SUM ➕

To add a range (both contiguous and noncontiguous) of numbers. After selecting the cell where you want the result to appear, you may type the =SUM( and choose the correct range of cells, individual cells, or a combination of both with the mouse. Alternatively, you may use ALT+= to obtain the auto sum of the contiguous range of numbers.

To obtain the sale for the entire year, you may enter the cell address of each cell that you want to be added together, so the formula in cell B17 is =SUM(B9, B16), or you could enter =SUM(B3:B8, B10:B15). Please note that a comma is needed to separate each cell (or range of cells) from the others.

Use the SUM function (refer to Sheet1 of the workbook)

MAX, MIN & AVERAGE⚡

The MAX function helps identify the highest value, while the MIN function calculates the lowest value in the column. These functions would return an error if the range of numbers or the cells you select contain non-numerical values.

The AVERAGE function helps find the mean of the range of numbers. The AVERAGE function counts the cells with a value of zero, but it will not include blank cells in the calculation. If you want those cells to be included, you need to enter 0 in the cells.

Use MAX, MIN & AVERAGE functions (refer to Sheet1 of the workbook)

COUNT & COUNTA 🔢

The COUNT function only counts the number of cells that contain numerical values. It will ignore blank cells and any cells with non-numerical values. The COUNTA function will count all cells containing data, including numerical values, non-numerical values, error values, and empty text (“”), but it will still ignore cells that are completely empty.

In the example below, we need to use the COUNT function given that we need to calculate the number of cells that contain numeric values without including the cells that contain the question marks. If you prefer to use the COUNTA function, you might need to remove the question marks from the cells. Otherwise, the COUNTA function would return 12 as the result which would be wrong.

In cell B17, you can calculate the yearly sales forecast based on the past months by converting current average sales to an annual amount by multiplying it by 12. So, the entire formula is =B14/B15*12, which yields the result of 83,085.60.

Use COUNT & COUNTA functions (refer to Sheet2 of the workbook)

ROUND, ROUNDUP & ROUNDDOWN📍

• The ROUNDUP function always rounds up (away from zero).
• The ROUNDDOWN function always rounds down (toward zero).
• The ROUND function rounds a number to a specified number of digits.
• Arguments in the syntax: number & num_digits.
• If num_digits is greater than 0 (zero), then the number is rounded to the specified number of decimal places. For example, to retain one decimal place, you simply need to place 1 in the last argument of the ROUND function.
• If num_digits is 0, the number is rounded to the nearest integer.
• If num_digits is less than 0, the number is rounded to the left of the decimal point. For example, to round the number to the nearest thousand, you need to use -3 as the last argument.

Use ROUND, ROUNDUP & ROUNDDOWN functions (refer to Sheet3 of the workbook)

IF🫥

To test certain conditions in the financial models and change outcomes or results depending on user inputs. The syntax looks like this: =IF(statement being tested, value if true, value if false).

In the example below, we are comparing the variance with the tolerance level of -10% using the IF function. In cell F3, enter the formula =IF(E3<$F$1,”OVER”,“OK”). Then, copy the variance formula all the way down to cell F11. We would notice that the software maintenance and hosting of the company are over the allocated budgets.

Use IF functions (refer to Sheet4 of the workbook)

COUNTIF & SUMIF💁‍♀️

• The COUNTIF function helps count the number of cells that match specified criteria.

For example, you have a list of sales made by salesperson by region, as shown in the figure below. In cell F2, type =COUNTIF( and press Ctrl+A. Then, select and lock the cells B2:B16 as the Range field in the Function Arguments dialog box. After that, choose E2 as the Criteria field. You don’t need to lock this reference because you want the cell reference to change as you copy it down the column. The resulting formula will be =COUNTIF($B$2:$B$16, E2) with the calculated value of 4. Finally, copy the formula down the column.

• The SUMIF function sums the values of cells in a range that meet the given criteria.

To know the sales in each region in the last example, type =SUMIF( and press Ctrl+A in cell F2. Select B2:B22 as the Range field, and then press F4. 4. Enter E2 as the criteria without locking it. Then, select and lock C2:C22 as the sum_range field. The resulting formula will be =SUMIF($B$2:$B$16, E2,$C$2:$C$16) with the calculated value of $50,729. Remember to copy the formula down the column.

Use COUNTIF & SUMIF functions (refer to Sheet5 of the workbook)

It is always good to build simple formulas first in separate cells and put them together so that the model is easier to follow. Lastly, I hope you found this article useful. Happy learning! 😎