How to ignore #NA in subtotals?

I have several data tables of identical layout that are updated daily, the
first two manually and the others by preset formula that calculates from the
first two. If a zero or no data are input into cells in the first two then
the formula of the others will return #NA, this is intentional because charts
are plotted from the latter tables and #NA prevents zero's being plotted all
over it for data that has not been entered yet (zero is a valid in negative
and positive data entry only when entered).

Each table has a SUM total, but, if #NA exits in any one of the cells
totaled the
SUM is returned #NA. How can I overcome this?

One way is to use
=if(isna(your_function),"N/A",your_function)
intead of allowing the #N/A

On Wed, 2 Mar 2005 09:53:02 -0800, Lee IT
<[email protected]> wrote:

>I have several data tables of identical layout that are updated daily, the
>first two manually and the others by preset formula that calculates from the
>first two. If a zero or no data are input into cells in the first two then
>the formula of the others will return #NA, this is intentional because charts
>are plotted from the latter tables and #NA prevents zero's being plotted all
>over it for data that has not been entered yet (zero is a valid in negative
>and positive data entry only when entered).
>
>Each table has a SUM total, but, if #NA exits in any one of the cells
>totaled the
>SUM is returned #NA. How can I overcome this?

I usually use two columns when I do this--one for the real values and one to be
used for charting (with the #n/a's).

But you could do something like:

=sum(if(isnumber(a1:a30),a1:a30))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Lee IT wrote:
>
> I have several data tables of identical layout that are updated daily, the
> first two manually and the others by preset formula that calculates from the
> first two. If a zero or no data are input into cells in the first two then
> the formula of the others will return #NA, this is intentional because charts
> are plotted from the latter tables and #NA prevents zero's being plotted all
> over it for data that has not been entered yet (zero is a valid in negative
> and positive data entry only when entered).
>
> Each table has a SUM total, but, if #NA exits in any one of the cells
> totaled the
> SUM is returned #NA. How can I overcome this?

--

Dave Peterson