Statistics, #N/A and dynamic range problem

This should take care of it

=MIN(IF(ISNUMBER(MyRange),MyRange))

=MAX(IF(ISNUMBER(MyRange),MyRange))

I assume that you want to include the N/A error as zero in the average?
If not

=AVERAGE(IF(ISNUMBER(MyRange),MyRange))

if you want to include the N/A in the average as zero

=SUMIF(MyRange,"#N/A")/COUNTA(MyRange)

the 3 first formulas entered with ctrl + shift & enter

note that both the average formulas will return a div error if MyRange is
empty
--

Regards,

Peo Sjoblom

"OscarC" wrote in message
...

Hello

I have several columns of data all of which start in row 7. In rows 1
- 6 I have summary statistics for each column of data, e.g. min, max,
mean, std dev etc.

Each column of data has a dynamic named range for use with a graph. ie
='Sheet1'!$C$7:OFFSET('Sheet'!$C$7,COUNTA('Sheet'! $C$7:$C$65536)-1,0)

Occassionally the data includes a blank cell, so to ensure that the
dynamic range works correctly, I have inserted #N/A in to the blank
cells.

The problem is this upsets the statistic summary in rows 1-7, which
return #N/A.

If I change the formula for the stats to an array formula as below I
can avoid the #N/A error:

=Max(IF(NOT(ISERROR(My_Range)),My_Range))

Which is fine for MAX, but not for MIN and AVERAGE as it assumes the
#N/A in the data column is a zero, so for MIN, 0 is reported.

The array formula =MIN(IF(My_Range0,My_Range,False)) would ignore a
zero.

Is there any way to combine these two array formulae, so they #N/A and
zero problem would be ignored in my stats summary?

Or is there a better way to achieve my goal - produce a stats summary
for my columns of data in the first 6 rows of each column, and use a
dynamic ranges to prouduce graphs, taking into account I have blank
cells?

Any help would be greatly appreciated!

Thanks,


Michael


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

On Sun, 7 Dec 2003 14:20:43 -0500, "Peo Sjoblom"
wrote:

This should take care of it

=MIN(IF(ISNUMBER(MyRange),MyRange))

=MAX(IF(ISNUMBER(MyRange),MyRange))

I assume that you want to include the N/A error as zero in the average?
If not

=AVERAGE(IF(ISNUMBER(MyRange),MyRange))

if you want to include the N/A in the average as zero

=SUMIF(MyRange,"#N/A")/COUNTA(MyRange)

the 3 first formulas entered with ctrl + shift & enter

note that both the average formulas will return a div error if MyRange is
empty


Thanks, that is the answer I am looking for! I decided against using
the N/A in the average as a zero.

Thanks again,

Michael