Conditional Formatting If Cell Value is One Value Less than or Two Values Less than another Cell?

[dgsmith80]

Hello, I hope that someone might be able to help me.
I am trying to apply some conditional formatting to a school grade sheet. I have a row for each student with a Target Grade in Column A, and then the actual grade achieved in the next Column B.
I've setup the Rules for Greater Than, Less Than and Equal To so that the colour of the achieved grade changes colour to Red, Green or Blue depending on if they got a Better, Worse or Equal grade to the prediction, however... if possible I would like to have it be Yellow if the grade is only 1 lower than predicted and Red if it's 2 or less. So if target was A and actual was C then that would be Red, but if actual was B it would be Yellow.
Hope all that makes sense, but if not I can try and share an example workbook.
Appreciate any guidance that might be available.

 

[jasonb75]

There are likely to be a few ways to do this, the one that comes to mind without too much thought is to use variations of this formula, where A2 contains the target grade, B2 contains the actual grade and F2:F10 is a list of all possible grades with the highest at the top.

=(MATCH(A2,$F$2;$F$10,0)-MATCH(B2,$F$2;$F$10,0))<=-2

The example formula with <=-2 at the end will identify an improvement of 2 our more grades, =-1 an improvement of 1, etc.

 

[dgsmith80]

There are likely to be a few ways to do this, the one that comes to mind without too much thought is to use variations of this formula, where A2 contains the target grade, B2 contains the actual grade and F2:F10 is a list of all possible grades with the highest at the top.

=(MATCH(A2,$F$2;$F$10,0)-MATCH(B2,$F$2;$F$10,0))<=-2

The example formula with <=-2 at the end will identify an improvement of 2 our more grades, =-1 an improvement of 1, etc.

Thank you for the response @jasonb75 I have tried it and got some progress.

As you can see in my example it has worked for the 1 less option, but the 2nd and 4th row which scored D when the Target was A and B show no colour at all but they scored 2 and 3 less than the target so I'm not sure what went wrong.

 

[jasonb75]

I notice that you have D2 and G2 in your formulas, where it should have been D3 and G3. That said, I had also had the logic backwards which wouldn't have helped.

I've shown a trimmed down version of your sheet below with the rules set up and working. Note that the simple formula that you had for an improvement in grade would not work for A improving to A*, the full formula is needed there to ensure consistency.

Book1
	A	B	C	D	E	F	G
2				Target			Grade
3				D			D
4				A			D
5				A			A
6				B			D
7				C			D
8				C			D
9				A*			A*
10
11
12	Poss Grade
13	A*
14	A
15	B
16	C
17	D
18	E
19	U
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G9	Expression	=D3=G3	text	NO
G3:G9	Expression	=(MATCH(D3,$A$13:$A$19,0)-MATCH(G3,$A$13:$A$19,0))>0	text	NO
G3:G9	Expression	=(MATCH(D3,$A$13:$A$19,0)-MATCH(G3,$A$13:$A$19,0))=-1	text	NO
G3:G9	Expression	=(MATCH(D3,$A$13:$A$19,0)-MATCH(G3,$A$13:$A$19,0))<=-2	text	NO

 

[dgsmith80]

Amazing - Thank you very much that is perfection!!!

If I can be cheeky and ask for one more bit of assistance. I have a sheet which shows 3 different modules which make up the overall grade, is there a way to use the same conditional rules to change the colour on those cells or will I have to duplicate the rules?

Example:

So ideally I want J3:J15, M3:M15 & P3:P15 to all change colours based on the Target Grade. So far initial testing only changes the colour of Column P.

 

[jasonb75]

If they are on a different sheet then you will need to duplicate the rules, I don't think you can set one rule to multiple sheets although I've never had cause to try it.

Assuming the same sheet, if you set the 'Applies to' range for each rule so that it covers all of the ranges as shown below then it should work as you need it to.

When you change the range, make sure that you have G3 selected on the sheet first (always have the top left cell of the range to be formated selected when working with conditional formatting and write any formulas based on that cell).

$G$3:$G$15,$J$3:$J$15,$M$3:$M$15,$P$3:$P$15

Unfortunately I'm posting on a mobile device at the moment so can't post an example as I did earlier.

 

[dgsmith80]

Hi @jasonb75 thank you for the suggestion - I did try that myself but it didn't work. All that happens is that the G3:G15 cells change colours but none of the rest. I assume it's something to do with the formula in the rules:

=(MATCH(D3,LOOKUP!$G$2:$G$8,0)-MATCH(R3,LOOKUP!$G$2:$G$8,0))>0

*I moved the Lookup table to a new tab - The cells I'm applying this to are: =$T$3:$T$15,$V$3:$V$15,$R$3:$R$15 (I tried keying it in manually as R, T, V but it changes back to this all the time?)

 

[jasonb75]

Sorry, that was my fault. I missed a critical $ symbol

=(MATCH($D3,LOOKUP!$G$2:$G$8,0)-MATCH(R3,LOOKUP!$G$2:$G$8,0))>0

If you set it up with R3 selected you shouldn't have any more problems (I hope).

 

[dgsmith80]

Sorry, that was my fault. I missed a critical $ symbol

=(MATCH($D3,LOOKUP!$G$2:$G$8,0)-MATCH(R3,LOOKUP!$G$2:$G$8,0))>0

If you set it up with R3 selected you shouldn't have any more problems (I hope).

Thank you very much for all the help