The 2 Most Important

Excel Functions AND
Formulas

SUMPRODUCT

AND

INDEX/MATCH

Lesson 8: Excel Spreadsheets  Mathematical Functions (59)

In annex 8 you have found a description of all 50  Excel functions in the "Mathematical" category. Below is the list of the 9 most useful ones. See more on the very powerful SUMPRODUCT function in Excel in lesson 11, more on the SUBTOTAL function in lesson 13.

Tips
Read other general tips on formulas in the Introduction to this section on Excel Functions and Formulas

When you specify in the format of a cell that you want only 2 decimals Excel shows only 2 decimals (rounding up) BUT it still uses all the decimals. For example if in cell A1 you enter 2.1456 and format it to show only 2 decimals you will see 2.15. Now if in cell B1 you write the formula =A1 and make the format "General" you will see that Excel is using all 4 decimals (2.1456). This is why you will need to use functions like INT, TRUNC, ROUND, ROUNDUP and ROUNDDOWN if you want to use a specific number of decimals in your calculations.

SUM

=SUM(A1,B6,G6) or =SUM(A1+B6+G6) will return the sum of the values in cells A1, B6 and G6
=SUM(A1:A23) will return the sum of the values in cells A1 to A23
=SUM(A1:A23,F3:F34) will return the sum of the values in cells A1 to A23 plus the sum of the values in cells F3  to F34

In cell B2 of a yearly summary you want to sum the values in cells B2 of each of the monthly sheets. You have named your sheets "January" , "February" ....and you have used: =January!B2+February!B2+March!B2...+December!B2
You can also write this:=SUM(January:December!B2)

TRUNC

I don't use the INT or ROUNDDOWN functions because TRUNC does the same thing and more. The TRUNC function removes decimals without rounding. If you have 2.2 or 2.7 in cell A1 =TRUNC(A1,0) will return 2. Interestingly enough if you have 12,345 in B1 using a minus sign in the second argument of TRUNC =TRUNC(B1,-3) will return  (12,000). Handy when you don't want to show the hundreds, the tens and units in a report.

ROUND

This function removes decimals rounding up the last decimal if the next one is 5 or over. So if you have 4.126 in cell A1 and use the formula =ROUND(A1,2) the result will be 4.13 if the value in A1 is 4.123 the result will be 4.12.

ROUNDUP

This function does the same thing as the function ROUND but always rounds up. So if you have 4.126 in cell A1 and use the formula =ROUNDUP(A1,2) the result will be 4.13 if the value in A1 is 4.123 the result will still be 4.13.

ABS

=ABS(A1) will return 5 if in cell A1 you have -5 or 5. This functions removes the sign.

MOD

The modulo is what is left after a division. =MOD(20,6) is 2 because you have 3 times 6 in 20 and the rest is 2. Notice the use of the comma to separate the arguments. See an application below in determining the age of a person.

SUMPRODUCT
The best kept secret in Microsoft Excel

Here is what Excel says you can do with SUMPRODUCT:

Let's say that you have a series of quantities in cells A1 to A5 and a series of unit prices in B1 to B5. With SUMPRODUCT you can calculate total sales with this formula: =SUMPRODUCT(A1:A5,B1:B5)

Basically SUMPRODUCT sums A1 multiplied by B1 plus A2 multiplied by B2.........

In the last 20 years I have used SUMPRODUCT for the purpose presented by Excel once or twice. But I use SUMPRODUCT daily to solve all kinds of other business data problems. It is the most powerful and useful function in Excel. Read chapter 13 that is entirely dedicated to SUMPRODUCT

SUBTOTAL

One of the giant steps (no. 2) that users make is when they learn about the database functionalities in Excel. When you know how to filter data then SUBTOTAL becomes a very interesting function.

The function SUBTOTAL allows (among other operations) to count, to sum or to calculate the average of filtered elements of a database. The function requires two arguments, the second is the range covered by the function and the first is a number between "1" and "11" that specifies the operation to be executed (for ex. "1" is for average, "2" is for count and "9" is for sum).
=SUBTOTAL(9,B2:B45)

SQRT

Extracting a square root is finding the number that multiplied by itself will result in the number that you are testing. Extracting a cubic  root is finding the number that multiplied by itself two times will result in the number that you are testing. Extracting the  fourth  root is finding the number that multiplied by itself 3 times will result in the number that you are testing.

To extract the square root of a number you will use a formula like:
=SQRT(16) that will result in 4 because 4 multiplied by 4 is 16 or
=SQRT(A1) that will also result in 4  if the value in cell A1 is 16.

There are no specific Excel function to extract the cubic root or any other root. You have to trick the POWER function into doing it.

POWER

You can raise a number to a power (multiplying it by itself a certain number of times with this function. Hence:
=POWER(4,2) will result in 16  (4 times 4) or
=POWER(A1,2) will also result in 16  if the value in cell A1 is 4.

You can  to trick the POWER function into extracting the square root, the cubic root and any other root by submitting a fraction as second argument. For example you can extract the square root of 16 with the formula =POWER(16,1/2), the cubic root with =POWER(16,1/3) and so on. 

ROUND, SUM
=ROUND(SUM(A1:A5),2) will return the sum of A1 to A5 rounded to 2 decimals.

IF, MOD, TRUNC and &
How many dozens are there in 106 units?
With the number of units in cell A1 the formulas in B1:
=TRUNC(A1/12,0) will return the number of   complete dozens
this formula in C1:
=MOD(A1,12) will return the number of units left when the total number is divided by 12. 

If you want to present the result as "8 dozens and 10  units" in a single cell you will use the following formula combining math & Trig functions and the ampersand (& ) sign:
=TRUNC(A1/12) & "dozens and " & MOD(A1,12) & " units"
But what if there are 96 units and you don't want the result to show as "8 dozens and 0  units" but as "8 dozens" . You will then use this formula:
=IF(MOD(A1,12)=0,TRUNC(A1/12) & " dozens" ,TRUNC(A1/12) & " dozens and " & MOD(A1 12) & " units" )

INT, TRUNC, MOD  and &
You want to determine the age of a person. If in cell " A3" you enter the date of birth, and in cell " B3" today's date, the following formula in " C3" would give you a good approximation of the age (plus or minus a few days):
=INT((B3-A3)/365) & " years and " & TRUNC((MOD((B3-A3) 365))/30) & " months"

If in cell A3 you enter the date of birth  and in B3 you enter the formula =NOW() then each day when  you open the workbook the age of the person is re-calculated in cell C3