Forum Discussion
Inconsistency between formula and graphing #NA, "" and blank cells
Hi all, I'm not understanding the logic here - can someone please explain the underlying philosophy? If a formula references a #NA it returns #NA, but if a formula references a cell set to ""...
Excel does not treat a cell with a formula that returns "" as blank, and never has.
For example, if A1 contains the formula ="", the formula =ISBLANK(A1) in B1 will return FALSE, and the formula =COUNTA(A1) will return 1.
In the "Hidden and Empty Cell Settings" dialog for a chart, you can specify how the error value #N/A will be treated, and how really empty cells (not "") will be treated.
Hi HansVogelaar
I agree with your count example, but what about =average? (or many other functions, slope, intercept...). If I select a range of cells using the average function it will treat a blank cell the same as =""
I agree with your count example, but what about =average? (or many other functions, slope, intercept...). If I select a range of cells using the average function it will treat a blank cell the same as =""
That is correct - a function such as COUNT or AVERAGE ignores both empty cells and cells that contain a text value such as "". So neither blanks nor "" is included in the result.