Some calculations, such as computing the value of your inventory, require a two-step process of multiplying values and then adding the results. You can reduce the work by using the SUMPRODUCT function.
Calculate the total value of your inventory using the SUMPRODUCT function. (Click for larger image). |
This function takes two ranges and multiplies each element in the first range by each matching element in the second range, and then it totals the result. For example, if your inventory worksheet includes numbers of each item in column B (rows 2 – 25) and the corresponding cost value in column C, you can calculate the overall value with this function:
=SUMPRODUCT(B2:B25,C2:C25)
If you are unsure which function to use for a particular task, you can get assistance from Excel itself. Click in the cell where the function should appear, choose Insert > Function and type a brief description of what you want to do.
Alternatively, select the category of function, for example Financial Functions and look for a function to perform the required task. Select the function to see a description of what it does and the data it requires. When you click OK the Function Arguments dialog appears, and you can select each item in turn to build your formula. Arguments in bold type are required and those in regular type are optional. Check the Function Arguments dialog for any specific information about the data the function requires.
Using Insert > Function gives you additional tools for locating and using Excel functions. (Click for larger image). |
If you have already started entering a function and you decide you need more help, press Ctrl + A to display the function arguments dialog. This gives you step-by-step assistance in completing the function.
When a particular calculation has no built-in function, you can build your own Excel formula. When doing so, take care that you don’t fall foul of the order in which Excel makes calculations. For example, Excel calculates =2+3*4 as 14 not 20 because it performs calculations in a particular sequence called the order of precedence.
Thus Excel performs multiplication and division before addition and subtraction so the 3*4 part of the formula is calculated before adding the result to the number 2. If the answer should be 20, then you must put brackets around the 2+3 part of the calculation to force it to be performed before the multiplication ‑‑ brackets take precedence over multiplication and division. This formula will give a result of 20: =(2+3)*4
An instance where you may encounter the need to put calculations in brackets is when you are calculating tax on a price less a discount. If you're selling an item for $100 with a $10 discount and tax is 7 percent of the total, then you will use =(100-10)*7% to calculate the tax ‑‑ without the brackets the result would be incorrect.
If you are using a function that you are unfamiliar with, always test it first with data for which you know the answer already so you can check to ensure it is working correctly.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com
This article was first published on SmallBusinessComputing.com.