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:
The formula used in column C is:
I also added Conditional Formatting for bold green or bold red based on the result of the IF statement:
Another approach to this same issue would be to use a combination of VLOOKUP and the SIGN functions. Here is the formula:
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:
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!