Excel 2007 : comparing Sales

[mikebailey54]

Ok so lets say in A1 I have last years sales 100 and in A2 I have this years sales of 25. If i use the formula in A3 A2/A1*100 I get 25

But What I want A3 to show is icon set with red arrow pointing down And The real difference in sales is -75%

So I need a formula that will tell me if sales are up or down from last year.



Thank you for any help.
Math has never came easy for me

[Palmetto]

Your profile shows you use Excel 2003 which does not support icon sets. However on the assumption your profile is out of date and you are using Excel 2007, see the attached which shows how to use the icon set. Select cell C1 and choose Conditional Formatting from the Ribbon Menu, then choose Manage Rules.

Formula in C1: =(B1/A1)-1

Will show increase/descrease in sales.
For help on using icon sets in Excel 2007, try a Forum or Google search as there are tons of tutorials and videos on this topic.


EDIT:
For pre-Excel 2007, this formula will return an arrow symbol, provided the cell is formatted to use the Windgings font.

=IF(LOOKUP((B2/A2)-1,{-1,0,0.1},{1,2,3})=1,CHAR(234),IF(LOOKUP((B2/A2)-1,{-1,0,0.1},{1,2,3})=2,CHAR(232),CHAR(233)))

[mikebailey54]

Thank you so much for your help! and it works great if last years sales are higher than this years sales. But when using this formula and I make this years sales higher I get a different result.


Example:

A1 100.00 A2 25 (A2/A1)-1 = -75% Great

A1 25 A2 100 (A2/A1)-1 = 300% ????????


Could I use A if stament

If A1 > A2 then (A2/A1)-1 else (A1/A2)-1 Or something like that?

[Palmetto]

A1 25 A2 100 (A2/A1)-1 = 300% ?

I've uploaded a new workbook in my previous post as well as added another formula for pre-Excel 2007 versions.

Since it is a comparision the result is correct and this is illustrated in the revised workbook.
If last year the sales were 25K and this year they are also 25K, then sales increased 0%.

However, if this year the sales are 50K, then the increase is 100% over last year. 25K = base (0) [i.e. the benchmark to compare against], so another 25K on top of that equals 100% of the base.

If sales are 75K this year, then it is a 200% increase
Sales (75K) - base (25K) = 50K = 200% (or 25K (100%) + 25K (100%) = 200% increase)
Hopefully, you see the progression.

[mikebailey54]

Thank you for taking the time to help me.



It's now confirmed I'm a dork