Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal & #n/a problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal & #n/a problem
Use the ISERROR method the example below returns 0 if the formula calcuates an error. =IF(ISERROR(A4/B4),0,A4/B4) HTH -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal & #n/a problem
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... " wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal & #n/a problem
Ok thanks, I will try using a pivot table - something I have never
used before! On Wed, 15 Dec 2004 13:13:01 -0800, "Jim Thomlinson" wrote: 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... " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal Problem | Excel Worksheet Functions | |||
Subtotal command problem | Excel Worksheet Functions | |||
Subtotal problem. Very strange! | Excel Worksheet Functions | |||
subtotal problem | Excel Programming | |||
subtotal problem | Excel Programming |