# Average with multiple MAXes

#### gumbee

##### New Member
(A) | (B) | (C)
Mark | 1 | 100
Mark | 2 | 90
Mark | 3 | 80
Kate | 1 | 70
Kate | 2 | 75
Kate | 4 | 90
John | 2 | 100
John | 3 | 95
John | 4 | 105

I'm trying to query a given week(B) for each employee(A) and trying to determine the AVG miles traveled(C) for the most recent week. The missing weeks are vacation weeks so I like to include the week before in the average where relevant:

e.g. - A query for week 3 would return:
Mark 3 80
Kate 2 75 (most recent)
John 3 95
=83.33

i was trying with the MAX function for col B but probably missing something simple

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### facethegod

##### Well-known Member
heres one way.. if your list is sorted by name and the week (ascending) as displayed

=AVERAGE(IF(COUNTIF(OFFSET(A1:A9,,,ROW(A1:A9)-ROW(A1)+1),A1:A9)=COUNTIF(A1:A9,A1:A9),C1:C9))

Confirmed with ctrl+shift+Enter, not just enter

#### gumbee

##### New Member
facethegod,

Thanks for the reply. I should have specified but the ascending weeks is NOT the case for the whole sheet (not a big deal because I can do sort first for most of the sheets).

#### gumbee

##### New Member
I ended up adding a column (D) to the list to show if the next week is a vacation week and copied down:

Code:
``=IF(B2=B3-1,"","V")``

Then I just searched for either a matching week (in F2) OR a 'V' in col (D) via an array entered formula:

Code:
``{=AVERAGE(IF((B2:B10=F2)+(D2:D10="V"),C2:C10))}``

I would be curious though if it's possible to do this with a MAX function via an array for each employee. Haven't found much via google and I keep getting the MAX of the entire array

Replies
3
Views
216
Replies
6
Views
1K
Replies
2
Views
173
Replies
5
Views
298
Replies
13
Views
364

1,191,420
Messages
5,986,464
Members
440,031
Latest member
davidvillegasr

### 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.

### Which adblocker are you using?

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

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