Excel: Use an if statement to return blank value for formulae and charts

Question

Is there a way to have an if function return a value that will be ignored by both average functions and charts?

In Gnumeric, an open-source, Excel-like program, you can have an if function return "" and the cell will appear empty. If you take the average of a bunch of such cells, some with returned values and some with returned "", the "" will be completely ignored. And if you create a chart with those cells as data points, cells with "" will not have a point plotted.

However, doing the same thing in Excel doesn't seem to work. I've selected the "Show empty cells as: Gaps" option (described here) but it doesn't work. I think this is because the cell isn't technically empty.

Answers to similar questions suggest using "na()" in the if statement, but this messes with the averaging functions.

Does anyone know of a solution?

---

_{Note: While this subject area has been addressed before, I don't think this is a duplicate. Here are some similar questions:}

_{IF statement: how to leave cell blank if condition is false ("" does not work)}

_{Leave a cell blank if condition is false}

_{Creating a chart in Excel that ignores #N/A or blank cells}

Answer 1

Perhaps you could keep the #N/As (for the chart) but instead of using AVERAGE on it's own you could use the array formula (assuming the values you want to average are in A1:A5):

=AVERAGE(IFNA(A1:A5,""))

Once you've entered the formula press Ctrl+Shift+Enter to evaluate it as an array formula.

Answer 2

I think you would have to use a slightly more complicated function to calculate the average.

There may be a better way, but this will work (in conjunction with the na() method you mentioned in your question.

=SUM(IF(ISERROR(AVG_RANGE),0,AVG_RANGE)) / SUM(IF(ISERROR(AVG_RANGE),0,1))

This is an array formula so you'll need to commit it with ctl-shift-enter