Help with some conditional formatting

[alicia1975]

Hey guys,

I'm new here and have really no idea what I'm doing but here goes nothing.

So I've been asked by a friend to help out with her excel spreadsheet, and i just can't figure out how or what I should do to create the function I desire

Basically, she works in a school, and there are essentially two columns, the ATG (target grade) and then the actual grade that the students are on. These grades are in the form of an integer between one and eight. I'm just wondering if theres a way to reference the actual grade to the ATG, and then output these functions:
1. If the actual grade is greater than or equal to the ATG, the cell is green
2. If the actual grade is lower than the ATG by 2 or equal to the ATG, the cell is yellow
3. If the actual grade is lower than the ATG by 3 or more, then the cell is red.

Any ideas of what to do, or a simple way of outputting this?

Thank you!!

 

[Gerald Higgins]

Hi, welcome to the board.

This is do-able with Conditional Formating.

Let's say the actual grade is in A1, and the ATG is in A2

In newer versions of Excel . . .
From the CF menu,
New Rule
Use a formula to determine which cells to format
Format values where this formula is true
=A1>A2
Format
and choose a format, such as a green fill.

Add similar rules for the other colours / conditions.

NOTE - you might want to clarify how you deal with the situation where actual grade is equal to ATG, as you seem to want it to be both green and yellow.

 

[alicia1975]

Thanks so much for guidance!

Yeah I made a typo, the yellow fill should just be if it is lower than the ATG by two
I understand the cond. formatting idea, but how do i apply the idea if it's lower than the ATG by two or three? that's the part where im stumped?
Also, all the ATGs will vary depending on the child
gah im so confused!!

 

[Gerald Higgins]

To do "lower than the ATG by two" you can do something like this

=A1<(A2-2)

Re-reading your OP, I think there is still scope for confusion.

Let's assume ATG = 100

If actual grade = 101, cell colour is green according to rule 1, OK.
If actual grade = 100, cell colour is green according to rule 1, OK
If actual grade = 99, cell colour is ???
If actual grade = 98, cell colour is yellow according to rule 2, (and post #3), OK
If actual grade = 97, cell colour is red according to rule 3, OK
If actual grade = 96, cell colour is red according to rule 3, OK

Is the above right ?

If the ATGs vary by child, that should not be a problem, depending on how your data is laid out.
Let's park that bit for now and deal with the basic CF for a single record.

 

[wideboydixon]

Here's what I have:

Book1
	A	B	C
1	Pupil	Target Grade	Actual Grade
2	Alice	5	5
3	Basil	6	3
4	Charlie	4	3
5	Daisy	7	8
6	Erica	3	3
7	Fred	4	4
Sheet4

I highlighted C2:C7 and added three conditional formatting formulas:

=$C2>=$B2
=$B2-$C2>=3
=AND($B2-$C2<3,$C2<$B2)

The first one is formatted green, the second one red and the last one yellow.

Hope that helps point you in the right direction.

WBD