Reputation: 21
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: 372
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
Upvotes: 1