Prev: Column link
Next: TimeStamp
From: GCRDelphi on 19 Apr 2010 16:18
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
From: Billy Liddel on 19 Apr 2010 20:58
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
 | 
Pages: 1
Prev: Column link
Next: TimeStamp