Excel Math and Trig Functions and Formulas

6137494695 
Tips and Ideas on Excel 
Tips and Ideas on Macros 
Excel Chapter 20 of 24: Excel Math and Trig Functions and Formulas 
Excel offers you 58 functions in the math & trig category. See them all with a short description in Excel Spreadsheets Math & Trig Functions (chapter 24). The most important and powerful function in Excel is SUMPRODUCT and it has a entire chapter dedicated to it. Here are the functions (10) that you will use more often with tips and examples. 

Functions  What it Does 
ABS  Returns the absolute value of a number 
MOD  Returns the remainder from division 
POWER  Returns the result of a number raised to a power and allows the calculation of cubic root and other roots 
ROUND  Rounds a number to a specified number of digits 
ROUNDUP  Rounds a number up, away from zero 
SQRT  Returns a positive square root 
SUBTOTAL  Returns a subtotal in a list or database 
SUM  Adds its arguments 
SUMIF  Adds the cells specified by a given criteria 
SUMPRODUCT  Returns the sum of the products of corresponding array components 
TRUNC  Truncates a number to an integer 
Tips 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 ROUNDOWN 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 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 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. SUMIF See Excel Lesson 20A of 23  Excel SUMIF Function SUMPRODUCT 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). 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: 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: 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
IF, MOD,
TRUNC and & 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:
INT, TRUNC,
MOD and & 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 recalculated in cell C3 
Next Chapter: SUMIF Function in Excel 
Excel Table of Contents 
Discover Even More in 50 Excel spreadsheets 

6137494695 
Tips and Ideas on Excel 
Tips and Ideas on Macros 
Excel Math and Trig Functions and Formulas