Conditional Formatting - Up Red Arrow and Down Green Arrow

Hi Helpers!

I have a KPI report I need trend arrows in, but based on 2 criteria, an up or down arrow based on whether there is improvement from previous and either red or green based on whether it is above threshold.

I've searched the forum and found this from 2016 but no matter how I tweak it I can't get it to work to my rules:

jason.b75 jason.b75 is offline
Forum Expert
Assuming value of interest in A1, enter this formula in B1, and fill down.
=IF(A1<0,CHAR(234),CHAR(233))
Apply the 'wingdings' font to column B.
Next apply conditional formatting to column B, using 2 formulas as rules.
First rule, changes font colour to green.
=B1=CHAR(234)
Second rule, changes font colour to red.
=B1=CHAR(234)
There are a couple of other arrow styles using the same font.

I've followed several multi step paths through conditional formattiong icon sets from the web but nothing seems to be giving the desired outcome, I have attached a sample sheet of what I want to achieve, although the arrows in the outcome cells are pasted in jpegs not from formula or formatting icons.

thanks in advance!

Jason

Thanks for taking the time to look at this, unfortunately I didn't save out any of the conditional formatting icon set routes from the web as none of them worked, I'll have a dig through web history but it was quite the rabbit hole I went down on this yesterday! It seems a common problem to have an upward red arrow and a downward green arrow but all the solutions were just against 1 criteria.

I've attached an update to the example sheet with the wingdings route i found from this forum from a few years ago

thanks again

Thank you so much! I've run it in and the arrows are the right way round but the colouring is off, I think I might have skewed something in the conditional formatting build? I built it 1 rule at a time from the new rule / use a formula to determine which cells to format path four times over and then used format painter to apply to to each of the corresponding cells, I've only ever worked with inherited, established conditional formatting before so am probably doing it the wrong way round, please see attached example

Hi there

That's great thank you so much!

I haven't closed the post yet as under either submit reply nor Go Advanced can I see the Add Reputation option, I've hovered over all the above options and they're all the formatting options, below the text box all I have is Post Icons and then additional options of Miscellaneous, Attachments, Subscription and Rate Thread.

I'd really like to use this add reputation feature, especially if I can go back into previously solved posts and add that retrospectively, I've had some very substantial assistance from this forum :D

thanks again for your help

Jason

Did some digging and found the route to adding rep, thanks again

Hi AliGW

I wasn't sure what the proper path was, this same query has developed, do I reopen it or start another post?

In short as well as the green for above and red for below they've added an amber category in between, so for example:

Green >= 85
Amber 81-84
Red <= 80

Thanks in advance

Jason

Hi AliGW

Thanks for all your input, after bending my head round trying to add this amber layer in they've removed it, so it's back to a single threshold with red and green above and below and arrows for improvement or decline, but they now also want to see a same as previous month icon as the current version shows an upward arrow if it's the same as previous month.

I've had a go in the attached so they can pick the look and feel they want but in Icon Sample 1 the Below Threshold and Declining and the Above Threshold and Same variants are returning the wrong colour, they look the same as the others and in the same order, I might have massively overcomplicated it in the way I've built it (I have pulled it back in from the version that has the client data in and lots of different versions against different thresholdS) and i might have just muddled it somewhere but can't see where for the life of me!

Thanks again for your help with this, I was just getting to the point where i thought i understood it!

Jason

Well according to the Add Reputation box i submitted i had thanked you, as i have repeatedly in my posts, thanks for your time but i'll work it out myself rather than be told off for inaccuracies.

Have a better day :D

Apologies, I might be a bit fried by the sun but I'm always ken to express my gratitude to the help I get from this forum for all the help Ive gotten from it over the years.

I can't see why Icon Set 1 is returning the wrong colour in E3 and E6. I've attached a workbook with a note in, its the 2 cells with thick borders that aren't behaving as expected, I've also added a word doc with a screen grab of the CF rules for each of the Icon Sample columns in the workbook.

Apart from the changes of CHAR refs for different icons they all look the same to me!

thanks again

Jason

Ha, typical, wheres the massive facepalm emoji when you need him!

Although I'm maybe more confused now, as the rules for column E where the error was all pointed to C3, it's the other columns, where the colouring was correct, that the C2 / C3 mix was!

I'm going to review this tomorrow BEFORE the sun gets to me, hopefully the cool be=reeze of morning will bring with it some enlightenment!

thanks again

Hey

I see clearly now (the heatstoke's gone!)

The problem I had with yesterday's review is that I was solely looking at the CF rules for Icon Set 1 / column E as they were the ones bringing through the wrong colours, everday has room for learning!

Thanks so much for your help with this, now they have the same as variant and options for what I will look like when I build it against all the different reporting lines I should hopefully be able to put this to bed and step quietly away from conditional formatting (for now), so for that alone, Thank you so much!