WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » SIGN Excel Function

SIGN Excel Function

SIGN Function in Excel

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

Syntax

SIGN Formula in Excel

Arguments

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.

Output:

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

  • If the number is greater than zero, the SIGN formula in excel will return 1.
  • If the number is equal to 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.

How to Use 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.

SIGN Example 1

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)

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

Now, 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 excel with respect to the previous year.

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

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

SIGN Example 2

and drag it to the rest of the cells.

SIGN Example 2-1

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:

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

In order to get the overall % 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 sign of each of the negative number to positive.

You may simply 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

Now, 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 you want to find if your sales are going up and down.

To do so, you can use the following 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.

vlookup sign

and drag it to the rest of the cells.

SIGN Example 4-1

Example #5

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

SIGN Example 5

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)

sumproduct

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.

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)

and for product A in East zone

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

SIGN Excel Function Video

Recommended Articles

This has been 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 example and downloadable excel templates. You may also look at these useful functions in excel –

  • VLOOKUP Multiple Criteria
  • COUNTIF Multiple Criteria
  • SUMPRODUCT in Excel
  • MONTH Excel
2 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download SIGN Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More