+ Reply to Thread
Results 1 to 3 of 3

Need help creating a color grading scale

  1. #1
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Post Need help creating a color grading scale

    Hi all,
    I created a grading scale based on color of the cell, well technically the letter in the cell which represents a color basically, and has conditional format to highlight the cell based on what letter is in it. For example, R = red, Y = yellow, G = green. Cities are listed going down column A and B-G have 6 different areas we are grading going across. (I.E, quality, security... etc..etc..)

    For the 6 areas i am entering either R,Y,G into each of the 6 cells to represent the grade given to that particular area. I then have at the very end in hidden columns a formula to count the number of Reds , Yellow and Green that should be allowed in those 6 cells (for each row) in order to trigger the last column labeled Overall which gives the final grade. For instance, lets say from B2-G2 i have 2 reds, 1 yellow, and 3 green, ... then i want the last column to automatically highlight a certain color , lets say... Yellow to indicate the 'Overall' grade. This is easy if i say =If(countif(B2:F2,"R")>"3","True","False") then set the conditional format to point at that cell and if it is "True" to highlight it Red. Then set a threshold for Yellow and set conditional rule below the Red one,, and then the same for Green (or leave it to where it says if the cells for REd and Yellow are "False" then automatically make it Green).

    I can do that but there is just many different combinations of number of R,Y, and G cells that can happen, which makes this a little difficult.

    Does anyone know how i can make grading all these different combinations easier?

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Need help creating a color grading scale

    Hello,

    you could create a helper table where you have one row for each possible colour combination, starting with

    RRRRRR
    RRRRRY
    RRRRYR
    RRRRYY
    RRRYRR

    and so on. In the next column, put the Value that you want to show for this colour combination.

    In your original table you can then concatenate the values of the six columns into one text string and use that as a lookup value for a Vlookup, along the lines of

    =vlookup(A1&B1&C1&D1&E1&F1,helperSheet!$A$1:$B$50,2,False)

    Adjust the $B$50 to the last row of your helper table. Now your conditional format for the total can be a simple evaluation of the returned value.

    cheers, teylyn

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Need help creating a color grading scale

    Another possibility.
    Thinking about this what color would you put in the final grade if a city got three R's and three G's?
    I thought about assigning a numerical value to each color such as R = 1, Y = 2 and G = 3. In a helper cell in column I you could get the average (mean) of the six areas that are being graded using a formula like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In the total cell, column H, display R if the value in column I is less than 1.5, Y < 2.5 which by elimination leaves G using a formula like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You can then extend the conditional formatting rules that are already applied to columns B:G into column H,
    Just a thought. Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Color Scale Not Working
    By k64 in forum Excel General
    Replies: 4
    Last Post: 07-27-2016, 11:24 AM
  2. [SOLVED] Conditional Formatting like Color Scale but doing it via Interior.Color
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2015, 06:36 AM
  3. Color Scale Formatting with Formula
    By nealj85 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2014, 03:38 PM
  4. Creating coding for grading for class project
    By Burkex12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2013, 04:53 AM
  5. Replies: 16
    Last Post: 11-21-2012, 07:20 PM
  6. Creating a grading feedback form using Excel
    By Patrick Immel in forum Excel General
    Replies: 8
    Last Post: 10-27-2012, 10:32 AM
  7. Color Scale Bar Chart
    By nsorden in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-25-2009, 04:59 AM

Tags for this Thread

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