Average

  • Thread starter Thank you, Heath
  • Start date
T

Thank you, Heath

Hi...how do I average a column of percentages, one for each month if the
month has yet to pass. There is a formula in each of these cells (doing
something else)and its causing excel to return "#DIV/0" error because I want
to average all 12 months but only 2 of the months have data in them so far
this year....i dont want to change the formula each month...understand?

Thank you so much
Heath Taubitz
Lockheed Martin
 
G

Gary''s Student

Let's say in C1 thru C12 we had:

=B1/A1
=B2/A2
=B3/A3
=B4/A4
=B5/A5
=B6/A6
=B7/A7
=B8/A8
=B9/A9
=B10/A10
=B11/A11
=B12/A12
replace these with:

=IF(A1=0,"",B1/A1)
=IF(A2=0,"",B2/A2)
=IF(A3=0,"",B3/A3)
=IF(A4=0,"",B4/A4)
=IF(A5=0,"",B5/A5)
=IF(A6=0,"",B6/A6)
=IF(A7=0,"",B7/A7)
=IF(A8=0,"",B8/A8)
=IF(A9=0,"",B9/A9)
=IF(A10=0,"",B10/A10)
=IF(A11=0,"",B11/A11)
=IF(A12=0,"",B12/A12)

The average will now work because the average ignores blanks.
 
T

Thank you, Heath

OK, I did...but now the value is coming back "0" Im not a novice but not an
expert either...I cant figure this out.. And will this allow me to use the
basic "Average" formula at the bottom of this coloum?
 
T

Thank you, Heath

This is what Im doing...the first two coloums are being divided as your
example showed...I changed out the D3/C3 for =IF(D3=0, "", C3/B3)....
it now gives me a "%0.0" as the answer to dividing 90/120...instead of %75.0

Col C Col D Col E

(row 3)January 120 90 75.0% $
452,549.00
February 112 70 62.5% $
446,591.00
March - - #DIV/0!
#DIV/0! #DIV/0!
April - - #DIV/0!
#DIV/0! #DIV/0!
May - - #DIV/0!
#DIV/0! #DIV/0!
June - - #DIV/0!
#DIV/0! #DIV/0!
July - - #DIV/0!
#DIV/0! #DIV/0!
August - - #DIV/0!
#DIV/0! #DIV/0!
September - - #DIV/0!
#DIV/0! #DIV/0!
October - - #DIV/0!
#DIV/0! #DIV/0!
November - - #DIV/0!
#DIV/0! #DIV/0!
December - - #DIV/0!
#DIV/0! #DIV/0!
 
G

Gary''s Student

No. The values must be blank and not 0.. the AVERAGE() function considers 0
to be real data to be averaged. You need blanks not zeros. Try this:

=IF((A1=0)+(B1=0),"",B1/A1)

Remember the goal is to get blanks whenever either A or B is either 0 or blank
 
T

Thank you, Heath

You my friend are a genius...

You got it...=IF((A1=0)+(B1=0),"",B1/A1) did the trick...


Heath
 
G

Gary''s Student

Excellent!!

Now try:

=IF((B3=0)+(C3=0), "", C3/B3)
if the original was =C3/B3

the last expression in the IF statement should match the original formula
 
Top