#DIV/0! when calculating average percentages?

#1 Numbers Fan

New Member
Joined
Oct 4, 2006
Messages
3
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

PATSYS

Well-known Member
Joined
Mar 12, 2006
Messages
1,749
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=AVERAGE(IF(ISNUMBER(Range),Range))

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

pooj_malh

New Member
Joined
Sep 25, 2006
Messages
26
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

New Member
Joined
Oct 4, 2006
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

New Member
Joined
Oct 4, 2006
Messages
3
=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.
 

Forum statistics

Threads
1,141,142
Messages
5,704,529
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top