Excel Formulas and Functions

Basic Math Formulas

One of the most important spreadsheet functions is using formulas to calculate values in cells. Formulas are just equations. Instead of adding or subtracting numbers, you are adding and subtracting the contents of cells. The power of formulas in Excel is whenever the contents of the cells referenced in a formula change, Excel will recalculate the formula answer for you!

Some formulas can get extremely complicated. Therefore, whenever you have completed a spreadsheet, it is a good idea to lock the cells containing formulas to prevent you from accidently writing over a formula. If you ever do want to change a formula, just unlock the spreadsheet. Here is more information on locking particular cells. The 5 basic rules to remember as we discuss formulas are:

  1. All formulas start with an equal (=) sign. This tells Excel that it is a formula.
  2. The answer that the formula produces will display in the cell into which you typed the formula.
  3. Cells are referenced in a formula by their column-row identifier, ie. A1, B2 (think Bingo).
  4. The symbols for addition, subtraction, multiplication, and division are: + - * /
  5. You do not have to enter capital letters in your formula; Excel will automatically capitalize them for you.

Example of simple math formulas:

  • =A1+A6   add the contents of cell A1 with the contents of cell A6
  • =A1+A2+A3   add the contents of these three cells. See SUM below when adding subtotals and totals
  • =A3-A1   subtract the contents of cell A1 from the contents value in cell A3
  • =B2*B3   the * means ‘multiply’, so this formula says multiple together the values in B2 and B3
  • =G5/A5   the forward slash ( / ) means ‘divide’, so this formula says divide G5 by A5. (NOTE: If you see #DIV/O! in a cell, you are dividing by zero or no value - which is not allowed.)
  • =G5^2   tells Excel to square the value in cell G5. The number after the caret is the exponent. Therefore, H2^3 would cube the value in cell H2.

We can combine multiple operations in one formula. Make sure you use parenthesis where needed or you may not get the correct results (see Order of Operations below). Here are some examples:

  • =(C1+C3)/C4   add the value in C1 to the value in C3, and then divide the result by the value in C4
  • =4*(A2+A5)+3   adds the contents of A2 and A5, multiples this sum by 4, and then adds 10.

Order of Operations

One can remember the Order of Operations by remembering the phrase Please Excuse My Dear Aunt Sally. The letters stand for: Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction. And all operations are carried out from left to right. First, all parentheses are resolved in the formula or expression. On the second pass, all exponents are resolved. Then all multiplication OR division is carried out. Lastly, all addition and subtraction is carried out. Please note that even though the Please Excuse My Dear Aunt Sally phrase might imply that multiplication is done before division, and addition is done before subtraction, that is not true. Multiplication and division are of equal weight and either is completed as one passes through the formula from left to right. Likewise for addition and subtraction.

Let's get goofy for a minute and work through a complicated expression where A1 contains the value 6 and A2 contains the value 3. Try it first and see if you get the correct answer.
Expression: 3^(A1/A2)+(3*A2)-2*(A1-A2)-15/A2

  • Pass 1 - Parentheses: 3^2+9-2*3-15/3 (A1/A2=6/3=2; 3*A2=3*3=9; A1-A2=6-3=3)
  • Pass 2 - Exponents: 9+9-2*3-15/3 (3^2 is 3*3=9)
  • Pass 3 - Mult and Div: 9+9-6-5 (work L to R: 2*3=6; 15/3=5)
  • Pass 4 - Add and Subt: 7

Calculating Percentages

There are 2 ways you can calculate percentages, depending on how you want the answer displayed. Let’s assume A1=25, and A2=50. If you enter the formula =A1/A2 in B6, the answer will be .5. However, if you format B6 to display a Percentage, then Excel will display 50% instead of .5 in cell B6.

Let‘s suppose you have a column heading of PERCENT and you just want the number 50 displayed. You would then use the formula: =(A1/A2)*100. Using the above example, A1/A2 equals .5, and .5 times 100 equals 50.

How Excel Interprets Cell References in Formulas

Excel interprets cell references in formulas in a ‘positioning’ manner. It looks at how cells relate to each other on the grid. There are mostly benefits to this, but first let’s explain doing some simple column totals. Assume you have numbers in cells A1 through A10, and you also have numbers in cells B1 through B10. Then, in cell C1 you enter the formula =A1+B1. Can you visualize this? All three cells are in column 1. You have a number in A1, a number in B1, and the sum of these two numbers will be calculated and displayed in C1. Now, this is how Excel reads the formula in cell C1: “Take the value in the cell 2 spaces above where I’m at, add it to the value in the cell 1 space above where I’m currently at, and put the answer in the cell where I’m currently at!

This is handy is because now you can copy the formula in C1 and paste it into cells C2 through C10 and the formulas will be perfect. Excel won’t repeat the exact formula of =A1+B1 into cell C2 - it will write the formula the way you want it: =A2+B2. Because if Excel is sitting in cell C2, the cell 2 spaces above is A2 and the cell 1 space above is B2. Cell C3 will contain the formula =A3+B3 and so on. The same is true if you are adding rows of numbers.

So, we have not only learned how Excel interprets formulas, but we have learned a fabulous shortcut. When you are creating a row or column of subtotals or totals, or repeating some other formula as described in the above example, you can copy and paste the formula from cell to cell.

Using an Absolute Cell Reference

There may be times when you want to copy a formula but you don't want Excel to change the cell references. Let’s say you want each number in Row A divided by the number in E1. You don’t want to hard-code the number in E1 because it will change each month. If your formulas/answers are going to be in Row B, the first formula - sitting in cell B1 - would be: =A1/E1. You’d want the formula in B2 to be =A2/E1, the formula in B3 to be =A3/E1, and so on. But what will happen if you put the formula =A1/E1 in cell B1 and then copy and paste it into cell B2? Will you get what you want … which would be =A2/E1? NO. Excel will put the formula =A2/E2 in cell B2. It will put this formula in B2 because of what we discussed above. To Excel, the formula in B1 reads: “Divide the cell 1 space above me (A1) by the cell 4 spaces below me (E1). So when it moves to B2, it will change A1 to A2 and E1 to E2. But you don’t want Excel to change E1 to E2; you want it to always use E1. You could manually type in each formula, but there is a better way - using an absolute cell reference. When you want Excel to keep a cell reference constant, you place a $ before the column and row. So instead of your first formula being =A1/E1, you would type =A1/$E$1. Now when Excel copies the formula from cell to cell, it will keep E1 constant in the formula. When you copy the formula to B2, Excel will write: =A2/E1. And in B3, the formula will read: =A3/E1.

Sample Formulas and Functions

Microsoft Excel has many built-in functions to perform calculations on spreadsheet data. Here are the most popular functions. An Excel function always begins with an equal = sign.

=SUM

If you want to total a column of numbers, i.e. cells G3 through G28, you could use a simple math formula like =G3+G4 … all the way to G28! But there is a better way. The cells G3 through G28 are referenced G3:G28 in Excel. Use the sum function =SUM(G3:G28) to add these cell contents. Here are some other examples of =SUM:

  • =SUM(E3:E9,E12)   adds contents of E3 through E9, plus E12
  • =SUM(A2:A23)/150   adds contents in A2 through A23 and then divide the result by 150.
  • =10*SUM(D1:D3)   adds contents in D1 through D3 and then multiply the result by 10.
  • =SUM(A2:A14,C4:C29)   adds the contents in A2 through A14, and then adds that to the sum of the contents of C4 through C29.

=PRODUCT

This function multiplies the contents of the referenced cells

  • =PRODUCT(A1:A4) multiplies the contents of A1 through A4. This would be the same as A1*A2*A3*A4
  • =PRODUCT(A1:A4,2) multiplies the contents of A1 through A4, and then multiple by the number 2

=ROUND

This function rounds the contents of a cell following normal rounding rules: down if the decimal is <.5 and up if the decimal is = or > .5. Inside the parentheses you specify the cell or cells, then the number of decimal places to round to. Here are two examples, and then I will tell you why this function is important!

=ROUND(A3,2)  says take the value in A3 and round it to the nearest hundredth (2 decimal places). So if the value in A3 is 7.239, the formula produces a value of 7.24.

=ROUND(C9,0)  says take the value in C9 and round it to the nearest integer (zero decimal places). So if the value in C9 is 22.43, the formula produces a value of 22.

When you format a cell in Excel, you’re specifying how Excel should display the number. But the underlying value may be different. Once I created a spreadsheet that calculated bonuses using a complicated formula. When I totaled some columns by hand, it appeared that Excel had made an error! For example:

Your cells are formatted for currency. You multiply two numbers by 25% and then add the results together. Our numbers are $100.85 and $101.45.

  • .25 * $100.85 = $25.2125, so Excel displays $25.21.
  • .25 * $101.45 = $25.3625, so Excel displays $25.36.

Therefore, $25.21 + $25.36 = $50.57. But Excel will show $50.58 on the spreadsheet! Why? Excel calculates it this way: $25.2125 + $25.3625 = $50.575 … and this rounds up to $50.58. Since this looks like an error in your spreadsheet, use the ROUND function when working with decimals in this fashion: =ROUND(B4*.25,2) multiply the contents of B4 by .25 (25%) and round the answer to 2 decimal places.

=SQRT

To find the square root of the contents of cell M5, use the function =SQRT(M5). You can put formulas inside the parantheses, such as: =SQRT(B4+B5). This formula says: find the square root of the sum of B4 and B5.

=AVERAGE

Let’s say you have 5 cells, B6 through B10. The contents of B6=1, B7=2, B8=3, B9=4, B10=5. To calculate the average of these five numbers, we add them together and divide by five. Excel will calculate this average with the function =AVERAGE(B6:B10)

Other examples:

  • =AVERAGE(D2,D4,D6,D8)  Excel would average the contents in those four cells.
  • =AVERAGE(C2:C9,C15:C19) Excel would average the contents of the cells in these two ranges.

=MAX

The MAX function will indicate which cell in a specified range or group of cells has the greatest value. Example:

  • =MAX(G3:G23)  would show the highest number found in cells G3 through G23.
  • =MAX(A2,A4,A6,A10:A20)  would show the highest number found in the cells referenced.

=MIN

The MIN function is similar to the MAX function. If we used the examples above but used =MIN instead, Excel would display the minimum value.

=TODAY()

Wherever cell you place this function will display the current day’s date. After you enter the formula, click in an empty cell and see the result. If you want to format the date differently than displayed, right-click in the cell and click Format. Click the first tab, click Date, and choose your format.

=IF

One of the most useful functions in Excel is the IF function. What it does is test a value in a specific cell to see if that value meets a certain condition. If the value does meet the condition (true), one result will happen. If the value does not meet the condition (false), a different action will occur.

This is a powerful function and is really quite easy to understand! The format is =IF(A,X,Y). The first value (A) is a condition which you are testing to see if it is true or false. The second value (X) is what you want displayed if the condition is true, and the last value (Y) is what you want if the condition is false. Let’s look at some examples:

  • =IF(D1 > 26, 33, 44). This says: If the value of D1 is greater than 26, then display 33 in this cell. But, if D1 is not greater than 26, then display 44.
  • =IF(D1 < 100, "OK", "Over Budget"). This says: If the value of D1 is less than 100, then display OK. Otherwise, display Over Budget. (If text if to be displayed, it must be in quotes.)
  • =IF(SUM(A1:A3)=50,"Slow","Fast") Yes, you can include formulas in an IF statement. This says: If the sum of the contents of cells A1 through A3 equals 50, then display Slow. If not equal to 50, display Fast.
  • = IF (A2 > 1000, 985, B2+225). This says: If the value of A2 is greater than 1000, then display 985. Otherwise, add 225 to the contents in B2 and display that number.

You may have =IF statements inside of =IF statements. These are called nested IFs. There are many examples of nested IF statements on the Internet.

=COUNTIF

This function allows you to display the number of cells in a specified range whose value meets your criteria. The format is: =COUNTIF(range,"criteria"). Look at the following examples:

  • =COUNTIF(A1:A18,“=100"). This says: Display the number of cells in A1 through G1 that contain the value 100.
  • =COUNTIF(B2:B12," < 8"). This says: Display the number of cells in B2 through B12 whose value is less than 8.
  • =COUNTIF(F3:Q3,"=Smith") This says: Display the number of cells in F3 through Q3 whose contents equals Smith.

Back to Top