How To Track Daily Sales Using The SIGN Function In Excel

In this tutorial we’ll take a look at two ways to add a simple indicator to your data to track changes in values from day to day, or period to period. One way will use a nested IF statement, while the other will use a more rarely accessed function in Excel called the SIGN function.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a range of dates and daily sales. In column C I’ve added an indicator to show whether the day’s sales went up, down, or stayed flat from the previous day:

101815-1

The formula used in column C is:

=IF((B4-B3)<0,”Down”,IF((B4-B3)>0,”Up”,”Flat”))

I also added Conditional Formatting for bold green or bold red based on the result of the IF statement:

101815-2

Another approach to this same issue would be to use a combination of VLOOKUP and the SIGN functions. Here is the formula:

=VLOOKUP(SIGN(B3-B2),$F$12:$G$14,2)

As you can see below, I’ve set up a small VLOOKUP table and used the result of the SIGN function to choose the appropriate result from column 2:

101815-3

How the SIGN function works is that it returns a “1” if the number (or result of a formula) is positive, a “-1” if it’s negative, or a “0” if zero. So, by doing a VLOOKUP based on the result of the SIGN function comparing the current and previous days sales, we can pull from the VLOOKUP table the column 2 result of Down, Flat, or Up.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

One Response to How To Track Daily Sales Using The SIGN Function In Excel

  1. Hagar abdelaziz February 1, 2017 at 2:38 am #

    Need help in MATCH function

Leave a Reply