Rolling average of the last 4 populated cells

Nyrrocdooh

New Member
Joined
Dec 30, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have several long columns of data that will be added to each week.
I need to calculate the rolling average for each column, of the last 4 populated cells - excluding blanks/zero's/N/As
For eg, cell range starts B4, data is added each week, there may be a blank cell reported in my data, which is fine.
I'm currently down to cell B137 but the previous 4 cells do not have data, (B134 - B137).
Therefore, I would be looking to calculate the average for the 4 cells above that DO have data, B130 - B133.

Then as I add data, the average would need to be calculated

Looking for a formula (or multiple), not a VBA solution.
I've looked extensively on here and other sites but to no avail.

Here's an example of my data.

1735571803809.png


Any help would be gratefully received.

Nyrrocdooh
 
Hi & welcome to MrExcel.
How about
Excel Formula:
=AVERAGE(TAKE(TOCOL(B4:B1000,3),-4))
 
Upvote 0
I assume you are adjusting the column for your other columns formulas (copying the original formula from column B should do that automatically).

Do you have calculations set to manual or automatic?
Do the values change if you press the F9 key?
 
Upvote 0
Do you mean if you add another number to the bottom of the data, nothing changes?
 
Upvote 0
Do you mean if you add another number to the bottom of the data, nothing changes?
Hey!
Thanks for the reply!

Data will be added to the bottom of each column, on a weekly basis. I need any new data to be included in the average of the last 4 populated cells.

Any help would be appreciated.
 
Upvote 0
As long as you use a large range, as I showed, then that data will automatically be included.
 
Upvote 0

Forum statistics

Threads
1,226,849
Messages
6,193,325
Members
453,790
Latest member
yassinosnoo1

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