Need an Averaging Formula

Ima_learnin'

Active Member
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
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
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
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

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

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

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

Replies
6
Views
130
Replies
2
Views
281
Replies
1
Views
348
Replies
4
Views
299
Replies
3
Views
357

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

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.

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

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