CODEWITHSUNDEEP
excelvba
Alan Romagnoli
Alan Romagnoli

Reputation: 21

How to choose the highest value based on multiple columns?

I have this table: Performance Table

When the same encounter is recorded I need to get the highest performance. A MAX function will do, but I need to do an AVERAGE function for all the encounters dynamically.

I tried this: =AVERAGEIFS(Player[Player.performance],Player[Player.difficulty],"=4") but gives me the average of all encounters with the Spec 3 and not always the Spec 3 has the highest performance. So I need to do the average for all encounters choosing the highest performance when a given encounter is recorded more than once. I cant figure out.

Any help?

Upvotes: 0

Views: 369

Answers (1)

Tony M
Tony M

Reputation: 1762

Place this formula into E2, but then be sure to hold down ctrl-shift as you click Enter (this makes it an array formula)

=MAX(IF($A$2:$A$16=A2,$D$2:$D$16))

Then drag this formula down. Now you have the maximum for each performance. To remove duplicates, select column A and use the advanced filter as shown in the animated .gif Here's a link to a video to learn more

enter image description here

Upvotes: 1

Related Questions