# Excel 2003 Conditional Formatting - Student Targets

Show 25 post(s) from this thread on one page
Page 1 of 2 12 Last
• 21st June 2010, 11:17 AM
j17sparky
Excel 2003 Conditional Formatting - Student Targets
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
• 21st June 2010, 11:22 AM
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...
• 21st June 2010, 11:23 AM
LosOjos
Quote:

Originally Posted by j17sparky
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 ;)
• 21st June 2010, 11:26 AM
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:

`=CODE(RIGHT(D3,1))`
• 21st June 2010, 11:28 AM
LosOjos
Quote:

Originally Posted by vikpaw
use a complex formula to do math on first digit then on second...

Code:

`=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)<LEFT(B1,1),"GREEN","RED"))`
That's the formula you need, based on Target being in column A and estimate in column B

EDIT: I had it backwards, oops!
• 21st June 2010, 11:36 AM
vikpaw
MAN, you're fast!! :p:thumb::first::high5:
I'm still counting brackets!
• 21st June 2010, 11:41 AM
LosOjos
Quote:

Originally Posted by vikpaw
MAN, you're fast!! :p:thumb::first::high5:
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.?
• 21st June 2010, 11:51 AM
j17sparky
Quote:

Originally Posted by LosOjos
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
• 21st June 2010, 11:55 AM
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.
• 21st June 2010, 12:10 PM
j17sparky
Quote:

Originally Posted by LosOjos
Code:

`=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)<LEFT(B1,1),"GREEN","RED"))`
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!
• 21st June 2010, 12:28 PM
ChrisH
I created some functions for this:

Code:

```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.
• 21st June 2010, 12:29 PM
LosOjos
Quote:

Originally Posted by j17sparky
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 :mad: bare with me though, I'll find it!
• 21st June 2010, 12:31 PM
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.
• 21st June 2010, 12:39 PM
vikpaw
Quote:

Originally Posted by LosOjos
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 :mad: bare with me though, I'll find it!

What do you mean refer to itself?
Attachment 7433
• 21st June 2010, 12:48 PM
LosOjos
Quote:

Originally Posted by vikpaw
What do you mean refer to itself?
Attachment 7433

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:

Code:

```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),1,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)<left(B1,1),0,if(right(A1,1)>right(B1,1),1,0)))```
let me know how you get on with that
Show 25 post(s) from this thread on one page
Page 1 of 2 12 Last