How to add new calculations to existing table based on text field with multiple entries?-CodePudding

Question: How can I populate the [score [TXT]] columns with the specified calculation? Sometimes the calculations will be based off multiple rows depending on the value in the [game] column.

I have a table with Metascores and game names, and want to apply some sort of formula that automatically calculates the AVG, MAX, and MIN for the entry. The table above has my desired output. I am using Office 365 - Excel.

Current table

Metascore	score AVG	score MAX	score MIN	game
87				Assassin's Creed Odyssey
86				Assassin's Creed Odyssey
83				Assassin's Creed Odyssey
66				Bleeding Edge
62				Bleeding Edge

Desired output

Metascore	score AVG	score MAX	score MIN	game
87	85.3	87	83	Assassin's Creed Odyssey
86	85.3	87	83	Assassin's Creed Odyssey
83	85.3	87	83	Assassin's Creed Odyssey
66	64	66	62	Bleeding Edge
62	64	66	62	Bleeding Edge

Some titles only occur once, some several times. Is there a formula or script I can apply that loops through the table and applied the calculation, or a different suggestion of an output?

Thanks for your help!!

CodePudding user response：

=UNIQUE(E2:E6) in for instance E10 =AVERAGEIF($E$2:$E$6,$E$10#,A2:A6) in A10 and copy to the right.

Or in one go using LET:

=LET(data,A2:E6, 
     game,INDEX(data,,5),
     unique,UNIQUE(game),
CHOOSE({1,2,3,4,5},
       AVERAGEIF(game, unique,INDEX(data,,1)),
       AVERAGEIF(game, unique,INDEX(data,,2)),
       AVERAGEIF(game, unique,INDEX(data,,3)),
       AVERAGEIF(game, unique,INDEX(data,,4)),
       unique))

Edit:

After the changed description, this is what you need:

In B2 use: =AVERAGEIF(E2:E6,E2:E6,A2:A6)

In C2 use: =MAXIFS(A2:A6,E2:E6,E2:E6)

In D2 use: =MINIFS(A2:A6,E2:E6,E2:E6)

If you'd have your data in two columns (let's say A20:B20) and would want a summary elsewhere, you could use the following:

=LET(data,A2:B6,
     game,INDEX(data,,2),
     score,INDEX(data,,1),
     unique,UNIQUE(game),
CHOOSE({1,2,3,4},
     unique,
     AVERAGEIF(game,unique,score),
     MAXIFS(score,game,unique),
     MINIFS(score,game,unique)))