#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.
 

Some videos you may like

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
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,192
=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,192

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,192
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top