How to Use the SIGN Function in Google Sheets [Real-life Examples]

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.

The SIGN function example in Google Sheets

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?

SIGN Function as Replacement to OR

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.

SIGN Function 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.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.