Average of Adjacent Range

ryusei

New Member
Joined
Jan 7, 2014
Messages
2
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.

ABC
1UsernameDatesAvg Rate
2User 1 18.50
3 12/31/201315.55
4 12/30/201320.34
5 12/27/201324.38
6 12/26/201322.37
7 12/24/201316.29
8 12/23/201318.06
9 12/20/201318.30
10User 2 14.65
11 12/31/20134.73
12 12/26/20133.33
13 12/24/201313.52
14 12/23/201330.36
15 12/20/201317.96
16 12/19/201323.08
17 12/18/201315.14

<tbody>
</tbody><colgroup><col><col><col><col></colgroup>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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.
 
Upvote 0
most welcome.
thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top