0

If I have a blank cell, I want to have another cell interpret that cell as blank and display "Not Graded" or "Graded" if it holds a value.

I have tried 

=IF(ISBLANK(C21), "Not Graded", "Graded")

and 

=IF(C21=""), "Not Graded, "Graded"))

However the cell that I'm evaluating APPEARS to be blank has a formula for an average in it, so Excel is returning ISBLANK as false, despite nothing showing within the cell to the user.

Is there a test where I can check for the contents of the cell rather than have Excel use formulas within that cell?

I've come across this: Return empty cell from formula in Excel

but am hoping to avoid VBA at this point if possible

Excel

Community
  • 1
  • 1
Kevin K
  • 25
  • 6
  • Are you sure the cell with the average formula is returning a zero-length string? Could it be returning a space character? – Excel Hero Nov 18 '15 at 20:00
  • Sorry, changed the second formula to match what I've got going in Excel – Kevin K Nov 18 '15 at 20:03
  • My understanding is that the cell is returning the average of say C3:C8, but with the numbers not having being placed yet the cell is waiting on that data – Kevin K Nov 18 '15 at 20:06
  • 1
    Can't be. If no values the average will result in `#DIV/0!`. Can you post the formula for the average? – Excel Hero Nov 18 '15 at 20:08
  • This is with the cell highlighted (bottom right), it displays AVERAGE(E8:E17) with nothing showing in the cell – Kevin K Nov 18 '15 at 20:09
  • Is the cell formatted in any way? Font Color, Number Formatting, Conditional Formatting? – Excel Hero Nov 18 '15 at 20:10
  • Ahhh it appears to be formatted in some way where the text remains white, even if you change it to black. I changed the background and now am able to see the expected '#DIV/0!'. I should be able to now set a cell to say IF given cell = "#DIV/0!" , set cell to "Not Graded" etc, yes? – Kevin K Nov 18 '15 at 20:14

2 Answers2

1

I suspect the solution to your problem is that the cell that looks blank is not. It has text that is being forced to display as white.

You can write a formula that that detects the error condition for this cell that only looks blank, like so:

=IF(ISERR(C21),"Not Graded","Graded")
Excel Hero
  • 12,652
  • 3
  • 22
  • 34
  • if the cell is not blank and not error, this formula would produce the wrong result. You would need an Or clause like this: =IF(OR(ISERR(C21),C21=""),"Not Graded","Graded") . The ISBLANK() function should also have the same result without checking for an error. – Brino Nov 18 '15 at 20:26
0

There are three main options for checking if a cell is truly blank, two of which you have already shown:

1. Empty String Equality
      =IF(C21="","Not Graded", "Graded")
2. ISBLANK() Function
      =IF(ISBLANK(C21), "Not Graded", "Graded")
3. Check the length of the cell value
      =IF(LEN(C21)=0,"Not Graded", "Graded")

In all three cases, the value of the cell will be checked, not the formula. So if the value of the cell is anything other than blank, or error, all three formulas will produce a result of "GRADED". However, if the cell is in error, then formulas 1 and 3 will also produce an error, whereas formula 2 will show that the cell is not blank.

If your cell C21 contains an average function, it should either produce a number, or an error. In either case it will not be blank.

It appears that you want to check whether or not the cell is in Error, in which case you can simply use the ISERR() or ISERROR() functions depending on your needs per @ExcelHero 's answer.

Brino
  • 2,374
  • 1
  • 19
  • 34
  • I might be misreading but I think the 'ISBLANK()' function would give me some trouble here because the cell always has content in it, whether that be the graded value or the error message. So the cell would always return false even if the "average" cell hasn't been given any grades – Kevin K Nov 18 '15 at 20:26
  • Correct, it will only tell if a cell is blank per your original description. If you want to check if a cell is in error, just use the iserr() function. If you want to check both, then you will need to check both – Brino Nov 18 '15 at 20:29