Worksheet functions plot and #NA's

[Morten Hvidberg-Knudsen]

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/com...dg=microsoft.public.excel.worksheet.functions

 

[Luke M]

I would think the rationale behind not creating something "new" is that it
already exists. Although you may still think it is "clumpsy" the formula
seems fairly straightforward:

{=AVERAGE(IF(ISNUMBER(A2:A7),A2:A7))}

I can say what I'm doing with a a single sentence. "If the cell is a number,
include in the average." Compared to some of the monstrous formulas I've seen
constructed over the years, this is nothing.

And while you say that most people are unfamiliar with arrays, I would argue
that simply not knowing something doesn't make it unuseful. Many people do
not know (or are familiar with) how to solve differential equations, but its
certainly useful in engineering.

 

[Morten Hvidberg-Knudsen]

You (intentionally ?) miss the point:

What is the reason (rationale behind) that the "missing value" treatment is
diffent in plots and in worksheet functions. ?

And exactly when does it "come handy" that non-numeric cells are plotted as
zero ?

 

[David Biddulph]

The easy option is to use "" as the output of one series from which to take
the average, and NA() as the output of another series to plot.
Fortunately Microsoft won't charge you extra for using an extra series.