The example below is contained in tab 3 of my workbook. There are two bits of data I need to return on a separate summary sheet for my boss. The first is the value in column C directly across from the user name (e.g. 18.50 for User 1). This I can do with a vlookup. However, my boss also wants me to return the average of the 5 most recent values, which are displayed in the 5 cells immediately below that (e.g. average(C3:C7) for user 1). Therefore, this formula needs to somehow be based off the username and then take an average of the 5 cells in column C beneath the one in which the username appears.
I've tried a lot of routes and am stumped. Any help would be much appreciated.
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
I've tried a lot of routes and am stumped. Any help would be much appreciated.
A | B | C | |
1 | Username | Dates | Avg Rate |
2 | User 1 | 18.50 | |
3 | 12/31/2013 | 15.55 | |
4 | 12/30/2013 | 20.34 | |
5 | 12/27/2013 | 24.38 | |
6 | 12/26/2013 | 22.37 | |
7 | 12/24/2013 | 16.29 | |
8 | 12/23/2013 | 18.06 | |
9 | 12/20/2013 | 18.30 | |
10 | User 2 | 14.65 | |
11 | 12/31/2013 | 4.73 | |
12 | 12/26/2013 | 3.33 | |
13 | 12/24/2013 | 13.52 | |
14 | 12/23/2013 | 30.36 | |
15 | 12/20/2013 | 17.96 | |
16 | 12/19/2013 | 23.08 | |
17 | 12/18/2013 | 15.14 |
<tbody>
</tbody><colgroup><col><col><col><col></colgroup>