Tricky Conditional Formatting Problem

[AD_Taylor]

Hi,

I am trying to do some colour coding based on GCSE/KS3 levels that are entered. There is one column, column J, that stores a students target grade. The other columns (about 10) should reference that column to see whether their grade is higher or lower and colour accordingly.

As Excel doesn't like comparing 2 strings together, I have 2 lookup tables that give each grade a relevant point score, 1 for KS3 and 1 for GCSE. These point scores are then compared to each other to get a reference for the colour coding.

The problem is with all the error checking and point score comparisons the formula has become very long. So long in fact that it doesn't even seem to want to paste into the conditional formatting box.

I've tried the formula in a cell on the sheet and it works from there perfectly. Heres the formula:

 = IF(AND( LEN(K15) > 0, LEN($J15) > 0), IF( VLOOKUP("yearGrp", settingsLkUp, 2, FALSE) <= 8, IF( OR( ISERROR(VLOOKUP(K15, KS3Pts, 3, FALSE)), ISERROR( VLOOKUP($J15, KS3Pts, 3, FALSE))), 0, VLOOKUP(K15, KS3Pts, 3, FALSE)  - VLOOKUP($J15, KS3Pts, 3, FALSE)),   IF( OR( ISERROR(VLOOKUP(K15, KS4Pts, 3, FALSE)),  ISERROR( VLOOKUP($J15, KS4Pts, 3, FALSE))), 0, VLOOKUP(K15, KS4Pts, 3, FALSE) - VLOOKUP($J15, KS4Pts, 3, FALSE) ) ),  0)

If the answer comes out negative i.e. the grade is lower, red colouring should be used and green for a positive result.

I found a thread on here mentioning the use of a named formula/range to help out the size but I couldn't get that to work properly either.

Any help is greatly appreciated!

 

[p45cal]

Well, it is quite a long formula!
I suspect this could be shortened. Could you post what's in:
settingsLkUp
KS3Pts
KS4Pts

and perhaps some examples of what might appear in K15 and J15.

I see also that you're looking up a hardcoded "yrGroup" in settingsLkUp; does this mean that the values in settingsLkUp can change? (Otherwise the result will always be the same, so you may as well hard code the result there.)

When you say "get a reference for the colour coding" you mean this formula returns a number (positive or negative) that represents how many grades the actual grade is away from the target grade, and then you want to use that number in conditional formatting?

Finally, because this is for work(?) could you confirm it's Excel 2003?

 

[AD_Taylor]

Hi thanks for the reply,

settingsLkUp is just a 2 column reference to the settings sheet. The yearGrp bit can change and can be any number from 7 to 13. So this part just tells it to grab the year groups number.

KS3Pts is another reference. 3 columns this time, the Value, a Description and the Points Score. All levels have an a, b, c and go up/down in points of 2

E.g.
Value, Description, Points
4a, Level 4a, 29
4b, Level 4b, 27
4c, Level 4c, 25
3a, Level 3a, 23

KS4Pts is a similar reference, 3 columns and looks like the following. All grade values go up/down in points of 6.

E.g.
Value, Description, Points
A+, A+ Grade, 58
A, A Grade, 52
B, B Grade, 46

(Not sure how to post it any other way sorry.)

In K15 and J15 any of the grades/levels may appear so, A+, A, 4b, 4c etc.
These will have Data Validation to make sure they are the only ones that can be entered.

Yes the reference will be used to decide the colour coding. So if the formula comes out positive it will do one colour code, and negative the other colour code.

And finally yes this is for work so Excel 2003. Needs to be able to run on 2010 as well though which I assume it will.

Thanks again.

 

[p45cal]

In Excel 2003's conditional formatting, you only have 3 conditions available and each one has to resolve to True or False. Your formula returns numeric differences between grades. How are you intending to use these values in conditional formatting to return True/False?

 

[AD_Taylor]

Probably should have explained that bit :P

Basically a " > 0" on the end of the one and a "< 0" on the end of the other?

I think that would work so it checks the number that comes out of the formula to whether it is positive or negative?

 

[p45cal]

The reason I ask is because at the moment your formula goes to the trouble of looking up values in a table and returning values, then subtracts those values from each other. If your tables of grades to points is always in order we might be able to just ask whether one grade is above/below/the same as the other, which could be determined merely by position in the list. If so, we could use Match, what's more we could consolidate the grades table to a single table, 1 column wide, so deciding which yearGrp becomes unnecessary. (Actually, you could consolidate the tables anyway with your existing formula).
Additional shortening can be achieved; instead of checking each bit for errors (you always seem to return 0 if an error is found), check the whole formula once for any error and return 0 if present.

Without error checking the formula:
=MATCH($J15,KS0Pts,0)-MATCH(K15,KS0Pts,0)
would be the row difference of the two grades in the consolidated table KS0Pts, so it could be equal, positive or negative.
Including error checking you'd get:

=IF(ISERROR(MATCH($J15,KS0Pts,0)-MATCH(K15,KS0Pts,0)),0,MATCH($J15,KS0Pts,0)-MATCH(K15,KS0Pts,0))

Now all you have to do in conditional formatting is to ask whether this value >0 or <0 (and perhaps =0 if you wanted).

The one thing that wouldn't be checked is if someone had put mixed grades in one row, say 3a for the target and A for the actual.

Taking the data you gave, the consolidated table KS0Pts could look like:
<table border="0" cellpadding="0" cellspacing="0" width="64"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt;width:48pt" height="20" width="64">A+</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">4a</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">4b</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">4c</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl64" style="height:15.0pt" height="20">3a</td> </tr> </tbody></table>

or perhaps:
<table border="0" cellpadding="0" cellspacing="0" width="64"><colgroup><col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:48pt" height="20" width="64">A+</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">4a</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">A</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">4b</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">4c</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">B</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3a</td> </tr> </tbody></table>it wouldn't matter as long as each yrGroup was in order within the table.

 

[AD_Taylor]

Thanks for this p45cal,

It looks a lot better than mine! It would be great if it could check for mixed grades on one row, but I'm sure I could build in something along these lines to fix it.

Unfortunately I'm quite busy with something at the moment and won't get a chance to test this soon. I'll let you know when I do.

Thanks again,
Adam

 

[AD_Taylor]

Okay well that busy-ness ended a lot sooner than I thought!

Had a look and a play around with it and to be honest its an amazing formula!
Works perfectly for the purpose I had in mind

Now I just have one last problem which is extra to my original question...

Is there a way to count these results?

E.g. So in Column J are the targets and in Column K are the grades which are now colour coding appropriately. At the top of Column J I'd like to have a couple of rows, the first of which counts how many green formats there are, and the other to count how many red formats there are.

So in more basic terms a count of how many times the MATCH formula returns < 0, and a second count of how many times it is > 0.

If this is possible for a column, i.e. J:J, would it also be possible for a single range, e.g. K15:M15?

Thanks again for your time in looking at this, really do appreciate it!

 

[AD_Taylor]

Annoyingly it won't let me edit my post now but,

At the top of Column K I'd like to have a couple of rows, the first of which counts how many green formats there are, and the other to count how many red formats there are.

I meant Column K not J here

 

[p45cal]

There's no error checking in the following, but try these:

=SUMPRODUCT(--(((MATCH($J15:$J21,KS0Pts,0)-MATCH(K15:K21,KS0Pts,0)))[COLOR=Red]<[/COLOR]0))
=SUMPRODUCT(--(((MATCH($J15:$J21,KS0Pts,0)-MATCH(K15:K21,KS0Pts,0)))[COLOR=Red]>[/COLOR]0))