How to use the SIGN function

What is the SIGN function?

The SIGN function returns the sign of a number. 1 for a positive number, 0 (zero) for a 0 (zero) and -1 for a negative number.

1. Introduction

What is the sign of a number?

The sign indicates if a number is positive or negative with one exception and that is zero. Positive numbers can be written with or without a plus sign and are larger than 0 (zero).

Negative numbers are always written with a minus sign are are smaller than 0 (zero).

Is zero positive or negative?

Zero is neither positive nor negative.

What is a signed number?

Positive and negative numbers are sometimes called signed numbers.

How to remove the negative sign from a number?

The ABS function returns the absolute value, in other words, the minus sign is removed.

How to toggle the sign?

Multiply by -1. For example,

3*-1 = -3
-2*-1 = 2

You can also use the minus sign like this: =-A1 This alternates the sign of a number specified in cell A1.

Alternative way to check if a number is larger than 0 (zero)?

Excel has comparison operators that you can use to check cell values against a condition.

• < less than sign
• > larger than sign
• = equal sign

Use the following formula to check if cell A1 contains a positive number:

It returns a Boolean value TRUE or FALSE if the condition is met.

2. Syntax

The SIGN function has only one argument.

SIGN(number)

number

Required. A number for which you want to know the sign of.

3. Example

The image above shows the SIGN function in cells C3:C8 and the source data in cells B3:B8. The first number specified in cell B3 is 23 which is a positive number, the SIGN function returns 1 in cell C3 indicating the number is positive.

The second number in cell B4 is -23 which the SIGN function evaluates to -1. This means the number is negative.

Formula in cell C3:

The third number is 0 (zero) which the SIGN function calculates to 0 (zero). The fourth and fifth number are Boolean values and are evaluated to TRUE - 1 and FALSE - 0 (zero).

What is a Boolean value?

A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions that I'll discuss below.

Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.

4. Function not working

The SIGN function converts numbers stored as text automatically which is demonstrated in cell C3. The source data in cell B3 is '23 which Excel interprets as a text value, however, it also understands that it might be a mistake indicated by the green arrow located in the top left corner of cell B3.

The SIGN function cand handle error values and returns an error value, this is shown in cell B4 and B5.

Text values return a #VALUE! error demonstrated in cell B5 and B6.

4.1 Troubleshooting the error value

When you encounter an error value in a cell a warning symbol appears, displayed in the image above. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error if you press with left mouse button on it.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

#NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.

#SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.

#DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically.

#VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.

#REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.

#NAME error - The #NAME error happens if you misspelled a function or a named range.

#NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.

#N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.

#GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

4.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that is really handy in these situations. Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. The italicized expression is the most recent result. The buttons at the bottom of the dialog box allows you to evaluate the formula in smaller calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat pressing the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9. F9 is especially useful if you have a feeling that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the issue..

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

The image above shows cell reference D3 converted to hard-coded value using the F9 key. The TRIMMEAN function requires numerical values between 0 (zero) and 1 which is not the case in this example. We have found what is wrong with the formula.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

4.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

5. Compare signs of numbers

This example shows a formula that compares the signs of two numbers, it returns TRUE if both numbers have the same sign and FALSE if the signs are different.

Formula in cell D3:

Explaining the formula

Step 1 - Extract the sign from the first number

SIGN(B3)

becomes

SIGN(23)

and returns 1.

Step 2 - Extract the sign from the second number

SIGN(B4)

becomes

SIGN(-23)

and returns -1.

Step 3 - Compare signs

The equal sign is a comparison operator and returns a boolean value TRUE if the condition is met and FALSE if the condition is not met. It lets you compare the signs of the numbers in cells B3 and B4.

SIGN(B3)=SIGN(B4)

becomes

1=-1

and returns FALSE.

6. Filter values by sign

This example demonstrates how to filter numbers based on their signs, cell range B3:B16 contains random integers both positive and negative signs. The FILTER function extracts the positive numbers based on their sign.

Dynamic array formula in cell D3:

Explaining the formula

Step 1 - Extract the signs from the numbers

SIGN(B3:B16)

becomes

SIGN({-22;-41;-31;38;0;-7;-43;38;-1;-1;31;45;20;44})

and returns

{-1;-1;-1;1;0;-1;-1;1;-1;-1;1;1;1;1}.

Step 2 - Compare output to 1

