Need an Averaging Formula

Ima_learnin'

Active Member
Joined
Dec 14, 2002
Messages
266
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’
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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'

Active Member
Joined
Dec 14, 2002
Messages
266
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Ima_learnin' said:
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'

Active Member
Joined
Dec 14, 2002
Messages
266

ADVERTISEMENT

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

New Member
Joined
Aug 4, 2003
Messages
20
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Ima_learnin' said:
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
Bothell said:
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,091
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top