Getting close but need some more heip

[Guest]

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

 

[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)

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

 

[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

 

[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))