| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding state that contains employees biggest sale
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. -- Richard |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
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 "Richard" wrote: 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. -- Richard |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
..... no it doesn't!!!
"Toppers" wrote: 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 "Richard" wrote: 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. -- Richard |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
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... -- HTH... Jim Thomlinson "Richard" wrote: 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. -- Richard |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
Array entered:
=INDEX(C1:C4,MATCH(1,(A1:A4="Joe")*(B1:B4=MAX(IF(A 1:A4="Joe",B1:B4))),0)) -- Biff Microsoft Excel MVP "Toppers" wrote in message ... .... no it doesn't!!! "Toppers" wrote: 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 "Richard" wrote: 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. -- Richard |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
.. 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 "Toppers" wrote: .... no it doesn't!!! "Toppers" wrote: 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 "Richard" wrote: 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. -- Richard |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
Works great. I'm just learning the power of the match, index combinations.
-- Richard "Toppers" wrote: . 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 "Toppers" wrote: .... no it doesn't!!! "Toppers" wrote: 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 "Richard" wrote: 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. -- Richard |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Finding state that contains employees biggest sale
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. -- Richard "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Richard" wrote: 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. -- Richard |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Converting State Names to State Abbreviations | Excel Discussion (Misc queries) | |||
| Finding a name with biggest number | Excel Worksheet Functions | |||
| If function with date; if sale is Oct place sale $ amount here | Excel Worksheet Functions | |||
| Would u help me with a Biggest Movers type of comparison? | Excel Discussion (Misc queries) | |||
| skus in columm a for each sale, qty. of skus sold, each sale in co | Excel Worksheet Functions | |||
Linear Mode
