Sometimes, we need the sign of any real number. Regarding the demand, Excel provides the **SIGN** function which returns the sign of number quickly. In this article, I’ll demonstrate the pros and cons of the **SIGN** function and also its usage.

The above figure shows the overview of the utilization of the **SIGN** function. Now, I’ll show you the uses of the function elaborately.

**Table of Contents**hide

## Introduction to The SIGN Function

The **SIGN** is a built-up Excel function, introduced in Excel 2003 version and also available in Excel Microsoft 365, returns the indication of number.

**Function Objective**

To obtain a number’s sign

**Syntax**

`SIGN (number)`

**Arguments Explanation**

Argument | Required/Optional | Explanation |
---|---|---|

number |
Required | The real number from which the sign in to be drawn out. |

**Return Value**

Return Value | Explanation |
---|---|

1 | if the number is positive |

0 | if the number is zero |

-1 | if the number is negative |

## How to Use The **SIGN **Function in Excel: 7 Effective Examples

Currently, we have a dataset about the price of some items based on different states of the US. Specifically, the price differs from the two months.

### 1. Basic Application of The SIGN Function

In the beginning, we’ll see the basic usage of the function that you have already seen in the previous figure. If you input the following formula simply, you’ll get the desired output immediately.

`=SIGN(B5)`

Moreover, you can utilize the **SIGN** function to change the sign of a list of numbers. For example, if you have a list of numbers like **B5:B10**, just use the formula below.

`=B5*SIGN(B5)`

Here, the **SIGN** function returns the sign of the list of numbers into positive.

### 2. Using The SIGN Function to Compare Two Data

As you see the price of the given items differs, you can compare the two data using the function. Just use the formula below.

`=SIGN(E5-D5)`

In the case of the first item i.e. Apple, the price is greater in February than that of January. That’s why the output is 1.

### 3. Determining Percentage Change Utilizing Excel SIGN Function

Furthermore, you can get the percentage change using the function. If the second price is higher than the first price, you’ll show a positive percentage number and vice versa.

The formula will be like the following-

`=(E5-D5)/D5*SIGN(D5)`

Here, **E5 **is the price of apple in February and **D5** is the price of the item in January.

As the price is greater in February than that of January, the function returns 17%.

### 4. Using SIGN Function Combined with IFERROR Function

The previous method is so easy but has a greater limitation. Because if any input is zero or not available (missing value), the function shows **#DIV/0!** Error.

To avoid such a situation, you need to add the **IFERROR** function first.

So the formula will be

`=IFERROR((K5-J5)/J5*SIGN(J5),0)`

Now, you see that the formula returns 0 instead of the error.

### 5. Operating Excel SIGN Function Jointly with VLOOKUP

If you want to display the change of the two prices as ‘down’ for negative change, ’equal’ for no change, and ‘up’ for positive change, then you can utilize the **VLOOKUP** function jointly with the **SIGN** function.

In that case, the formula will be-

`=VLOOKUP(SIGN(H5-G5),$B$5:$C$7,2)`

Here, **H5** is the price at February, **G5** is the price at January, **B5:C7** is a table where the sign is fixed to show ‘down’, ‘equal’, and ‘up’, **2** is the column index.

As the second price of ‘Apple’ is lower than the first price, the output shows ‘down’.

### 6. Employing SIGN Function with SUMPRODUCT Function

Again, if you want the total sales of a specific product of a certain state, you need the combination of **SUMPRODUCT **and **SIGN** functions.

Like the following formula.

`=SUMPRODUCT(--SIGN((B5:B15="Apple")+(C5:C15="Utah")),D5:D15)`

Here, **B5:B15** is the item range, **C5:C15** is the range of the states, and **D5:D15** is sales for a specific item.

**Formula Breakdown:**

Firstly, the **SIGN((B5:B15=”Apple”)+(C5:C15=”Utah”))** formula sums up if the item “Apple” and state “Utah” are matched, otherwise, it will return 0.

Finally, **SUMPRODUCT(–SIGN((B5:B15=”Apple”)+(C5:C15=”Utah”)),D5:D15)** returns the total of the desired item and state.

The above picture reveals the total sales of the Apple of Utah state.

### 7. Applying The SIGN Function with VBA Code

If you have a larger dataset, it is time-consuming and a little bit boring to get the required result using a formula.

Rather you can utilize the **VBA **code in Excel which performs the result rapidly and accurately.

Now, let’s see how you can apply the **VBA** code to get the sign of any real number.

**Step 1: **

Firstly, open a module by clicking **Developer**>**Visual** **Basic**>**Insert**>**Module**.

**Step 2: **

Then, copy the following code in your module.

```
Sub example_SGN()
Range("C5").Value = Sgn(Range("B5"))
End Sub
```

**Step 3:**

Finally, run the code.

Then you’ll see the following output.

**Notice:**

The following things are essential in the above **VBA** code.

- Worksheet name: Here, the worksheet name is “
**SIGN_VBA**” - Logic: The
**SGN**function finds the sign of a number. - Input Cell: Here, the input cell is
**B5**. - Output range: The output range is
**C5**.

## Things to Remember

Common Errors | Occurs If |
---|---|

#VALUE! |
The input is non-numeric |

#DIV/0! |
Any data is missing |

**Download Practice Workbook**

## Conclusion

This is how you can easily get the sign of any given number. Now, choose the methods based on your requirements. I strongly believe that this article will articulate your journey in Excel. If you have any queries or suggestions, please let me know in The comments section below.