Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Pareto data with Formula
I want to create a data table for use to create an Automatically updated
Pareto Chart, for this I need that the values automatically be sorted in descending order, so I have the following example scenario: Column A has Labels and Column B has Values A -1 B -2 C - 3 D - 2 E -1 If I use the following formula: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,2),$B$1:$B$ 5,0)) I get B, but there are two 2 and if I use the 3rd largest: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,3),$B$1:$B$ 5,0)) I also Get B instead of the required D. How can I get it so the end results looks like this: C B D A E Thank You. -- Gabriel Camarena R. Delphi Tijuana IT Support |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic Pareto data with Formula
Use a helper column to find the Nominal Rank of the scores, then use this for
the INDEX. C2: =RANK($B2,$B$2:$B$6)+COUNTIF($B$2:B2,B2)-1 D2: =INDEX($A$2:$A$6,MATCH(ROWS($1:1),$C$2:$C$6,0)) HTH Peter "GCRDelphi" wrote: I want to create a data table for use to create an Automatically updated Pareto Chart, for this I need that the values automatically be sorted in descending order, so I have the following example scenario: Column A has Labels and Column B has Values A -1 B -2 C - 3 D - 2 E -1 If I use the following formula: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,2),$B$1:$B$ 5,0)) I get B, but there are two 2 and if I use the 3rd largest: =INDEX($A$1:$A$5,MATCH(LARGE($B$1:$B$5,3),$B$1:$B$ 5,0)) I also Get B instead of the required D. How can I get it so the end results looks like this: C B D A E Thank You. -- Gabriel Camarena R. Delphi Tijuana IT Support |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pareto/Bar charts | Excel Discussion (Misc queries) | |||
How I do the pareto charts? | Charts and Charting in Excel | |||
Pareto of data | Excel Discussion (Misc queries) | |||
ROI,Pareto, T test | Charts and Charting in Excel | |||
Pareto With Excel | Excel Discussion (Misc queries) |