What Is SIGN Google Sheets Function?
The SIGN Google Sheets function returns the preset return values based on the SIGN of the real integers or the numeric values in a given dataset. The return values are ‘-1’ for a negative number, ‘0’ for a cell with 0 as a value and ‘+1’ for a positive number.
Using the SIGN function in Google Sheets, we can get the values directly or find the SIGN for the results of a formula.
Use this SIGN Google Sheets Function Template to follow along with the examples in this article.
Download Excel TemplateFor example, we will use the SIGN Google Sheets function to find the SIGN for the given data.
Select cell B2, enter the formula =SIGN(A2-A3), and press the “Enter” key.
The result is returned as “-1”, as shown above. In cell B2, first, the difference of the cells is calculated, (A2-A3), i.e., (4-7), and the result is -3. Since the result is a negative number, the return value of the SIGN is -1, as shown.
Key Takeaways
- The SIGN Google Sheets function checks the SIGN of the entered number, whether it is positive, 0 or a negative number and returns the respective value.
- The result can be the direct SIGN value of the selected numeric value or the SIGN value of the result of an executed formula.
- The function returns the SIGN values as follows:
- If the “value” is in the range “-∞, 0”, it will return -1.
- If the “value” is equal to “0”, it will return 0.
- If the “value” is in the range “0, ∞”, it will return 1.
SIGN() Google Sheets Formula
The syntax of the SIGN Google Sheets formula is,
The mandatory argument of the SIGN Google Sheets formula is,
- value: The cell value, cell reference or the result of a calculation to find the SIGN value, whether it’s a Zero, positive or negative number.
How To Use SIGN Google Sheets Function?
We can use the SIGN Google Sheets function in 2 ways, namely,
- Access from the Google Sheets ribbon.
- Enter in the worksheet manually.
Method #1 – Access from the Google Sheets Ribbon 🡢
Choose an empty cell for the output 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “Math” option right arrow 🡢 select the “SIGN” function, as shown below.
The “SIGN” formulaappears, as shown below. Enter the argument as the cell reference.
Method #2 – Enter in the Worksheet Manually 🡢
- Select an empty cell for the output.
- Type = SIGN( in the selected cell. [Alternatively, type =S or =SI and double-click the SIGN function from the list of suggestions shown by Google Sheets.]
- Enter the argument as cell values or cell references.
- Close the brackets and press the “Enter” key.
Examples
We will understand some advanced scenarios using the SIGN Google Sheets examples.
Example #1
We will find the positive SIGN of the values using SIGN Google Sheets function.
In the table, the data is,
- Column A contains the Value1.
- Column B contains the Value2.
- Column C displays the Output.
The steps to find the sign value using the SIGN Google Sheets Function are,
Step 1: Select cell C2, enter the formula =SIGN(A2-B2), and press the “Enter” key. The result is “1”, as the difference between cells A2 and B2 is a positive number.
Step 2: Drag the formula from cell C2 to C5 using the fill handle.
The output is shown above as +1 since the difference of the values are all positive numbers.
Example #2
We will find the negative SIGN of the values using the SIGN Google Sheets function.
In the table, the data is,
- Column A contains the Value1.
- Column B contains the Value2.
- Column C displays the Output.
The steps to find the sign value using the SIGN Function are,
Step 1: Select cell C2, enter the formula =SIGN(A2-B2), and press the “Enter” key. The result is “-1”, as the difference between cells A2 and B2 is a negative number.
Step 2: Drag the formula from cell C2 to C5 using the fill handle.
The output is shown above as -1 since the difference of the values are all negative numbers.
Example #3
We will find the zero SIGN of the values using the SIGN Google Sheets function.
In the table, the data is,
- Column A contains the Value1.
- Column B contains the Value2.
- Column C displays the Output.
The steps to find the sign value using the Google Sheets SIGN Function are,
Step 1: Select cell C2, enter the formula =SIGN(A2-B2), and press the “Enter” key. The result is “0”, as the difference between cells A2 and B2 is 0.
Step 2: Drag the formula from cell C2 to C5 using the fill handle.
The output is shown above as 0 since the difference of the values are all 0.
Example #4
We will find the sign of the errors using the SIGN Google Sheets function.
In the table, the data is,
- Column A contains the Value1.
- Column B contains the Value2.
- Column C displays the Output.
The steps to find the sign value using the SIGN Google Sheets Function are,
Step 1: Select cell C2, enter the formula =SIGN(A2-B2), and press the “Enter” key. The result is a “#VALUE!” error, as the difference between cells A2 and B2 is an error.
Step 2: Drag the formula from cell C2 to C5 using the fill handle.
The output is shown above as errors since the difference of the values are all errors, because the dataset consists of textual values, symbols, etc.
Important Things to Note
- The “#VALUE!” error occurs when the argument entered,
- Contains non-numeric values, such as letters, alpha-numeric, symbols, special characters or empty or blank cells.
- Contains a cell range for the value argument. Therefore, we must select only one cell reference for the function and not a cell range.
- When the cell value is a text directly entered as an argument like =sign(a), then, we get the “#NAME?” error.
Frequently Asked Questions
We often forget in which category a function falls, here, the “SIGN” function. Then, we can insert the function as follows:
Choose an empty cell 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “All” option right arrow 🡢 select the “SIGN” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
A few reasons the SIGN function may not work are:
•The value selected is a text and is inserted directly with or without double-quotes.
• The cell references selected is a text value, alpha-numeric, empty or a blank cell.
• The calculated result selected as a cell reference is an Error.
For example, we will find the SIGNs using the SIGN function for the values in different formats. In the table, the data is,
• Column A contains the Value.
• Column B displays the Output.
The steps to calculate the value by the SIGN Function in Google Sheets are as follows:
Step 1: Select cell B2, enter the formula =SIGN(A2), and press the “Enter” key. The result is “1” a positive number sign, as shown below.
Step 2: Drag the formula from cell B2 to B4 using the fill handle.
As shown in the output above, the Google Sheets SIGN function shows the following results.
a. Positive, such as 2, the function returns 1.
b. Negative, such as -2, the function returns -1.
c. Zero, the function returns 0.
Alternatively, we can find the Functions icon to insert the SIGN function by following the path shown below.
• Choose an empty cell 🡢 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🡢 click the “All” option right arrow 🡢 select the “SIGN” function, as shown below.
Use this SIGN Google Sheets Function Template to follow along with the examples in this article.
Download Excel Template