Average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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?
 
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!
 
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
 
You my friend are a genius...

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


Heath
 
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
 
Back
Top