Issue with Excel Handling Conditional Formatting

Question

I have a table which updates the rankings for global banks. It looks something like this:

Each day it updates the rankings and shows the last four weeks.

I want to use conditional formatting so that if a bank moves up in the rankings from one week to the next, it's highlighted in Green, and if it moves down, it's highlighted in Red.

So for instance RBC during 10/19/2016 would be highlighted in Red, and during 10/26/2016 it would be highlighted in Green.

This is problematic to apply with conditional formatting because the positions of the conditional cells aren't fixed and will move around as the rankings change. For instance if I'd written something like F24 < G24 as my conditional rule, it would be comparing RBC's ranking with CIBC's ranking rather than last week's RBC ranking. I need to use relative references here and overlapping formulas and I don't see a way to do that with conditional formatting rules available.

I think I could wrap the formulas in an IF statement to get around this, but I don't think there's a way to apply conditional formatting to an IF statement. How can I conditionally format the way that I need to?

KromeWing · Accepted Answer

I tryed the Nathan_Sav's solution, but it didn't work because I inserted a header in each column (since you have a date as header). This happens because the match result is no longer aligned with the row index when you have headers.

The solution I found (based on Nathan's solution) is to compare the match result of the previous column with the match result of the selected column. Would be something like this:

=MATCH(B3,A$2:A$11,0) for RED



=MATCH(B3,A$2:A$11,0)>MATCH(B3,B$2:B$11,0) for GREEN

And yes, this is a rule for conditional formatting formula.



I tryed to comment on Nathan's answer, but I don't have reputation to do so...

Hope that helps!

Issue with Excel Handling Conditional Formatting

Answers (2)

Related Questions