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!

 

[AD_Taylor]

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))

This is great and does work, however, it only works correctly once all cells in the range have a value in them. If for example a cell is left blank or a value is entered that isn't in the points score table then the formula returns an error.

I've been trying to modify it myself but having no luck. I think what needs to be done is to tell the formula that if a specific cell causes an error to remove it from the range or do nothing with it.

I tried:

=SUMPRODUCT( --(((MATCH( IF( $J17:$J21 <> "", $J17:$J21, FALSE),KS0Pts,0)-MATCH( IF(L17:L21 <> "",L17:L21, FALSE),KS0Pts, 0)))<0) )

entered as an array formula (Ctrl + Shift + Enter), with FALSE entered in the points table. This removes the error but because I've placed FALSE at the end of the points table it returns a high value, meaning the subtraction will always return a value <0, and therefore the count becomes inaccurate.

Is it possible to include any kind of error checking that would make this work?

Hope this makes sense! And thanks again for all the help so far!

 

[AD_Taylor]

Again it won't let me edit, but:

or a value is entered that isn't in the points score table

Here I mean that there is a possibility of 'Abs' being entered into Column K. 'Abs' or Absent should not be treated as a grade and therefore there should have no colour coding and not be included in the count. It should be treated as similar to a blank.

 

[AD_Taylor]

Well sorry for the triple post but

With a mass load of error checking (it could probably be shortened :P) I now have a working formula!

Both of the below are entered as array formulas

For getting values > 0:
=SUMPRODUCT(--(( IF( ISERROR( IF(ISERROR(MATCH($J21:$J281,KS0Pts,0)),"",MATCH($J21:$J281,KS0Pts,0)) - IF(ISERROR(MATCH(K21:K281,KS0Pts,0)),"",MATCH(K21:K281,KS0Pts,0)) ), -10, IF(ISERROR(MATCH($J21:$J281,KS0Pts,0)),"",MATCH($J21:$J281,KS0Pts,0)) - IF(ISERROR(MATCH(K21:K281,KS0Pts,0)),"",MATCH(K21:K281,KS0Pts,0) ) ) )>0))


And for getting values < 0:

=SUMPRODUCT(--(( IF( ISERROR( IF(ISERROR(MATCH($J21:$J281,KS0Pts,0)),"",MATCH($J21:$J281,KS0Pts,0)) - IF(ISERROR(MATCH(K21:K281,KS0Pts,0)),"",MATCH(K21:K281,KS0Pts,0)) ), 10, IF(ISERROR(MATCH($J21:$J281,KS0Pts,0)),"",MATCH($J21:$J281,KS0Pts,0)) - IF(ISERROR(MATCH(K21:K281,KS0Pts,0)),"",MATCH(K21:K281,KS0Pts,0) ) ) )<0))


First of all I check if any of the values in my range are causing errors in the match formula. If they are I replace them with a blank ("") to sort of elimate them from the data.

Then when a "" is subtracted from a number, or vice versa, another error will occur. The second error check wrapped around this part, puts a value that will not be included by the count if the error happens.
E.g. If I am counting <0 and a "" is found, I put +10 so that this result is not counted.

Bit hard to get my head around but like I say it works

Really appreciate the help p45cal, couldn't have got this far without it!

 

[p45cal]

Good that you have it working - I'm not going to try and shorten it - but having looked at it very briefly, you say that you use 10 and -10 to prevent inclusion in the count, why not use 0 here in both formulae instead? (I may have missed something.)

 

[Peter_SSs]

Again it won't let me edit

FYI, you can only edit your post for 10 minutes after making it.

 

[AD_Taylor]

Good that you have it working - I'm not going to try and shorten it - but having looked at it very briefly, you say that you use 10 and -10 to prevent inclusion in the count, why not use 0 here in both formulae instead? (I may have missed something.)

Yes in my original post I only needed to check >0 and <0. The needs have now expanded to have to check =0 (Grades are equal) and =-1 (Actual grade is 1 below target). Luckily the formula works for all 4 conditions so no problems

 

[AD_Taylor]

FYI, you can only edit your post for 10 minutes after making it.

Thanks Peter - thought it was something I was doing wrong