Getting close but need some more heip

G

grinlrar

Ok I have learned out how to create a formula that averages totals and
ignores divide by zero errors
=AVERAGE(IF(NOT(ISERROR(D103:F103)),D103:F103))

And how to create a formula that averages totals and ignores zero values
=AVERAGE(IF(D103:F103=0,"",D103:F103))

Now I need to combine the two into one formula but I can't get the syntax
right. I need it to average 3 cells containing totals but ignore cells that
contain zero and not create a divide by zero error if all three cells contain
zero since this the average value is linked to another workbook.

Anyone willing to tackle this one? Anyone know of a book or resource that
helps one learn to write multiple function formulas
Thanks
G.R. in Little Rock
 
B

Biff

Hi!
Ok I have learned out how to create a formula that averages totals and
ignores divide by zero errors
=AVERAGE(IF(NOT(ISERROR(D103:F103)),D103:F103))

Try this: (array entered)

=AVERAGE(IF(ISNUMBER(D103:F103),D103:F103))
And how to create a formula that averages totals and ignores zero values
=AVERAGE(IF(D103:F103=0,"",D103:F103))

Try this: (array entered)

=AVERAGE(IF(D103:F103 said:
Now I need to combine the two into one formula

Try this: (array entered)

=AVERAGE(IF((ISNUMBER(D103:F103))*(N(D103:F103)<>0),D103:F103))

Biff
 
B

Biff

Ooops!

Disregard this:
=AVERAGE(IF((ISNUMBER(D103:F103))*(N(D103:F103)<>0),D103:F103))

Try this: (array entered)

=AVERAGE(IF(ISNUMBER(D103:F103),IF(D103:F103<>0,D103:F103)))

Biff
 
A

Aladin Akyurek

One way:

If you have #DIV/0! errors in the target range...

=SUMIF(D103:F103,"<>#DIV/0!")/MAX(1,COUNT(D103:F103)-COUNTIF(D103:F103,0))

If there are no negative numbers, replace "<>#DIV/0!" with ">0".

If no #DIV/0! errors in the target range...

=SUM(D103:F103)/MAX(1,COUNT(D103:F103)-COUNTIF(D103:F103,0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top