CODEWITHSUNDEEP
excel-2010kpi
Lozza1234
Lozza1234

Reputation: 27

using excel to show Increase, Decrease and no change in Multiple colours

Looking for some help or to be pointed in the right direction. Been stuck on this for a while and the main issue is I don't even know where to start. I am sure there is a solution but my brain will not see it.

To use as an example I have a table that shows monthly results for multiple different areas:

enter image description here

What I want to do is in the gap along side each number is show an increase, decrease or no change, using up arrow down arrow and square. That I can do using conditional formatting my issue comes with the fact that I also want it to be multiple Colours. So it will take into account increase and decrease and whether they are in target.See below:enter image description here

Atm I am copying and pasting each month. But having it automated would be amazing. The outcome would hopefully look like this:enter image description here

I am hoping there is a way that I can do two things compare to the previous month and then check it against my table to see where it sits then display the appropriate symbol.

Thank you in advance for any help or a place to start.

Upvotes: 1

Views: 10984

Answers (2)

Peyton Westlake
Peyton Westlake

Reputation: 1

No need for a long, complex formula

=IF(A2>A1,"t",IF(A2<A1,"u","v")) - set the font to Marlett

Upvotes: 0

Lozza1234
Lozza1234

Reputation: 27

So extremely easy to figure out once I looked at it in a simplistic way.

Firstly, I used: =IF(-SIGN(E26-G26)= 0, "n", IF(-SIGN(E26-G26)=1, "é", IF(-SIGN(E26-G26)=-1, "ê","")))

To compare the current number to the previous number and get a 1, 0 or -1 depending on if it had gone up down or stayed the same, using this it either displays é, n or ê. I then set the font on these cells to Wingdings. so they become up arrow, down arrow and a square.

Then I use conditional formatting to colour it based on the adjacent cells value.

Upvotes: 0

Related Questions