SIGN Excel Function

SIGN in Excel (Table of Contents)

• SIGN Function
• SIGN Formula in Excel
• SIGN in Excel – Illustration
• How to Use SIGN Function?

SIGN Function in Excel

The Sign in Excel is a Maths/Trig function. It is a built-in function in Excel used to indicate the sign of any given number.

SIGN Formula in Excel

Arguments used for SIGN Formula in Excel

the number: 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 any cell reference. The SIGN function in Excel returns the sign (-1, 0 or +1) of the supplied numerical argument.

Output:

The SIGN Formula in Excel has only three outputs: 1, 0, -1.

• If number is greater than zero, the SIGN in Excel will return 1.
• If number is equal to zero, the SIGN in Excel will return 0.
• If number is less than zero, the SIGN function will return -1.

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

SIGN in Excel – Illustration

Suppose you have a list of numbers {2.1, 3, -4, 1, 0, -7} in A3:A8 as shown below and you want to find the sign of each number.

For the first number in A3, you can input the SIGN Formula—

=SIGN(A3)

It will return 1.

You can now drag it to rest of the cells till B8 to get the sign of each number.

Instead of giving the cell references, you can also provide the number as it is as shown below.

=SIGN(2.1)

It will return the same output.

 

How to Use SIGN Function?

The SIGN function is a built-in function in Excel and is mostly used in association with other Excel functions. Let us see SIGN in Excel examples.

SIGN in Excel Example #1

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

Some of the 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)

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

Now, drag it to get the value for the rest of the cells.

SIGN in Excel Example #2

In the above example, you may also want to calculate the percentage increase/decrease with respect to the previous year.

To do so, you can use the following SIGN Formula:

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

and drag it to the rest of the cells.

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

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

In order to get the overall % increase or decrease, you can use the following SIGN formula:

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

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

SIGN in Excel Example #3

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

Now, you want to change the sign of each of the negative number to positive.

You may simply use the following SIGN Formula:

=B3 * SIGN(B3)

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 280.

Now, drag it get the values for rest of the numbers.

SIGN in Excel Example #4

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

To do so, you can use the following SIGN Formula—

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

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

The SIGN function will compare the current and previous month sales using the SIGN function, and VLOOKUP will pull the information from the VLOOKUP table and return whether the sales are going Up, zero or down.

and drag it to the rest of the cells.

SIGN in Excel Example #5

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

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

It can be calculated as:

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

Let us see the above SIGN Function in detail.

B4:B15 = “EAST”

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”

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”)

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”))

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)

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.

This will finally return 23,300.

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)

and for product A in East zone

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

Things to Remember About SIGN Function in Excel

• The SIGN function provides the sign of the given input number.
• SIGN in Excel takes as input only a number. This number can be a cell reference or an output from another operation.
• The SIGN function returns any of the three {+1, 0, -1}.
• If the number is positive, the SIGN in Excel will return +1
• If the number is negative, the SIGN in Excel will return -1
• If the number is zero, the SIGN in Excel will return 0.
• If the argument is a non-numeric value, the SIGN in Excel will return #VALUE!

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

Recommended Articles

This has been a guide to SIGN Function in Excel. Here we discuss the SIGN Formula in excel and how to use SIGN function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• VLOOKUP Multiple Criteria
• COUNTIF Multiple Criteria
• FIND Excel
• MONTH Excel
• YEAR Excel Function
• CLEAN Excel Function
• DATE Excel Function
• COUNTIF Function in Excel
• COUNT Function in Excel