Syntax. SIGN(number)

Definition. This function returns the sign of a number. SIGN() returns the following values:

Argument

Background. Numbers greater than zero are positive numbers and have the plus sign (+). Numbers less than zero are negative and have a minus sign (–). The plus sign can be omitted. The number 0 is neither positive nor negative.

With the SIGN() function, you can filter values based on their sign.

Example. Assume that you want to filter the subsidiaries that generate negative revenues in a sales list. First, you display the sum of the losses (see Figure 16-25).

Figure 16-25. Revenues with different signs.

You enter the SIGN() function in column C with a reference to the revenue (see Figure 16-26). Now you can filter all revenues with a minus sign.

Figure 16-26. Filtering values with the minus sign.

To display the sum of the losses, you enter the following formula in E11:

{=SUM(IF(SIGN(B2:B9)=-1,B2:B9))}

To display the revenues, you enter the following formula:

{=SUM(IF(SIGN(B2:B9)=1,B2:B9))}

You have to enter both formulas as array formulas, so you must press Ctrl+Shift+Enter after you enter the formula.