# Inconsistency between formula and graphing #NA, "" and blank cells

Copper Contributor

# 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 "" it ignores it.

Formula don't appear to differentiate between cells set to "" and an actual blank cell.

If a graph references a #NA it can be set to ignore it, but if it references a cell set to "" it treats it as a value of zero and graphs this point and includes it in a trendline.

BUT if a graph references a truely blank cell it does not graph it.

Why does graphing treat "" and an actual blank cell differently, whereas functions do not?

Why do functions treat #NA differently from graphing?

(I'm sure earlier versions of excel grapging did not treat "" differently from an actual empty cell - why did it change?)

thanks,

Matthew

5 Replies

# Re: Inconsistency between formula and graphing #NA, "" and blank cells

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.

# Re: Inconsistency between formula and graphing #NA, "" and blank cells

Hi @Hans Vogelaar
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 =""

# Re: Inconsistency between formula and graphing #NA, "" and blank cells

You're right, there can be inconsistencies in how Excel handles different cell values, specifically regarding #NA, empty cells, and cells containing "". Here's a breakdown of the observed behaviors and potential explanations:

Formulas vs. Graphing:

• #NA: Formulas typically propagate #NA when encountering it in references, as it signifies an error or unavailable data. This behavior ensures that downstream calculations don't produce misleading results due to errors.
• Empty cells and "": Formulas generally treat empty cells (no value entered) and cells containing "" (empty string) the same way, often ignoring them in calculations like SUM, AVERAGE, etc. This is because an empty cell doesn't hold any meaningful data, and "" explicitly represents an empty string value.
• Graphing: Graphing behavior can differ from formulas. While empty cells are usually excluded from graphs, cells containing "" are often treated as 0. This might be due to simplifying assumptions for visual representation:
• Empty cells likely indicate missing data, so excluding them from the graph makes sense.
• Cells containing "" might imply an intentional placeholder or zero value, justifying their inclusion as 0 in the graph.

Historical Changes:

• Earlier Excel versions might have treated "" differently in graphs, but specific details or reasons for changes are not readily available. It's possible that the current behavior was chosen for consistency with other charting software or to simplify data visualization.

Function-Specific Behavior:

• While many functions treat empty cells and "" similarly, some functions might have specific handling:
• COUNT functions (e.g., COUNTA, COUNTBLANK) explicitly distinguish between empty cells and "" for counting purposes.
• Statistical functions like AVERAGE typically ignore both empty cells and "" when calculating the average. However, some statistical functions might have different handling depending on their specific purpose.

While the observed inconsistencies might seem illogical, it's important to consider the context and purpose of formulas and graphs. Understanding these differences can help you interpret results correctly and potentially adjust your approach if needed.

For example, if you want to exclude both empty cells and cells containing "" from calculations or graphs, you can use functions like ISBLANK or IF statements to handle them explicitly.

Remember, Excel's behavior might evolve over time, so staying updated on any changes or clarifications from Microsoft is recommended

# Re: Inconsistency between formula and graphing #NA, "" and blank cells

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.

# Re: Inconsistency between formula and graphing #NA, "" and blank cells

So I think there are 2 problems here that are wildly inconsistent;
Graphing converts strings to a value - that is programatically incorrect and should be fixed - a When graphing expects a value and receives a string it should be treated as #NA (not a value).
But... the real problem here is that a function can make a decision when they are passed an empty cell and there does not appear to be a method to force a cell to be empty? That is about as useful as write only memory.
-matthew