A very common task is: to plot an range of cells and to do some calculation
(e.g. Average) on the same range of cells.

If all cells contain numeric values this works fine.

If some values are "missing", indicated by empty cells, it also works fine:
the missing values are not plotted and they are not taken into considerations
the calculated average.

But if the cellls are a result of a calculation the is no (easy) way to
accomplish this:

If the "missing values" are assigned the value #NA, then they are not
plotted, but the average becomes #NA.

If the "missing" values are assigned some non-numeric value (e.g. an empty
string, "") then the Average function becomes correct, but the values are
plotted as zero.

I know (from the frequent posts on the subject) the the problem can be
circumvented by a clumpsy IF array construct. But is a clumpsy workaround
(and who are familiar with Array Functions anyhow ? None of ny engineering
collegues are !)

My question is:

Is there some sort of option in the latest Excel that I have missed and
which solves the problem.

If not:

What is the rationale behind this behaviour ?

And if none:

Why have it not been corrected long ago ?


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions