The math and trigonometry functions include the SUM() function. This function is probably the most commonly used function in Excel. To enhance the SUM() function, Excel offers the SUMIF() function. Assume that you have a list containing customer sales. To filter the sales for one customer and calculate the sum, use the SUMIF() function as shown in the following example.
The customers are listed in A2:A20, and the sales are listed in B2:B20 (see Figure 2-51). Cell D6 contains the customer whose sales you want to query. The formula in E6 for the sum of the sales is:
=SUMIF(A2:A20,D6,B2:B20)
If you enter the name of the customer in D6, you can calculate the sum of the sales.
Another function of the math and trigonometry function is the RANDBETWEEN() function, which makes life with Excel easier. With this function you can quickly fill tables with test and demonstration data.
Note
For Excel 2003 and earlier, you might have to activate this add-in function (see the section titled Analysis Functions earlier in this chapter). In Excel 2007 and Excel 2010, the function is available by default.
Enter the RANDBETWEEN() function in any cell, and then enter the minimum and maximum value for the number range. If the values range from 500 through 1000, use the formula =RANDBETWEEN(500,1000). Now you can copy the formula in the other value cells to display a different value from 500 through 1000 in those cells (see Figure 2-52).
If you open the file or press the F9 key, all values are recalculated. To convert the random values in the cells to fixed values, copy the range containing the random values and select the Insert Values command in the shortcut menu to insert the range. Select the Values option in the Paste Special dialog box (see Figure 2-53), and click OK.
These are just two examples of the functions in the math and trigonometry section. You will find further examples in Chapter 7 to Chapter 17 of this book.