Subtotal & #n/a problem

[news]

Hello,

I have a spreadsheet that has named columns of data.

Column A (ReadDate) contains dates

Column B (LocationID) contains data (sample point ID's e.g. 0001,
0002, 0003 etc)

Column C (Methane) contains data

The columns have autofilter, so for example I could select sample
point 0003 in column B (LocationID).

On a separate worksheet I created a foruma using SUBTOTAL to
calculate MIN, MAX, MEAN etc. An example of the formula is:

=SUBTOTAL(5,Methane)

which returns the minimum value from the methane column, or the
minimum methane value for a sample point if a specific sample point
had been selected in column B.

The formula worksfine, except if there is a #N/A value in the methane
column. The formula then returns a #N/A error.

My question is - how do I get the SUBTOTAL formula to ignore the #N/A
in the Methane column and do what it is supposed to do? I could
remove the #N/A from the methane column, but I need it there from
charting purposes.

Hope someone can help....

thanks

Michael

 

[DMoney]

Use the ISERROR method the example below returns 0 if the
formula calcuates an error.

=IF(ISERROR(A4/B4),0,A4/B4)

HTH

 

[Guest]

The long and the short of the answer is that you can't. Subtotal will always
look at the #N/A. You have two options.

1 Remore all of the #N/A s by fixing the formulas

2 Instead of using subtotals use a pivot table. Based on your description
of the data a pivot table should work just fine. In the Table options you can
set error values to be 0 and then your subtotals will work. Additionally you
can graph straight off of the pivot table if that floats your boat...

HTH...

 

[news]

Ok thanks, I will try using a pivot table - something I have never
used before!