Rolling average of the last 4 populated cells

[Nyrrocdooh]

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.

Any help would be gratefully received.

Nyrrocdooh

 

[Fluff]

Hi & welcome to MrExcel.
How about

=AVERAGE(TAKE(TOCOL(B4:B1000,3),-4))

 

[Nyrrocdooh]

Hi & welcome to MrExcel.
How about

=AVERAGE(TAKE(TOCOL(B4:B1000,3),-4))

Thanks for replying.
Your formula gives me the same answer every time, 18.

 

[Joe4]

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?

 

[Fluff]

Do you mean if you add another number to the bottom of the data, nothing changes?

 

[Nyrrocdooh]

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.

 

[Fluff]

As long as you use a large range, as I showed, then that data will automatically be included.