FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

SIGN Excel Function

Updated on January 2, 2024
Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

SIGN Function in Excel

The SIGN function in Excel is a Maths/Trig function that gives us this result. The SIGN function returns the sign (-1, 0, or +1) of the supplied numerical argument. The SIGN formula in Excel can be used by typing the keyword: =SIGN( and providing the number as input.

For example, suppose we have a dataset of sales of a product for two years in cells A1(80,000) and B1(50,000). Now, we want to see if there is an increase in the figure compared to last year. In such a scenario, we can use the following SIGN formula:

=SIGN(A1-B1)

=+1.

Syntax

SIGN Formula in Excel

Arguments

the number: It is the number to get the sign for.

The input number can be any number entered directly or in the form of any mathematical operation or cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

Output:

The SIGN formula in Excel has only three results: +1, 0, and -1.

  • If the number exceeds zero, the Excel SIGN formula will return 1.
  • If the number equals zero, the SIGN formula in Excel will return 0.
  • If the number is less than zero, the SIGN formula in Excel will return -1.

If the supplied number argument is non-numeric, the Excel SIGN function will return #VALUE! Error.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

SIGN Excel Function Video

How to Use the SIGN Function in Excel? (with Examples)

You can download this SIGN Function Excel Template here – SIGN Function Excel Template

Example #1

Suppose you have the final balance figures for seven departments for 2016 and 2017, as shown below.

Departments20162017
Department A1000012000
Department B-1890-2000
Department C250000320000
Department D8000060000
Department E-50000-40000
Department F-10002500
Department G1200012500

Some departments are running in debt, and some are giving good returns. Now, you want to see if there is an increase in the figure compared to last year. To do so, you can use the following SIGN formula for the first one.

=SIGN(D4 – C4)

SIGN Example 1-1

It will return +1. The argument to the SIGN function is a value returned from other functions.

SIGN Example 1-2

Drag it to get the value for the rest of the cells.

SIGN Example 1-3

Example #2

In the above example, you may also want to calculate the percentage increase in excelCalculate The Percentage Increase In ExcelPercentage increase = (New Value - Old Value)/ Old Value. Instead of showing the delta as a Value, percentage increase shows how much the value has changed in terms of percentage increase.read more concerning the previous year.

For that, you can use the following SIGN formula:

=(D4 – C4)/C4 * SIGN(C4)

SIGN Example 2

 Drag it to the rest of the cells.

SIGN Example 2-1

If the balance for 2016 is zero, the function will give an error. Alternatively, we may use the following SIGN formula to avoid the error:

=IFERROR((D4 – C4) / C4 * SIGN(C4), 0)

To get the overall percentage of increase or decrease, you can use the following formula:

(SUM(D4:D10) – SUM(C4:C10)) / SUM(C4:C10) * SIGN(SUM(C4:C10))

SIGN Example 2-2

SUM(D4:D10) will give the net balance including all departments for 2017

SUM(C4:C10) will give the net balance including all departments for 2016

SUM(D4:D10) – SUM(C4:C10) will give the net gain or loss, including all departments.

(SUM(D4:D10) – SUM(C4:C10)) / SUM(C4:C10) * SIGN(SUM(C4:C10)) will give the percentage gain or loss

Example #3

Suppose you have a list of numbers in B3:B8, as shown below.

SIGN Example 3

Now, you want to change the negative number’s sign to positive.

You may use the following formula:

=B3 * SIGN(B3)

SIGN Example 3-1

If the B3 is negative, SIGN(B3) is -1, and B3 * SIGN(B3) will be negative * negative, which will return positive.

If the B3 is positive, SIGN(B3) is +1, and B3 * SIGN(B3) will be positive * positive, which will return positive.

It will return to 280.

SIGN Example 3-2

Drag it to get the values for the rest of the numbers.

drag the values

Example #4

Suppose you have your monthly sales in F4:F10 and want to find out if your sales are going up and down.

To do so, you can use the following formula:

=VLOOKUP(SIGN(F5 – F4), A5:B7, 2)

A5:B7 contains the information on the up, zero, and down.

The SIGN function will compare the current and previous month’s sales using the SIGN function. The VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more Excel function will pull the information from the VLOOKUP table and return whether the sales are going up, zero, or down.

vlookup sign

Drag it to the rest of the cells.

SIGN Example 4-1

Example #5

Suppose you have sales data from four zones: East, West, North, and South for products A and B, as shown below.

ZoneProductQuantityPriceSales
EastA210002000
EastB115001500
WestA412004800
NorthA315004500
NorthB510005000
WestB10130013000
SouthA612007200
SouthB12150018000
EastA311003300
NorthB412004800
WestB513006500

Now, you want the total sales for product A or East zone.

It can be calculated as:

=SUMPRODUCT(SIGN((B4:B15 = “EAST”) + (C4:C15 = “A”)) * F4:F15)

sumproduct

Let us see the above SIGN function in detail.

B4:B15 = “EAST”

It will give 1 if it is “EAST” else it will return 0. It will return {1, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0}

C4:C15 = “A”

It will give 1 if it is “A” else it will return 0. It will return {1, 0, 1, 1, 0, 0, 1, 0, 1, 0, 0}

(B4:B15 = “EAST”) + (C4:C15 = “A”)

It will return sum the two and {0, 1, 2}. It will return {2, 2, 1, 1, 0, 0, 1, 0, 2, 0, 0}

SIGN((B4:B15 = “EAST”) + (C4:C15 = “A”))

It will then return {0, 1} here since there is no negative number. It will return {1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 0}.

SUMPRODUCT(SIGN((B4:B15 = “EAST”) + (C4:C15 = “A”)) * F4:F15)

It will first take the product of the two matrix {1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 0} and {2000, 1500, 4800, 4500, 5000, 13000, 7200, 18000, 3300, 4800, 6500} which will return {2000, 1500, 4800, 4500, 0, 0, 7200, 0, 3300, 0, 0}, and then sum it.

It will finally return 23,300.

sales in product a

Similarly, to calculate the product sales for East or West zones, you may use the following SIGN formula:

=SUMPRODUCT(SIGN((B4:B15 = “EAST”) + (B4:B15 = “WEST”)) * F4:F15)

For product A in East zone:

=SUMPRODUCT(SIGN((B4:B15 = “EAST”) * (C4:C15 = “A”)) * F4:F15)

Recommended Articles

This article is a guide to SIGN Function in Excel. Here, we discuss the SIGN formula in Excel and how to use the SIGN function, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *