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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 6, 2019
Messages
4
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!
 

Stinkydaddy

New Member
Joined
Mar 6, 2019
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

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
Top