conditional formatting whilst comparing column data

[Rachael000]

hi... i hope someone can help with this, but i've got a spreadsheet where i have predicted gardes in column A, and actual grades in column b... i need to highlight where column B differs from column A, for example:


Pred.grade in A2="d", actual grade in B2="f". i would like cell B2 to show as RED (colour) because it is lower than the grade in A2.


If A2="f" and B2="d", then i would need B2 to show as GREEN (colour) as the grade in B2 is higher than the grade in A2.


Basically, i have the grades a-f, and i need column B to highlight where the students have done better / worse than their predicted grades. short of amending each cell individually (i have over 3000 entries), i don't know how to do this!


Would appreciate any help offered!


Thank you

 

[Luke M]

CF formula for Worse than expected:

=A2<B2

Format, pattern color RED


CF formula for Better than expected:

=A2>B2

Format, pattern color GREEN


CF formula for same:

=A2=B2

??

 

[Rachael000]

Thanks Luke, that's really helped - I've now got the problem that the cells which don't have any entry in column B (some of the students didn't take certain subjects) are all showing as red, even though there are no entries.


Is there any way to tell the spreadsheet to only account for the cells which have an entry?

 

[kchaitanyag]

hi Rachael,


for no entry cases use this.


=ISBLANK(A2)=TRUE

and patter color NO COlOR


"and at manage rules. check the box ( STOP IF TRUE)"

 

[Luke M]

Or, modify the rules to have an AND argurement like:

=AND(COUNTA(A2:B2)=2,A2<B2)

to make sure both cells have been filled in.

 

[Rachael000]

Hi Luke,


Sorry, i'm not sure what you mean... do you mean put all three CF entries in for 'better', 'worse' and 'equal' as above and then enter a fourth CF criteria? I only have three conditions available in the CF box and have never tried vba or macros, (am a bit scared of them)so if there is a way around it that would be great!


Thanks

 

[Luke M]

Sorry, I should have been clearer. No, you only need 3 rules (assuming there's one for equal), I was just amending:


CF formula for Worse than expected:

=AND(COUNTA(A2:B2)=2,A2<B2)

Format, pattern color RED


CF formula for Better than expected:

=AND(COUNTA(A2:B2)=2,A2>B2)

Format, pattern color GREEN


CF formula for same:

=AND(COUNTA(A2:B2)=2,A2=B2)

??

 

[Rachael000]

I've just tried those formulae, (thanks very much btw, haven't used the AND function before)... but it's still showing the empty cells as green


I'm sorry to be a pain, but how would the =ISBLANK(A2)=TRUE formula work which you suggested earlier? would i combine that with the 'better', 'worse' and 'equal' CF conditions?


Really appreciate your help

 

[Luke M]

Rachael,

Is B2 really empty, or is it the output of a formula that evaluates to ""?


We could vary the check then, and replace all the COUNTA functions with something like this:

Bad

=AND(B2<>"",A2<B2)

Good

=AND(B2<>"",A2>B2)

Equal

=AND(B2<>"",A2=B2)

 

[Rachael000]

You're a genius Thank you ever so much!!

 

[Luke M]

Thanks for the feedback!