Rolling Average of last ten of a certain criteria

[Stinkydaddy]

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

 

[steve the fish]

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.

 

[Stinkydaddy]

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!

 

[steve the fish]

Are there any value errors in column L?

 

[Stinkydaddy]

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

 

[steve the fish]

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.

 

[Stinkydaddy]

Ok thank you