Finding state that contains employees biggest sale

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data like this

EMPLOYEE SALE STATE
Joe 10.00 PA
Joe 20.00 VA
Sue 5.00 TX
Sue 10.00 WA

For each employee, how do I find the state that corresponds to the largest
sale. For example, for Joe this would be VA since his largest sale of 20.00
was to VA.
 
one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1:$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name
 
..... no it doesn't!!!

Toppers said:
one way:

=INDEX($C$1:$C$4,MATCH(MAX(($A$1:$A$4="Joe")*($B$1:$B$4)),$B$1:$B$4,0))

Enter with Ctrl+Shift+Enter

Substitute "Joe" with cell containing name
Click to expand...
 
How about a pivot table. Place your cursor in the data set and select Data ->
Pivot Table (Brings up the wizard. You can follow the wizard or more likely
just select Finish)

Place the names in the left column and then the state next to it. In the
center section add the amounts. Now right click on the State field and select
Field Settings -> Advanced. Change the order to desending and Select Show Top
(1).

You may want to get rid of Subtotals and a few other things just to clean it
up a tad...
 
Array entered:

=INDEX(C1:C4,MATCH(1,(A1:A4="Joe")*(B1:B4=MAX(IF(A1:A4="Joe",B1:B4))),0))
 
.. what I intended ...

=INDEX($C$2:$C$5,MATCH(MAX(($A$2:$A$5="Joe")*($B$2:$B$5)),($A$2:$A$5="Joe")*($B$2:$B$5),0))

entered with Ctrl+Shift+Enter
 
I have pivot close to working. What do I enter as field under Select Show Top?
It seems to only offer one value, Sales - but that doesn't work.
 
Back
Top