+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting to Compare Target Grades with Actual Grades

  1. #1
    Registered User
    Join Date
    11-29-2018
    Location
    England
    MS-Off Ver
    Office 2016
    Posts
    2

    Conditional Formatting to Compare Target Grades with Actual Grades

    Hello, I've put a lot of work into a grade tracker I'm really proud of but there is one thing it is missing before it's perfect in my view and I was hoping you wonderful people could help. I've tried several searches but I'm struggling to interpret what is being said. I'm not exactly new to excel, it's a skill I'm developing but I'm hoping that if I could learn how to do this, I could then apply it to multiple scenarios as the grade range remains the same for all of our students and all of our year groups.

    I've added an example document, which is a mini version of what we're working with (as the actual version is up to column DV with each unit of testing). In the example data case I'd want to compare the grade in column I with the target in column B. Students above target would have their cells coloured green, on target would be amber and below target would be red. I know that some sort of conditional formatting would be neccessary, just not sure what to do here?

    The grade range is U, 1, 2, 3, 4, 5, 6, 7, 8, 9. (If the U causes complicates things we could replace it with 0).

    Students targets are from 1-9.

    PS: In the sample data, grades start with U, then jump to 2 as this is the lowest graded score on this particular paper - but this isn't always the case. Hope this makes sense.

    Any help would be much appreciated.

    EDIT: It won't let me add an attachment for the sample data

    EDIT 2: I've added a clipping of the screen clicking on the grade cell to show how this has been formed.

    Capture.PNG
    Last edited by aramsbottom; 11-29-2018 at 07:57 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Conditional Formatting to Compare Target Grades with Actual Grades

    As an example and you can maybe do the rest...

    Conditional Formatting
    • Highlight applicable range >> A2:I19
    • Home Tab >> Styles >> Conditional Formatting >> New Rule
    • Select a Rule Type: Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =$I2>$B2
    • Format… [Number, Font, Border, Fill]
    • OK >> OK
    HTH
    Regards, Jeff

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional Formatting to Compare Target Grades with Actual Grades

    It looks like your target grades in column B are real numbers (aligned to the right), whereas the formula in I2 will return text values, because you have put quotes around the numeric values. You need the two columns to have the same type of data, in order to do a direct comparison.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    11-29-2018
    Location
    England
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Conditional Formatting to Compare Target Grades with Actual Grades

    Thank you both for this! It has really helped improve the clarity of the results on the bigger version which goes on forever with different sets of tests and assessments. Much appreciated

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cannot find a formula that converts fine grades, to whole grades......
    By GW1066 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-03-2018, 05:20 PM
  2. Replies: 10
    Last Post: 11-14-2016, 02:11 PM
  3. Replies: 1
    Last Post: 05-16-2016, 03:06 PM
  4. Procedure to find average of grades when alphabet grades are given
    By vsbhogar in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2012, 03:37 AM
  5. [SOLVED] formula for comparing predicted grades vs actual grades
    By maths in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-25-2012, 11:23 AM
  6. Calculating Target/Actual Grades
    By djs25uk in forum Excel General
    Replies: 5
    Last Post: 09-20-2011, 05:56 AM
  7. Conditional Formatting With Grades
    By november678x in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2006, 09:15 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1