Excel 2003 Conditional Formatting - Student Targets

[Guest Guest]

Ive been asked to look at conditional formatting forthe student target grades excel sheets. The formatted cells will be compared to his target grade cell, if hes doing better it will be green, on target orange, and worse red.

 

The grades are structured as such;

 

Highest

...

5a

5b

5c

4a

4b

4c

3a

3b

3c

...

Lowest

 

As you can see the number works as usual; 5 is greater than 4. But the letters work the other way round; a is greater than b.

 

Because of the letters bit Im a little stumped in how to do this. Any ideas?

 

Cheers

[vikpaw]

Use a lookup table to convert them all to numeric in a different column. Then do the diff on that.

Or, use a complex formula to do math on first digit then on second...

[LosOjos]

Ive been asked to look at conditional formatting forthe student target grades excel sheets. The formatted cells will be compared to his target grade cell, if hes doing better it will be green, on target orange, and worse red.

 

The grades are structured as such;

 

Highest

...

5a

5b

5c

4a

4b

4c

3a

3b

3c

...

Lowest

 

As you can see the number works as usual; 5 is greater than 4. But the letters work the other way round; a is greater than b.

 

Because of the letters bit Im a little stumped in how to do this. Any ideas?

 

Cheers

 

Easiest way I can think of would be to assign a value to each grade.

 

Set up a table that contains all the grades in one column, and assign a value to each in the other (a simple 1 - 100 or however many grades you have will do). Then on your target sheet, add a column next to your targets that looks up this value (vlookup) from the table you created and perform your conditional formatting calculations on that column. You can hide the column with the values on it so as not to confuse SLT

[vikpaw]

SIMS could do this as the grades would be part of a gradeset and each would have a value associated with it. Which MIS do you use?

 

For excel: part of the formulae could work like this:

 

=CODE(RIGHT(D3,1))

Edited June 21, 2010 by vikpaw
clarity

[LosOjos]

use a complex formula to do math on first digit then on second...

 

=IF(LEFT(A1,1)=LEFT(B1,1),IF(RIGHT(A1,1)=RIGHT(B1,1),"AMBER",IF(RIGHT(A1,1)>RIGHT(B1,1),"GREEN","RED")),IF(LEFT(A1,1)
 

That's the formula you need, based on Target being in column A and estimate in column B
 

EDIT: I had it backwards, oops!

Edited June 21, 2010 by LosOjos

[vikpaw]

MAN, you're fast!! :thumb:

I'm still counting brackets!

[LosOjos]

MAN, you're fast!! :thumb:

I'm still counting brackets!

 

LOL years of messing about with Excel formulae...

 

However, it's not much use here I realised, it needs cutting in to sections for the conditional formatting, just trying to remember how to make a cell reference itself and I'll post up exactly how to set up conditional formatting for you...

 

EDIT:

@j17sparky: In Excel 2003, do you have an option to use conditional formatting based on a forumla, or just simple options such as "Is greater than", "Is equal to" etc.?

Edited June 21, 2010 by LosOjos

[Guest Guest]

EDIT:

@j17sparky: In Excel 2003, do you have an option to use conditional formatting based on a forumla, or just simple options such as "Is greater than", "Is equal to" etc.?

 

Yes you can put in a formula

[vikpaw]

is equal to "amber" should be ok. worst case you may have to copy formula cell and paste as value, or change the colour words to just be numbers 1,2,3, and conditionally format based on that.

[Guest Guest]

=IF(LEFT(A1,1)=LEFT(B1,1),IF(RIGHT(A1,1)=RIGHT(B1,1),"AMBER",IF(RIGHT(A1,1)>RIGHT(B1,1),"GREEN","RED")),IF(LEFT(A1,1)
 

That's the formula you need, based on Target being in column A and estimate in column B
 

EDIT: I had it backwards, oops!
 

Is this bit supposed to work, as it doesnt?
 

Must admit on beingvery rusty in excel, havnt used it since i was in school i dont think!

[ChrisH]

I created some functions for this:

 

Function LevelToPoints(strLevel As String)
Dim objDictionary As New Scripting.Dictionary
Dim intAnswer As Integer

'Set up level to points Dictionary
objDictionary.Add "1", 13
objDictionary.Add "2", 15
objDictionary.Add "2c", 15
objDictionary.Add "2b", 17
objDictionary.Add "2a", 19
objDictionary.Add "3c", 21
objDictionary.Add "3b", 23
objDictionary.Add "3a", 25
objDictionary.Add "4c", 27
objDictionary.Add "4b", 29
objDictionary.Add "4a", 31
objDictionary.Add "5c", 33
objDictionary.Add "5b", 35
objDictionary.Add "5a", 37
objDictionary.Add "6c", 39
objDictionary.Add "6b", 41
objDictionary.Add "6a", 43
objDictionary.Add "7c", 45
objDictionary.Add "7b", 47
objDictionary.Add "7a", 49
objDictionary.Add "8", 51

If objDictionary.Exists(strLevel) Then
'MsgBox intAnswer
LevelToPoints = objDictionary.Item(strLevel)
End If
End Function

 

I then compare these values. You will just need to do the conditional formatting bits.

Edited June 21, 2010 by ChrisH

[LosOjos]

Is this bit supposed to work, as it doesnt?

 

Must admit on beingvery rusty in excel, havnt used it since i was in school i dont think!

 

If you copy that formula in to a cell and make sure it's pointing at the correct cells ("A1" should point to the target cell on the current row, "B1" the cell you're comparing on that row), then the value of the cell will become either "RED", "AMBER", or "GREEN", although it won't actually apply conditional formatting.

 

I'm trying to find how to make a cell refer to itself in the conditional formatting formulae, but every site that tells you how is filtered here bare with me though, I'll find it!

[vikpaw]

It worked for me. you have to make sure that you start your test spreadsheet with the data in A1,B1 and ideally copy paste the quoted above into C1. Then possibly hit F9 to update it.

[vikpaw]

I'm trying to find how to make a cell refer to itself in the conditional formatting formulae, but every site that tells you how is filtered here bare with me though, I'll find it!

What do you mean refer to itself?

[LosOjos]

What do you mean refer to itself?

[ATTACH=CONFIG]7433[/ATTACH]

 

I was trying to make it tidier, so that the each cell checks it's own value against the formula, but I think I'm making it more confusing in my head than it needs to be, the following should work:

 

Use each of these formulae as "Formula Is" formatting (the formulae presume that the first cell you're applying this to is B1 and the first target is in A1, you'll need to adjust these to suit your spreadsheet):

Green formatted cell:
=IF(left(A1,1)left(B1,1),0,if(right(A1,1)
Amber formatted cell:
=IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0)

Red Formatted cell:
=IF(left(A1,1)>left(B1,1),1,if(left(A1,1)right(B1,1),1,0)))

 

let me know how you get on with that

[vikpaw]

oh, i see what you meant now.

[Guest Guest]

I was trying to make it tidier, so that the each cell checks it's own value against the formula, but I think I'm making it more confusing in my head than it needs to be, the following should work:

 

Use each of these formulae as "Formula Is" formatting (the formulae presume that the first cell you're applying this to is B1 and the first target is in A1, you'll need to adjust these to suit your spreadsheet):

Green formatted cell:
=IF(left(A1,1)left(B1,1),0,if(right(A1,1)
Amber formatted cell:
=IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0)

Red Formatted cell:
=IF(left(A1,1)>left(B1,1),1,if(left(A1,1)right(B1,1),1,0)))

 

let me know how you get on with that

 

Thats looking good.

 

One problem though, the lettering is working the wrong way round; b is better than a.

 

 

Thanks for your help so far!

[LosOjos]

b is better than a.

 

That's the problem with these huge formulae, easy to lose track lol!

 

Hopefully this should do it then:

 

Green formatted cell:
=IF(left(A1,1)left(B1,1),0,if(right(A1,1)>right(B1,1),1,0)))

Amber formatted cell:
=IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0)

Red Formatted cell:
=IF(left(A1,1)>left(B1,1),1,if(left(A1,1)

[Guest Guest]

Brilliant, that works!

 

It would also seem that you can copy down conditional formatting! Thats how long i havnt used excel for when 2003 has a feature i didnt know about!

 

Thanks alot for your help, and everyone else. I do prefer this method rather than vLookups and vb as it should be abit more resiliant to teachers messing around with the worksheet.

Edited June 21, 2010 by Guest