Microsoft Office Tutorials and References

In Depth Information

**Looking for a Sign**

Figure 7-8:

Using SIGN

to help

correctly

sum

amounts.

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.