The equal sign is a comparison operator and returns a boolean value TRUE if the condition is met and FALSE if the condition is not met. We need to compare with 1 to extract positive numbers from cell range B3:B16.

SIGN(B3:B16)=1

becomes

{-1;-1;-1;1;0;-1;-1;1;-1;-1;1;1;1;1}=1

and returns

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE}

Step 3 - Filter values based on condition

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B16,SIGN(B3:B16)=1)

becomes

FILTER({-22;-41;-31;38;0;-7;-43;38;-1;-1;31;45;20;44}, {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})

and returns

{38;38;31;45;20;44}

7. Extract records containing negative numbers

Array formula in B23:

copied down as far as needed and then copied to the right as far as needed.

Explaining array formula in cell B23

Step 1 - Filter negative values and return their corresponding row number

IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({1;5;0;-2;-5;2;4;4;-3;-4;-4;1;-1;0;-5;1}<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, "")

and returns

{"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}

Step 2 - Return the k-th smallest row number

SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1))

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, ROW(A1))

becomes

SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, 1)

and returns 4.

Step 3 - Return value from cell range

INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))

becomes

INDEX($B$3:$D$18, 4, 1)

becomes

INDEX({"MARGARET","ROBINSON",1; "DAVID","DAVIS",5; "MICHAEL","JONES",0; "MARIA","GARCIA",-2; "LISA","RODRIGUEZ",-5; "PATRICIA","JACKSON",2; "MARY","THOMAS",4; "THOMAS","TAYLOR",4; "MICHAEL","JONES",-3; "RICHARD","MILLER",-4; "JOSEPH","MOORE",-4; "LINDA","WHITE",1; "BARBARA","HARRIS",-1; "MARY","THOMAS",0; "JOHN","JOHNSON",-5; "DOROTHY","CLARK",1} 4, 1)

and returns "Maria" in cell B23.

Get excel sample file for this tutorial.

extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)

8. Extract negative values and adjacent cells (Excel Filter)

1. Select B2:D18
2. Press with left mouse button on "Data" tab
3. Press with left mouse button on "Filter" on the Ribbon
4. Press with left mouse button on Black triangle in cell D2
   
5. Select "Number Filters"
6. Press with left mouse button on "Less Than..."
   
   
7. Type 0 (zero)
8. Press with left mouse button on OK!

9. Count values by sign

The following formula works only in Excel 365, it extracts the signs from a cell range and puts the corresponding count next to the signs. For example, cell range B3:B16 has 14 numbers, some positive, some negative and one a zero.

The formula counts the positive numbers and returns 6, negative numbers 7 and one zero number.

Dynamic array formula in cell D3:

The LET function makes this formula much smaller and you also need to change the cell reference in one location only in the formula.

Explaining formula in cell D3

HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))

Step 1 - Calculate sign of number

The SIGN function returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.

Function syntax: SIGN(number)

SIGN(B3:B16)

becomes

SIGN({-22;-41;-31;38;0;-7;-43;38;-1;-1;31;45;20;44})

and returns

{-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1}

Step 2 - Create a unique distinct list of signs

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(SIGN(B3:B16))

becomes

UNIQUE({-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1})

and returns

{-1;1;0}

Step 3 - Calculate the frequency of each sign

The FREQUENCY function calculates how often values occur within a range of values and then returns a vertical array of numbers.

Function syntax: FREQUENCY(data_array, bins_array)

FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16)))

becomes

FREQUENCY({-1; -1; -1; 1; 0; -1; -1; 1; -1; -1; 1; 1; 1; 1},{-1;1;0})

and returns

{7;6;1;0}

Step 4 - Remove the last value

Delete the last value in the ouput array from the FREQUENCY function.

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1)

becomes

DROP({7;6;1;0},-1)

and returns

{7;6;1}

Step 5 - Stack arrays horizontally

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))

becomes

HSTACK({-1;1;0},{7;6;1})

and returns

{-1,7;1,6;0,1}

Step 6 - Shorten formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

HSTACK(UNIQUE(SIGN(B3:B16)),DROP(FREQUENCY(SIGN(B3:B16),UNIQUE(SIGN(B3:B16))),-1))

y - SIGN(B3:B16)
x - UNIQUE(y)

LET(y,SIGN(B3:B16),x, UNIQUE(y), HSTACK(x,DROP(FREQUENCY(y,x),-1)))

'SIGN' function examples

Functions in 'Math and trigonometry' category

The SIGN function function is one of 62 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Comments (7)