formula for comparing predicted grades vs actual grades

**maths** · 10-25-2012, 05:44 AM

Hi everyone,

I'm new to the forum, and although I thought I was quite good with Excel it has transpired that I am a total noob!

Is this possible? If so, could someone give me the correct formula. I have tried saying:
Condition 1 =H4<B4
Condition 2 - I was unsure how to do this as I just want one letter less
Condition 3 =OR(H4=B4,H4>B4)

However, this doesn't work. I am using Excel Mac 2011. The error message I receive is you cannot use relative references in conditional formatting criteria for color scales, data bars and icon sets.

Thank you, any help would be greatly appreciated.

**Pete_UK** · 10-25-2012, 05:59 AM

When setting up your CF with New Rule choose "Use a formula ....", and then you can specify your formula (and it can include relative references), and use the Format button with Fill to choose your colour.

Hope this helps.

Pete

**maths** · 10-25-2012, 06:27 AM

thank you, but that's exactly what I've been trying. However, I still receive the same error message "you cannot use relative references in conditional formatting for color scales, data bars and icon sets"

**Pete_UK** · 10-25-2012, 06:33 AM

Post an example workbook (change the names, if any).

Pete

**maths** · 10-25-2012, 06:43 AM

I've made a rough workbook of what I need. At the moment I have manually changed the colours of the actual grades column, but I want excel to do this automatically as I have over 800 students.

I want Excel to check the predicted grades and change the colour according to the actual grades.

Thank you

example.xlsx

**Pete_UK** · 10-25-2012, 07:31 AM

Okay, I'll take a look at it this evening, as I have some urgent work to do at the moment.

Pete

**maths** · 10-25-2012, 07:59 AM

aww thank you Pete, you're a star

**twiggywales** · 10-25-2012, 08:05 AM

have a look at this.

i used the code function (and upper just incase you put the grade in lower case)

i think is does what you need

example (1).xlsx

(it does use 3 formulas i dont know if you could do it with less but i cant lol)

**Pete_UK** · 10-25-2012, 08:17 AM

I managed to find a bit of time, and the attached shows how this can be done.

I've inserted columns so that your actual grades are in the same column as your real file - this will make it easy to apply across. I've also listed the grades in column M, so thay can be converted into numbers more easily. If this is a column that you already use in your file, then insert extra columns in this file so that it shifts over to a blank column in your real file, and then you can just use the Format Painter to copy the conditional formats across. I have used these formulae for the conditional formatting:

=MATCH($H2,$M:$M,0)-MATCH($B2,$M:$M,0)>1 -----> RED

=MATCH($H2,$M:$M,0)-MATCH($B2,$M:$M,0)=1 -----> YELLOW

=MATCH($H2,$M:$M,0)<MATCH($B2,$M:$M,0) --------> GREEN

and the cell remains white if the two grades are the same.

Hope this helps.

Pete

**maths** · 10-25-2012, 10:35 AM

Thanks guys, you are absolute legends!! :D Thank you so much, both those suggestions are absolutely fantastic and I'm sure this will be very handy for many years to come.

thanks again