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:
For help with this example, see the image above.
- Enter the following data into cells D1 to D3: 45, -26, 0
- Click on cell E1 in the spreadsheet - this is where the function will be located.
- Click on the Formulas tab of the ribbon menu.
- Choose Math & Trig from the ribbon to open the function drop down list.
- Click on SIGN in the list to bring up the function's dialog box.
- In the dialog box, click on the Number line.
- Click on cell D1 in the spreadsheet to enter that cell reference as the location for the function to check.
- Click OK in the dialog box.
- The number 1 should appear in cell E1 because the number in cell D1 is a positive number.
- 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.
- 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 ).
- When you click on cell E1 the complete function = SIGN ( D1 , 0.1 ) appears in the formula bar above the worksheet.
Other Math Functions