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

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<0,D103:F103))

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

"grinlrar" wrote in message
...
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

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,D1 03:F103)))

Biff

"Biff" wrote in message
...
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<0,D103:F103))

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

"grinlrar" wrote in message
...
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

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

grinlrar wrote:
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