# Averageif, but only average last X # of values

1. ## Averageif, but only average last X # of values

Hi all. For this question I have two spreadsheets involved: Results and Dashboard. "Results" is a spreadsheet logging statistics over a certain time period. Column B contains the value I'm matching against, and AK is the column I'm averaging. Column B contains many different repeating values. Each day I copy and paste onto "Dashboard" in column A, the subset of values that I'm interested in on that day. I have an Averageif function next to that averaging any value from Results column AK if the value matches column B. I'm finding that it would be helpful to also have the averages of the last 10 entries for that particular value since they're in chronological order. The entries won't fall within a specific date range though, so the only criteria will be the fact that they're the last 10 entries. I may want to use the same formula again for a smaller set of entries like 3 or 5. Any help would be greatly appreciated. Thanks in advance!

2. ## Re: Averageif, but only average last X # of values

Try something like this...

Data Range
 A B C D E 1 2 tom 85 Ed 58 3 tom 88 4 tom 93 5 bob 66 6 ed 86 7 tom 101 8 tom 232 9 ed 44 10 bob 99 11 bob 87 12 33 13 ed 44 14 bob 72 15 ------ ------ ------ ------ ------

This array formula** entered in E2:

=AVERAGE(IF(ROW(A2:A14)>=LARGE(IF(A2:A14=D2,ROW(A2:A14)),3),IF(A2:A14=D2,B2:B14)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

For your application change the 3 to 10.

3. ## Re: Averageif, but only average last X # of values

1) Create a couple of named ranges to make th formulas easy to read.
- Press CTRL-F3 to open the Name Manager
- Add the following two named ranges:
>> MyKeys: =OFFSET(Sheet1!\$A\$1, , , COUNTA(Sheet1!\$A:\$A), ) (change the sheet name as needed)
>> MyValues: =OFFSET(MyKeys, , 1, , )

2) Now, assuming one value to Average by is in cell G3, the array formula will now work:
=AVERAGE(AVERAGE(LARGE(IF(MyKeys=G3, MyValues), {1,2,3,4,5,6,7,8,9,10})))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Data Range
 A B C D E F G H 1 Key Value 2 Cat 1 3 Cat 1 cat 5 4 Cat 1 5 Cat 1 6 Cat 1 7 Cat 1 8 Cat 1 9 Cat 1 10 Cat 1 11 Cat 1 12 Cat 2 13 Cat 2 14 Cat 2 15 Cat 2 16 Cat 2 17 Cat 2 18 Cat 2 19 Cat 2 20 Cat 2 21 Cat 2 22 Cat 5 23 Cat 5 24 Cat 5 25 Cat 5 26 Cat 5 27 Cat 5 28 Cat 5 29 Cat 5 30 Cat 5 31 Cat 5

4. ## Re: Averageif, but only average last X # of values

Thanks! I started working off of Tony's because that came in first. I have a new wrinkle. I lied when I said which column I'd be matching against. I'm working with sports statistics, and column B has the player names, but column AZ has a unique player ID created by combining the cells with their name, position, and team. I have many columns pulling data referencing this unique player ID, but for some reason this formula won't work with that, it returns #N/A. The formula works fine when searching by the player name alone. I've triple checked to make sure it's not a reference error from changing between searching for the player name or player ID. It's not a problem with the player ID, because a simple Averageif formula works fine when referencing this. It's also not an issue with the formula creating the player ID, because I copied it and pasted it back as a value and that didn't fix the issue. It's driving me nuts because a test scenario I created worked fine, but the real one won't. Here is the adapted formula I'm using, as well as the simple Averageif formula which works fine.

=AVERAGE(IF(ROW(Results!B:B)>=LARGE(IF(Results!B:B=AZ2,ROW(Results!B:B)),10),IF(Results!B:B=AZ2,Results!AK:AK)))

I used CTRL+SHIFT+ENTER.

=AVERAGEIF(Results!B:B,AZ2,Results!AK:AK)

I'll have to try Jerry's next. I can't for the life of me figure out why this won't work. I also run a countif to see the # of games played and there are plenty of entries according to this formula. Plenty = many more than 10.

=IF(A2="","",COUNTIF(Results!\$B:\$B,\$AZ2))

5. ## Re: Averageif, but only average last X # of values

Originally Posted by smatchymo
=AVERAGE(IF(ROW(Results!B:B)>=LARGE(IF(Results!B:B=AZ2,ROW(Results!B:B)),10),IF(Results!B:B=AZ2,Results!AK:AK)))
Referencing entire columns won't cause an error but you should avoid doing that as it's very inefficient (slow to calculate).

If you can't get it to work post a SMALL sample file (20 rows worth of data is plenty) so we can see what the problem may be.

6. ## Re: Averageif, but only average last X # of values

I had the same problem with Jerry's formula but figured out the issue. Column B on the Results spreadsheet has the occasional #N/A in it which doesn't seem to bother the regular Averageif formula. I've created a new formula to prevent those. Thanks for the help!

7. ## Re: Averageif, but only average last X # of values

Thanks for the tip Tony. I'm working on an NBA spreadsheet. Currently there are 11,265 rows and I have no clue how many there will be by seasons end. I was hoping for a formula where I wouldn't have to go CTRL+SHIFT+ENTER because I know those can be slow. I've learned some other tricks since my NFL spreadsheet though and should be able to manage. The NFL sheet takes 40 minutes to calculate when loading fresh data each week... But since substituting Vlookup with Index/Match, as well as combining cells to create unique player ID's instead of using a complex Index/Match formula matching against two criteria (Player Name & Team), I've been able to build a much quicker NBA spreadsheet.

8. ## Re: Averageif, but only average last X # of values

Originally Posted by smatchymo
Currently there are 11,265 rows and I have no clue how many there will be by seasons end.
Use dynamic ranges or refer to a range that is bigger than you think you'll need but less than the entire column.

9. ## Re: Averageif, but only average last X # of values

Good deal. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1