Average of Adjacent Range

[ryusei]

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.

	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>

 

[cyrilbrd]

Hi, Not sure if this what you want.
Given that data are always within same format:
Formula in D2 is =IF(ISBLANK(A2),"",AVERAGE(C3:C7)) copied down till needed.

 

[ryusei]

Wow. You are a genius. At first glance, I was ready to write back and say that this won't work but then the meaning of what you were doing sunk in. Thanks so much.

 

[cyrilbrd]

most welcome.
thanks for the feedback.