azcat90
New Member
- Joined
- Dec 6, 2005
- Messages
- 21
I'lll throw this out to you guys...
In Excel I am working with pasted output from an Access query that summarizes shipped packages by Service Type, pricing zone and rated weight.
Goal is to find the mode weight for each service type.
Currently I pivot the results and make the service type columns, the weights rows and sum of packages the data and then place the following formulas at the tops of the columns:
cell b2 =INDEX($A$5:$A$110,MATCH(MAX(B5:B110),B5:B110,0),1)
cell c2 =INDEX($A$5:$A$110,MATCH(MAX(C5:C110),C5:C110,0),1)
cell d2 =INDEX($A$5:$A$110,MATCH(MAX(D5:D110),D5:D110,0),1)
cell e2 =INDEX($A$5:$A$110,MATCH(MAX(E5:E110),E5:E110,0),1)
This will find the weight row with the max number of packages and then match it with the row header weight value.
I am looking for a less clunky way to derive the mode for each service type from the original summarized query results to streamline the processs for each new customer project.
In Excel I am working with pasted output from an Access query that summarizes shipped packages by Service Type, pricing zone and rated weight.
Goal is to find the mode weight for each service type.
Currently I pivot the results and make the service type columns, the weights rows and sum of packages the data and then place the following formulas at the tops of the columns:
cell b2 =INDEX($A$5:$A$110,MATCH(MAX(B5:B110),B5:B110,0),1)
cell c2 =INDEX($A$5:$A$110,MATCH(MAX(C5:C110),C5:C110,0),1)
cell d2 =INDEX($A$5:$A$110,MATCH(MAX(D5:D110),D5:D110,0),1)
cell e2 =INDEX($A$5:$A$110,MATCH(MAX(E5:E110),E5:E110,0),1)
This will find the weight row with the max number of packages and then match it with the row header weight value.
I am looking for a less clunky way to derive the mode for each service type from the original summarized query results to streamline the processs for each new customer project.
Last edited:
