How do I get a subtotal when the data I am Subtotaling contains #n/a without
returning an answer of #n/a.


I am using =SUBTOTAL(9,M6:M199)/SUBTOTAL(9,P6:P199)/1440

This formula takes total time in seconds divided by total calls divided by
1440 and the cell is formated to time. The answer should return a time in
minutes and seconds; however, it returns #n/a. How would I get the formula to
ignore the #n/a in the data set that it is calculating?

John wrote:
How do I get a subtotal when the data I am Subtotaling contains #n/a without
returning an answer of #n/a.


I am using =SUBTOTAL(9,M6:M199)/SUBTOTAL(9,P6:P199)/1440

This formula takes total time in seconds divided by total calls divided by
1440 and the cell is formated to time. The answer should return a time in
minutes and seconds; however, it returns #n/a. How would I get the formula to
ignore the #n/a in the data set that it is calculating?

Can you get rid of the #N/A result that is causing the problem? What is the
formula in that cell or cells?

The cell that contains#N/A is the following formula:

=IF(ISBLANK(B168:B168)," ",VLOOKUP(B168,STATS!D:N,11,FALSE))

I would like to make the above formula return an empty value. The above
formula is searching in a database for a name. That name does not exist in
the database therefor it returns the #N/A. I would prefer that it just return
nothing.

"Glenn" wrote:

John wrote:
How do I get a subtotal when the data I am Subtotaling contains #n/a without
returning an answer of #n/a.


I am using =SUBTOTAL(9,M6:M199)/SUBTOTAL(9,P6:P199)/1440

This formula takes total time in seconds divided by total calls divided by
1440 and the cell is formated to time. The answer should return a time in
minutes and seconds; however, it returns #n/a. How would I get the formula to
ignore the #n/a in the data set that it is calculating?

Can you get rid of the #N/A result that is causing the problem? What is the
formula in that cell or cells?

Change the VLOOKUP formula.

=IF(ISNA(VLOOKUP(B168,STATS!D:N,11,FALSE)),"",VLOO KUP(B168,STATS!D:N,11,FALSE))

Note: "" which is blank " " returns a space.


Gord Dibben MS Excel MVP

On Mon, 2 Feb 2009 14:12:13 -0800, John
wrote:

The cell that contains#N/A is the following formula:

=IF(ISBLANK(B168:B168)," ",VLOOKUP(B168,STATS!D:N,11,FALSE))

I would like to make the above formula return an empty value. The above
formula is searching in a database for a name. That name does not exist in
the database therefor it returns the #N/A. I would prefer that it just return
nothing.

"Glenn" wrote:

John wrote:
How do I get a subtotal when the data I am Subtotaling contains #n/a without
returning an answer of #n/a.


I am using =SUBTOTAL(9,M6:M199)/SUBTOTAL(9,P6:P199)/1440

This formula takes total time in seconds divided by total calls divided by
1440 and the cell is formated to time. The answer should return a time in
minutes and seconds; however, it returns #n/a. How would I get the formula to
ignore the #n/a in the data set that it is calculating?

Can you get rid of the #N/A result that is causing the problem? What is the
formula in that cell or cells?

Hi,

You could try something like this as well:

=(sumif(M6:M199,"=-9.99999E307")/sumif(P6:P199,"=-9.99999E307"))/1440

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"John" wrote in message
...
How do I get a subtotal when the data I am Subtotaling contains #n/a
without
returning an answer of #n/a.


I am using =SUBTOTAL(9,M6:M199)/SUBTOTAL(9,P6:P199)/1440

This formula takes total time in seconds divided by total calls divided by
1440 and the cell is formated to time. The answer should return a time in
minutes and seconds; however, it returns #n/a. How would I get the formula
to
ignore the #n/a in the data set that it is calculating?