SIGN()

Syntax. SIGN(number)

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

• 1 if the number is positive

• 0 if the number is 0

• –1 if the number is negative

Argument

• number (required) Any real number

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.

See Also

ABS()

Sample Files

Use the SIGN worksheet in the Chapter16.xls or Chapter16.xlsx sample file. The sample files are found in the Chapter16 folder. For more information about the sample files, see the section titled Using the Sample Files.