CODEWITHSUNDEEP
excelvbaexcel-formula
leora
leora

Reputation: 196589

is there an excel formula to find max item in a pivot table

i have a pivot table with:

  1. Teams as the Row Label
  2. Regions as the Column Label

and count of Application data

i want to have a column that displays the Region with the highest count for each Team.

So if my table looks like this:

Team | US | Europe | Asia
Team A 3 2 1
Team B 1 2 3
Team C 1 1 3
Team D 1 2 3

for Team A it would return US. For Team D it would return Asia is this possible?

Upvotes: 0

Views: 6560

Answers (2)

pnuts
pnuts

Reputation: 59475

There is no need to copy the PT to a 'regular' table: 

=INDEX($C$3:EG$3,,MATCH(MAX(C4:E4),C4:E4,0))

in H4 in the example and copied down to suit may to serve.

SO4779080 example

However, this does only pick the first instance of a maximum value that is repeated within a row (example adapted to show this) whereas Conditional Formatting does not have this possible drawback when applied to =C4:E7 with a rule such as: 

=C4=MAX($C4:$E4)

Upvotes: 0

Karsten W.
Karsten W.

Reputation: 18440

Two ideas:

  • use conditional formating on the pivot table
  • try something along a custom maxif function (see this blogpost) on the pivot datasource

EDIT: If you copy the pivot table to a normal sheet, you could use the index twice to get what you want. Assuming your example data resides in A1:D5, you could put in E2 for instance

=INDEX(B$1:D$1,INDEX(B2:D2,MAX(B2:D2)))

If you have a row with two maximum values, the first max column will be returned.

Google refine appears to be a free tool very well suited for this kind of data manipulation.

Upvotes: 1

Related Questions