• Share

Discuss in my forum

# Excel SIGN Function

## Find Positive and Negative Values in a Worksheet

Excel SIGN Function

The purpose of the SIGN function is to tell you whether a number in a specific cell is either negative or positive in value or whether it equal to zero.

The SIGN function is another of Excel's functions that is most valuable when used with another function, such as the IF function.

The syntax for the SIGN function is:

= SIGN ( Number )

Number - the number to be tested. This can be an actual number but it is usually the cell reference for the number to be tested.

If the number is:

• positive (such as 45) the function returns a 1
• negative (such as -26) the function returns a -1
• a zero ( 0 ) the function returns a zero ( 0 )

Example Using Excel's SIGN Function:

1. Enter the following data into cells D1 to D3: 45, -26, 0

2. Click on cell E1 in the spreadsheet - this is where the function will be located.

3. Click on the Formulas tab of the ribbon menu.

4. Choose Math & Trig from the ribbon to open the function drop down list.

5. Click on SIGN in the list to bring up the function's dialog box.

6. In the dialog box, click on the Number line.

7. Click on cell D1 in the spreadsheet to enter that cell reference as the location for the function to check.

8. Click OK in the dialog box.

9. The number 1 should appear in cell E1 because the number in cell D1 is a positive number.

10. Drag the fill handle in the bottom right corner of cell E1 down to cells D2 and D3 to copy the function to those cells.

11. The cells D2 and D3 should have the numbers -1 and 0 respectively since D2 contains a negative number ( - 26 ) and D3 contains a zero ( 0 ).

12. When you click on cell E1 the complete function = SIGN ( D1 , 0.1 ) appears in the formula bar above the worksheet.

Ted French