|
|
||
|
|
|
Excel Index and Search Tool (Click
on the buttons below) |
Excel Lesson 27 of 30: Basic and Important Excel Math and Trig Functions |
| 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 |
| 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 The SUMIF functions
allows you to sum certain values in a range that respect a certain
condition: The SUMIF function
also allows you to sum a certain column if in the other column a
criteria is respected: Remember that the range that you are adding is the third argument (C1:C13) and that the criteria (A1 or "Peter") that you are testing is in the first argument (B1:B13). SUMIF is an interesting function but SUMPRODUCT does the same thing and much more. With SUMPRODUCT you can sum a range based on criteria in one or more other columns. 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 11 that is entirely dedicated to SUMPRODUCT SUBTOTAL One of the giant step (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 to perform ad hoc analysis. 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 re-calculated in cell C3 |
|
Excel Index and Search Tool (Click
on the buttons below) |
|
|
||
|
|