Google Sheets – Create a Grade Sheet with Conditional Formatting

conditional formattinggoogle sheets

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

the spreadsheet lights up.

Best Answer

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(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.

Code

function customSum(range) {
  var sum=0;  
  for(var i=0, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      sum += range[i][j];
    }
  }  
  return sum;
}

Example

I've created an example file for you: Custom Sum

Remark

Please note the difference between the two pieces of code. No API calls are used. The reference you use (cells) has more than one column !! If you want that, then you need to take that into account as well. Also note that changes in the range will be calculated immediately.

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

Best Answer

Related Solutions

Google Sheets – Data Ranges in Custom Functions

Code

Example

Remark

Google-sheets – How to check if a cell is blank for Conditional formatting

Related Topic