AVERAGEIF with a twist..

**Svilen** · 01-12-2016, 11:37 AM

Hi all,

I am looking to do an AVERAGEIF, but only for "x" number of the highest values within a range.

Example:

We have one tab with 3 columns:

Office Name City Employees
Clarendon DC 4
Georgetown DC 5
Horace Mann DC 6

There are multiple offices within the same city.

We have another tab with 3 columns:

City Number of selected offices Avg number of employees for selected offices
DC 2 ????

As you can see, I am looking to compute the avg number of employees in an office for DC, but we are only taking the 2 offices with the highest number of employees.

I can´t think of any conceivable way to do this. Can anyone help please?

**Glenn Kennedy** · 01-12-2016, 11:47 AM

Is it always 2 offices that you want to use for the average?

**hemesh** · 01-12-2016, 11:48 AM

@ Svilen could you post a workbook to make it more clear

**Svilen** · 01-12-2016, 11:55 AM

Originally Posted by Glenn Kennedy

Is it always 2 offices that you want to use for the average?

No, it is not. The number of offices varies in function of a different spreadsheet, which is irrelevant to this problems.
The number of offices selected for the average will always be lower or equal to the number of total offices in the range.

Originally Posted by hemesh

@ Svilen could you post a workbook to make it more clear

I will try to post a workbook a bit later today when things calm down in the office.

Thanks!

**Glenn Kennedy** · 01-12-2016, 11:58 AM

try this array formula:

=SUMPRODUCT(LARGE(IF($B$2:$B$7=$E2,$C$2:$C$7),ROW(INDIRECT("1:"&$F2))))/$F2

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

City names in B, values in C, selected city and number to average in E2 and F2. copy down if needed.