CODEWITHSUNDEEP
excelpivot-table
gmorissette
gmorissette

Reputation: 291

Pivot table help / Data fetching

I am creating a spreadsheet for sports stats and am having a hard time writing one specific formula. I am keeping track of the shots on goal of every team in a league throughout the season. Each team has an assigned column with its name on the first row of the spreadsheet. The shots on goal are entered below and organized according to the dates of the events. Between the name of the team and the results are some stats (average, standard deviation etc.) These stats are on the same row for each team.

Here's the problem: I would like to create a separate section in the same spreadsheet where the stats would be organized in a ascending or descending order (accordingly) with the corresponding team names side by side. The formula would more or less look like:

<Takes stats in this row, sort in determined order here and place corresponding team name next to stat>

I made a quick demo of what I have in mind. I hope it will be helpful.

SO24643835 question example

Upvotes: 0

Views: 55

Answers (1)

pnuts
pnuts

Reputation: 59485

I suggest not using a PivotTable (quite unlike me!). If "Team A" is in B1, create a column of values somewhere, say starting in H2, with the positions (01, 02, etc) to suit but use 1, 2 etc for convenience. Then:

in I2: =INDEX(B$1:E$1,MATCH(J2,B$2:E$2,0))
in J2: =SMALL($B$2:$E$2,H2)

both copied down to suit.

I'm afraid not foolproof - if two teams have exactly the same STD.DEV. value then one team name will appear twice and the other not at all.

Upvotes: 0

Related Questions