This new tutorial is about the use of SIGN function in Google Sheets. You can use this function as part of complex formulas.
The function SIGN is a built-in Google Sheets function that categorized under the Math functions. What is the use of the SIGN function in Google Sheets?
The purpose of the SIGN function is to evaluate and return the sign of numbers as below.
The SIGN function returns 1 if the input value is positive and -1 if the input value is negative. For the input value 0, it returns 0.
The Syntax and Examples of the SIGN Function in Google Sheets
Syntax:
SIGN(value)
Example: The formulas and results have shown in the image below.
Now let me take you to one real-life use of the SIGN formula in Google Sheets.
Real-life Use the SIGN Function in Google Sheets
The Usage of SIGN Function as Replacement to OR Logical Test:
You can use SIGN function as an equivalent to the OR logical test. How to do that?
In the above test, if either of the values in A1 or B1 is greater than 50, the formula result would be TRUE else FALSE. Remember! TRUE means the number 1 and FALSE is equal to 0.
See how the SIGN function replaces the OR logical test below.
=sign((A1>50)+(B1>50))
This SIGN formula would return 1 if any of the value is >50 else 0. There is a specific purpose in the use of SIGN here. If both the conditions are true, without SIGN, the formula would return 1+1=2.
The function SIGN can return the sign of the value 2 and that would be 1. So it’s equal to TRUE.
I normally use the SIGN function in SUMPRODUCT! Yes! I know this is a new tip for you. See how?
The SIGN Function in SUMPRODUCT in Google Sheets:
We can use the above same logic in SUMPRODUCT.
Before explaining this SIGN and SUMPRODUCT combo, I would like to draw your attention to this normal SUMPRODUCT formula.
=sumproduct(D2:D5,E2:E5)
It finds the product of column D and E and which is equal to;
=D2*E2+D3*E3+D4*E4+D5*E5
Now see the screenshot for the SIGN and SUMPRODUCT combo. That formula finds the product of the Column D and E, if column C value is <500 or Column D value is <25.
Similar:Â How to Use OR Condition in SUMPRODUCT in Google Sheets
Hope you can understand where you can use the SIGN function in Google Sheets.
