Need an Averaging Formula

[Ima_learnin']

Hello All,

I have in row B3:M3 months Jan through Feb, in row 4, same columns, some data that links to other sheets…I need a formula in N4 that dynamically only averages the cells that have data…for example, currently only Jan and Feb have data….the rest of the cells have a #DIV/0! Error until the source file is populated, I need the formula to give me the average for Jan and Feb…then next month when Mar has data average Jan through Mar….hope this makes sense

Thanks, Ima Learnin’

 

[Brian from Maui]

Try,

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

Change the range to suit your needs,

What formula is giving the #DIV/0! error message?

 

[Ima_learnin']

I got rid of the div error's with the iserror function and the formula worked perfect....thanks...it didn't work when I left the div errors in the sum range.

Thanks again

 

[Brian from Maui]

I got rid of the div error's with the iserror function and the formula worked perfect....thanks...it didn't work when I left the div errors in the sum range.

Thanks again

Not too fond of ISERROR,

=SUMIF(A1:A4,"<>#div/0!")/MAX(1,COUNTIF(A1:A4,">0"))

What's the formula that produces the #DIV/0!?

 

[Ima_learnin']

great thanks...oh the source of the div error is the users file has some formulas that are dividing by cells that are not yet populated so it's dividing by zero thus the error result.

thanks, Ima Learnin'

 

[Bothell]

Brian,

What is the purpose of the MAX in your formula?

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

Wouldn't it work just fine with the following?

=SUM(A1:A4)/COUNTIF(A1:A4,">0")

Thanks!

~bothell

 

[Brian from Maui]

great thanks...oh the source of the div error is the users file has some formulas that are dividing by cells that are not yet populated so it's dividing by zero thus the error result.

thanks, Ima Learnin'

Use something to the effect of,

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

Where B1 is the divisor

 

[Brian from Maui]

Brian,

What is the purpose of the MAX in your formula?

=SUM(A1:A4)/MAX(1,COUNTIF(A1:A4,">0"))

Wouldn't it work just fine with the following?

=SUM(A1:A4)/COUNTIF(A1:A4,">0")

Thanks!

~bothell

Bothell,

As explained by Aladin,

If COUNTIF(A1:A4,">0") results in 0, we'd get #DIV/0!

MAX(1,0) ==> 1 rescues us.