Function microsoft excel 2007 how to#
Function microsoft excel 2007 plus#
Function microsoft excel 2007 series#

=ROUND(SUM(A1:A5),2) will return the sum of A1 to A5 rounded to 2 decimals. 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. 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. =POWER(4,2) will result in 16 (4 times 4) orĪlso result in 16 if the value in cell A1 is 4. You can raise a number to a power (multiplying it by itself a certain number of times with this function. You have to trick the POWER function into doing it. There are no specific Excel function to extract the cubic root or any other root. =SQRT(16) that will result in 4 because 4 multiplied by 4 is 16 or To extract the square root of a number you will use a formula like: Extracting the fourth root is finding the number that multiplied by itself 3 times 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. '1' is for average, '2' is for count and '9' is forĮxtracting a square root is finding the number that multiplied by itself will result in the number that you are testing. '1' and '11' that specifies the operation toīe executed (for ex. The function and the first is a number between

Function microsoft excel 2007 how to#

When you know how to filter data then SUBTOTAL becomes a very interesting function.Ĭalculate the average of filtered elementsĪrguments, the second is the range covered by 2) that users make is when they learn about the database functionalities in Excel. Read chapter 13 that is entirely dedicated to SUMPRODUCT It is the most powerful and useful function in Excel. But I use SUMPRODUCT daily to solve all kinds of other business data problems. In the last 20 years I have used SUMPRODUCT for the purpose presented by Excel once or twice.

Function microsoft excel 2007 plus#

With SUMPRODUCT you can calculate total sales with this formula: =SUMPRODUCT(A1:A5,B1:B5)īasically SUMPRODUCT sums A1 multiplied by B1 plus A2 multiplied by B2.

Function microsoft excel 2007 series#

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. Here is what Excel says you can do with SUMPRODUCT: See Excel Lesson 15 - Excel SUMIF Function See an application below in determining the age of a person. Notice the use of the comma to separate the arguments. =MOD(20,6) is 2 because you have 3 times 6 in 20 and the rest is 2. The modulo is what is left after a division. =ABS(A1) will return 5 if in cell A1 you have -5 or 5. 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. 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 =ROUND(A1,2) the result will be 4.13 if the value in A1 is 4.123 the result will be 4.12. This function removes decimals rounding up the last decimal if the next one is 5 or over. Handy when you don't want to show the hundreds, the tens and units in a report. 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). If you have 2.2 or 2.7 in cell A1 =TRUNC(A1,0) will return 2. The TRUNC function removes decimals without rounding. I don't use the INT or ROUNDDOWN functions because TRUNC does the same thing and more. You can also write this: =SUM(January:December!B2) and you have used: =January!B2+February!B2+March!B2.+December!B2 You have named your sheets 'January', 'February'.

In cell B2 of a yearly summary you want to sum the values in cells B2 of each of the monthly sheets. =SUM(A1:A23,F3:F34) will return the sum of the values in cells A1 toĪ23 plus the sum of the values in cells F3 to F34 =SUM(A1:A23) will return the sum of the values in cells A1 to A23 =SUM(A1,B6,G6) or =SUM(A1+B6+G6) will return the sum of the values in cells A1, B6 and G6 INT, TRUNC, ROUND, ROUNDUP and ROUNDDOWN if you want to use a specific number of decimals in your calculations. This is why you will need to use functions like 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). For example if in cell A1 you enter 2.1456 and format it to show only 2 decimals you will see 2.15. 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.

Read other general tips on formulas in the Introduction to this section on Excel Functions and Formulas 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. Lesson 8: Excel Spreadsheets Mathematical Functions (59) Microsoft Excel Mathematical Functions This is an excerpt from Pierre Leclerc (The 2 Most Important