Statistics, #N/A and dynamic range problem

  • Thread starter Thread starter OscarC
  • Start date Start date
O

OscarC

Hello

I have several columns of data all of which start in row 7. In rows
- 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. i
='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 th
dynamic range works correctly, I have inserted #N/A in to the blan
cells.

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

If I change the formula for the stats to an array formula as below
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 th
#N/A in the data column is a zero, so for MIN, 0 is reported.

The array formula =MIN(IF(My_Range<>0,My_Range,False)) would ignore
zero.

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

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

Any help would be greatly appreciated!

Thanks,


Michae
 
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 said:
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_Range<>0,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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
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
Click to expand...


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
 
Dear Sir

I have a problem with #N/A indication:

When I type a formula on sheet 2 based on the values copied from sheet 1
I cell on the sheet 2 shows #N/A.

What I want is :

The cell on sheet 2 with the formula but it should not show #N/A instead it
should show blank with the formula.
 
My question :-

When I type a formula on sheet 2 the values taken from sheet 1,
the cell on sheet 2 shows #N/A (with the formula).

What I want :-

Instead of showing #N/A I want the cell to be shown blank but with the formula.

How to do it ?
 
Back
Top