#DIV/0! when calculating average percentages?

[#1 Numbers Fan]

I'm trying to calcualte average growth percentages from month to month for a 12 month period and my results are being messed up by the #DIV/0! error falling in line with certain months due to no-production. I tried to insert a couple of IF <>0 criteria statements in my formulas and they all failed to work. Can someone please shed some light on this for me? I feel i am close, maybe i'm just making a stupid mistke. Thank you for any help guys.

 

[PATSYS]

Assuming that your jan-dec growth figures are in A1:L1, type this formula:

=AVERAGE(IF(ISERROR(A1:L1)=FALSE,A1:L1))

confirm with CTRL+SHIFT+ENTER

 

[Aladin Akyurek]

=AVERAGE(IF(ISNUMBER(Range),Range))

which needs to be confirmed with control+shift+enter, not just with enter.

 

[pooj_malh]

Can you post an example what you are trying to do?
If I am calculating the average of all zero's i dont get any error
It might be possible that you are getting the error when calculating the % growth..

Can you throw some light on the calculation that you are doing?

Regards

 

[Aladin Akyurek]

Can you post an example what you are trying to do?
If I am calculating the average of all zero's i dont get any error
It might be possible that you are getting the error when calculating the % growth..

Can you throw some light on the calculation that you are doing?

Regards

If the range of interest houses one or more #DIV/0! values, an ordinary AVERAGE formula will also end up in #DIV/0!.

 

[#1 Numbers Fan]

Thank you for the quick responses everyone. Here is a quick example of what my worksheet looks like:

Ex:
Col B Jan: 78.9%
Col D Feb: 82.1%
Col F Mar: #DIV/0!
Col H Apr: 75.2%

Col Z Avg: #DIV/0!

Any ideas for the correct formula? The data i have in my sheet is similar except it is for the full 12 mos. Thanks again everyone!

 

[Aladin Akyurek]

Thank you for the quick responses everyone. Here is a quick example of what my worksheet looks like:

Ex:
Col B Jan: 78.9%
Col D Feb: 82.1%
Col F Mar: #DIV/0!
Col H Apr: 75.2%

Col Z Avg: #DIV/0!

Any ideas for the correct formula? The data i have in my sheet is similar except it is for the full 12 mos. Thanks again everyone!

=AVERAGE(IF(MOD(COLUMN(B2:H2)-COLUMN(B2),2)=0,IF(ISNUMBER(B2:H2),B2:H2)))

which you must confirm with control+shift+enter, not just with enter.

 

[#1 Numbers Fan]

=AVERAGE(IF(MOD(COLUMN(B2:H2)-COLUMN(B2),2)=0,IF(ISNUMBER(B2:H2),B2:H2)))

which you must confirm with control+shift+enter, not just with enter.

Thank you very much for your help! It works great and it makes sense when you break it down.