Rolling Average of last ten of a certain criteria

Stinkydaddy

New Member
Joined
Mar 6, 2019
Messages
4
Hi. I'm after some help with adding a formula to calculate a rolling average of the last 10 entries of a certain criteria.
In column A I have five different locations. There are multiple entries for each location in the date order of measurements taken and column L has a measurement taken at that date and location. For each entry I want to add an average of the last 10 measurements for that location. I've added a count in column b to count the entries for that location and can manage an Averageif to average the entries for that location, but how do I limit the average to the last ten entries for that location?
Thank you
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi. Theres bound to be something simpler than this but i cant think of it.

=AVERAGEIF(INDEX(A1:A1000,LARGE(IF(A1:A1000="locA",ROW(A1:A1000)),10)):A1000,"locA",INDEX(L1:L1000,LARGE(IF(A1:A1000="locA",ROW(A1:A1000)),10)):L1000)

This assumes the data is housed within 1000 rows. If not change the 1000s in the formula. "locA" is what i have called your location so point that to the appropriate cell. It also requires CTRL-SHIFT-ENTER to enter into a cell rather than just enter.
 
Upvote 0
Hi. Theres bound to be something simpler than this but i cant think of it.

=AVERAGEIF(INDEX(A1:A1000,LARGE(IF(A1:A1000="locA",ROW(A1:A1000)),10)):A1000,"locA",INDEX(L1:L1000,LARGE(IF(A1:A1000="locA",ROW(A1:A1000)),10)):L1000)

This assumes the data is housed within 1000 rows. If not change the 1000s in the formula. "locA" is what i have called your location so point that to the appropriate cell. It also requires CTRL-SHIFT-ENTER to enter into a cell rather than just enter.


Thank you. I tried this and the brackets come up to show that the array is working but it is returning a VALUE error!
 
Upvote 0
Are there any value errors in column L?

Sorry I did get it to work, but seemed to then do something wrong, when I copied it right up the column it is giving the same answer for each line of each location, but should be the average of the last ten for each line, therefore different
Thank you
 
Upvote 0
Sorry that doesnt give me much to work on. The formula will work for the last 10 entries so examine the formulas. Sounds like you need some absolution of the cells but i cant be certain.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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