# Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color.

The problem I'm having has to do with grades of Zero. I have this formula for the other grades, with 90% being the passing mark, so anything above that isn't marked

``````=and(\$E:\$E<90%, \$E:\$E>0%)
``````

The problem is that apparently the value of an empty cell is considered to be 0 by the spreadsheet. If I did a conditional format with the following:

``````=\$E:\$E=0
``````

You can use `isblank()` to format blank cells the way you want to. For example

``````=or(and(\$E:\$E<90%, \$E:\$E>0%), isblank(\$E:\$E))
``````

or

``````=or(and(\$E:\$E<90%, \$E:\$E>0%), not(isblank(\$E:\$E)))
``````

By the way, you can simply enter `\$E1` here instead of `\$E:\$E` - the conditional formatting formula can be written as it applies to the upper left corner of the range.

• Here's a link to what the table will look like: Example Table - as you can see, one of the problems is that the Grades column will contain four different classes of values. There will be some blank cells, some that contain o% that I want to highlight that row in red, from 1%-89% that will be highlighted yellow, and then 90% and above that won't have highlighting. – draconix Apr 1 '16 at 23:13
• The root cause here is poor data organization. The comments should have been placed in one cell (line breaks can be created with Ctrl-Enter) instead of spanning across several rows. – user79865 Apr 1 '16 at 23:17

Finally I got it to work. Here's the formula I used:

``````=and(\$E:\$E=0%;not(isBlank(\$E:\$E)))
``````

The organization of the cells is because there is another layer to the data that I forgot to include in the example. Each grade is composed of different sections that are graded separately. For example

English-90%
Creative Writing - Pass
Grammar - Fail