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?
Bookmarks