Microsoft Office Tutorials and References
In Depth Information
Looking for a Sign
As shown in Figure 7-8, values in Column B equal 1 when the associated value
in Column A is positive. Column B displays –1 when the associated value is
negative. This information is then used in a SUMIF function, which selectively
sums information from Column A.
In cell B18 is this formula: =SUMIF(B3:B15,1,A3:A15).
In cell B19 is this formula: =ABS(SUMIF(B3:B15,-1,A3:A15)).
The SUMIF function is used to indicate a criterion to use in determining
which values to sum. For the sum of sales in cell B18, the presence of the
value 1 in Column B determines which values to sum in Column A. For the
sum of returns in cell B19, the presence of the value –1 in Column B
determines which values to sum in Column A.
Also, the ABSOLUTE function (ABS) is used to present the number in cell
B19 as a positive number. The answer in cell B19 is the sum of merchandise
returns. You would say there was $64.18 (not –$64.18) in returned
merchandise, if you were asked.
The SUMIF function is covered in Chapter 8. The ABS function is covered next
in this chapter.
Here’s how to use the SIGN function:
1. Position the cursor in the cell where you want the results to appear.
2. Enter =SIGN( to begin the function entry.