# #DIV/0! when calculating average percentages?

#### #1 Numbers Fan

##### New Member
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### PATSYS

##### Well-known Member
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

##### MrExcel MVP
=AVERAGE(IF(ISNUMBER(Range),Range))

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

#### pooj_malh

##### New Member
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

##### MrExcel MVP

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

##### MrExcel MVP
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
=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.

Replies
2
Views
63
Replies
2
Views
40
Replies
7
Views
535
Replies
6
Views
472
Replies
5
Views
296

1,109,368
Messages
5,528,274
Members
409,813
Latest member
robyrux

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...