+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
Office Software Thread, Excel 2003 Conditional Formatting - Student Targets in Technical; Ive been asked to look at conditional formatting forthe student target grades excel sheets. The formatted cells will be compared ...
  1. #1


    Join Date
    Oct 2006
    Posts
    3,414
    Thank Post
    184
    Thanked 356 Times in 285 Posts
    Rep Power
    149

    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

  2. #2

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    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...

  3. #3

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,559
    Thank Post
    1,469
    Thanked 1,219 Times in 828 Posts
    Rep Power
    752
    Quote Originally Posted by j17sparky View Post
    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

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    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

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,559
    Thank Post
    1,469
    Thanked 1,219 Times in 828 Posts
    Rep Power
    752
    Quote Originally Posted by vikpaw View Post
    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!
    Last edited by LosOjos; 21st June 2010 at 11:30 AM.

  6. Thanks to LosOjos from:

    j17sparky (21st June 2010)

  7. #6

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    MAN, you're fast!!
    I'm still counting brackets!

  8. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,559
    Thank Post
    1,469
    Thanked 1,219 Times in 828 Posts
    Rep Power
    752
    Quote Originally Posted by vikpaw View Post
    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.

  9. #8


    Join Date
    Oct 2006
    Posts
    3,414
    Thank Post
    184
    Thanked 356 Times in 285 Posts
    Rep Power
    149
    Quote Originally Posted by LosOjos View Post
    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

  10. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    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.

  11. #10


    Join Date
    Oct 2006
    Posts
    3,414
    Thank Post
    184
    Thanked 356 Times in 285 Posts
    Rep Power
    149
    Quote Originally Posted by LosOjos View Post
    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!

  12. #11
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    4,993
    Thank Post
    120
    Thanked 286 Times in 263 Posts
    Rep Power
    107
    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.
    Last edited by ChrisH; 21st June 2010 at 12:30 PM.

  13. Thanks to ChrisH from:

    j17sparky (21st June 2010)

  14. #12

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,559
    Thank Post
    1,469
    Thanked 1,219 Times in 828 Posts
    Rep Power
    752
    Quote Originally Posted by j17sparky View Post
    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!

  15. #13

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    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.

  16. #14

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,779
    Thank Post
    714
    Thanked 1,436 Times in 1,189 Posts
    Rep Power
    359
    Quote Originally Posted by LosOjos View Post
    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?
    21-06-2010-14.38.19.jpg

  17. #15

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,559
    Thank Post
    1,469
    Thanked 1,219 Times in 828 Posts
    Rep Power
    752
    Quote Originally Posted by vikpaw View Post
    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:

    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

  18. Thanks to LosOjos from:

    j17sparky (21st June 2010)

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [MS Office - 2003] Conditional Formatting
    By garethedmondson in forum Office Software
    Replies: 1
    Last Post: 3rd February 2010, 01:40 AM
  2. [MS Office - 2003] Conditional Formatting
    By stevenwba in forum Office Software
    Replies: 3
    Last Post: 17th December 2009, 02:28 PM
  3. [MS Office - 2003] Excel - Conditional Formatting using formula
    By park_bench in forum Office Software
    Replies: 1
    Last Post: 10th June 2009, 02:25 PM
  4. Conditional formatting help needed
    By Jobos in forum How do you do....it?
    Replies: 3
    Last Post: 3rd June 2008, 01:58 PM
  5. Help with conditional formatting
    By randle in forum Windows
    Replies: 3
    Last Post: 25th October 2007, 01:20 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •