|
|
|
The 2 Most Important Excel Functions AND
AND
|
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.
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 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 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 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. SUMIF See Excel Lesson 15 - 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 re-calculated in cell C3
|
Back home