Thread: conditional formatting and letter/number grades

okay, so I have ZERO experience using excel...which still puts me at the "top of my class" among teachers at my school.

I'm now using it to make a school data wall - an excel file that has student name, DOB followed by various school assessments.

QUESTION ONE
I want to use the conditional formatting to highlight certain data - for example, I want anything in the D- to C+ range to be red, B- to B to be yellow and B+ to A+ to be green.

I can easily see how I would do this if the data was in percentages. you would just say that anything less than 70 is red, etc. however, excel doesn't know that a "B-" is less than a "B", etc.

the simple solution would be to convert the grades, which I would do if this was just for me. however, other teachers will be using this, and I want to keep it as simple as possible. I want them to type in D+ and have the cell go immediately red. I can do this, I just can't do the range, which is what I need. some users will be using open office or 2003 excel, which I believe only let you have 3 conditions (I may be wrong on this).

QUESTION TWO
we use the letter scale as well as a number scale going 0, 1, 1+, 2-, 2, 2+ etc all the way to 4+. yes, I realize this is exactly the same as using the letters, and just as inefficient. I did not choose it, vote for it, or endorse it. you can thank our government for the confusion!

I can't see how to let excel take in this data since it thinks that "1+" is the start of an equation. I have since learned that typing it in as...

'3+

will allow it to appear normal, but as I mentioned earlier, other teachers will be entering data, and I would like them to be able to simply type 3+ and be done with it.

once I am able to input these numbers, I will have the same problem as question one with needing to have excel understand the order.

thanks in advance to all you excel wizards out there! I look forward to reading your responses.

For #1: Try =OR(A1={"A+","A","A-","B+"}) for conditional formatting.
For #2: Format all cells in that column to be text before entering data in them. Then use a conditional formatting formula like the above.

Originally Posted by darkyam

For #1: Try =OR(A1={"A+","A","A-","B+"}) for conditional formatting.
For #2: Format all cells in that column to be text before entering data in them. Then use a conditional formatting formula like the above.

thanks darkyam, I am using the conditional formatting wizard.

I have tried entering multiple values in, but it doesn't work. I make a new rule, and put in "cell value", "equal to" and then try putting in something like...

"A+","A","B+" and then set my options, but it doesn't work, or works in strange ways (only formatting the A for example.

it works perfectly when I just do a single value.

Go to New Rule... then Use a formula to determine which cells to format.

=or(a1="a+",a1="a",a1="a-",a1="b+")

thanks a lot for all the tips and files guys.

I'm learning a lot. I think I am going to go with something a friend of mine taught me, which is to write visual basic code.

the If/Then stuff makes it really easy.

thanks again everyone! incredibly helpful!