Max Averageif formula

Question

Hi all.

I have a sheet studying tested cars and their corresponding MPG values (see below)

I have already found which manufacturer has the highest average MPG:

{=INDEX(A2:A20,MATCH(MAX(AVERAGEIF(B2:B20,B2:B20)),AVERAGEIF(B2:B20,B2:B20),0))}

The above formula returns the manufacturer name, but now I want the corresponding average for that manufacturer.

Any help would be appreciated.

thanks.

Answer 1

That's great. But check your original formula as that is not supposed to give right result.

Answer 2

Hello Aurelien21

I am V. Arya, Independent Advisor, to work with you on this issue. The picture is missing, hence it is not possible to say what is in column B.

But I doubt if your formula will give right result. Right array entered formula would be

=INDEX(A2:A20,MATCH(MAX(AVERAGEIF(A2:A20,A2:A20,B2:B20)),AVERAGEIF(A2:A20,A2:A20,B2:B20),0))

Below formulas will get you the highest average

=AGGREGATE(14,6,AVERAGEIF(A2:A20,A2:A20,B2:B20),1)

=MAX(INDEX(AVERAGEIF(A2:A20,A2:A20,B2:B20),,))

OR you can array enter following formula

=MAX(AVERAGEIF(A2:A20,A2:A20,B2:B20))

Answer 3

Thank you V. Arya

I will stick to HansV's simpler formula as my actual workbook has some 60,000 entries, so avoiding Arrays where possible is best :)

Answer 4

Ah, how obvious and simple!!

Thanks very much HansV

Answer 5

Let's say your formula for the manufacturer with the highest average MPG is in cell D2.

The following formula will return the average MPG for that manufacturer is

=AVERAGEIF(A2:A20,D2,B2:B20)