# Average in rows

1. ## Average in rows

I have included a workbook with this. In column B what I need is the average of the last 5 games a team has played. C being there first game. So for example in row 2 I need the average of air force for games 9 thru 13 so column J thru O. Whereas in row 3 I need Akron for games 7 thru 11, I thru M. Now in row 19 for Brown, I need to average 2, 3, 8,9 and 10 because there is no data for 4 thru 7.

2. Try this,

B2 on sheet1

HTML Code:
``=AVERAGE(OFFSET(D2,0,LARGE(IF(ISNUMBER(D2:AA2),COLUMN(D2:AA2)-COLUMN(D2)),5),,COLUMNS(D2:AA2)-LARGE(IF(ISNUMBER(D2:AA2),COLUMN(D2:AA2)-COLUMN(D2)),5)))``
Ctrl+Shift+Enter and copy down

Hope this helps.

3. This works great thanks so much. I have a small problem which won't matter as the season gets later but early on when I don't have enough games. For example at team that has played only four games it returns an error. Just wondering of the code could be altered for this. Thanks again. I was going to try to write some kind of long if statement with some kind of counter. Your way much easier.

4. Assuming you always have the correct number of games played shown in column C (perhaps use the formula =COUNT(D2:AA2) in C2 copied down) try this formula in B2, confirmed with CTRL+SHIFT+ENTER and copied down

=AVERAGE(IF(COLUMN(D2:AA2)>=LARGE(IF(D2:AA2<>"", COLUMN(D2:AA2)),MIN(5,C2)),IF(ISNUMBER(D2:AA2),D2:AA2)))

5. Another

``Please Login or Register  to view this content.``
Ctrl+Shift+Enter

Hope this helps.

6. Thanks a lot guys :P

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