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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
most welcome.
thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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
Top