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...
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
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...
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
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))
Last edited by vikpaw; 21st June 2010 at 11:28 AM. Reason: clarity
Last edited by LosOjos; 21st June 2010 at 11:30 AM.
j17sparky (21st June 2010)
MAN, you're fast!!
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.?
Last edited by LosOjos; 21st June 2010 at 11:44 AM.
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.
I created some functions for this:
I then compare these values. You will just need to do the conditional formatting bits.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
Last edited by ChrisH; 21st June 2010 at 12:30 PM.
j17sparky (21st June 2010)
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!
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.
What do you mean refer to itself?
21-06-2010-14.38.19.jpg
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:
let me know how you get on with thatCode: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)))
j17sparky (21st June 2010)
There are currently 1 users browsing this thread. (0 members and 1 guests)