Help with Rank

[Silo]

Hello Everyone

I need help with a ranked formula.

Here’s what I need.

I like to rank values in I3:I19 in descending order, with there corresponding Text Value (Employee Names) which are in range A3:A19.

I like this ranking in Columns L or M starting in the 3rd row.

Any help would be appreciated

Thanks

 

[xld]

Try this array formula

=MATCH(I3,LARGE(IF($A$3:$A$19=A3,$I$3:$I$19),ROW(INDIRECT("1:"&COUNTIF($A$3:$A$19,A3)))),0)

 

[Silo]

xld

I try that formula with no success

 

[Silo]

Is there something else I need to do to this formula?

 

[xld]

Did you array enter it (Ctrl-Shift-Enter)

 

[Silo]

How do I do that?

 

[jeffreybrown]

Select the cell with the formula - select F2 - select Ctrl + Shift + Enter

 

[Silo]

Yes, I copied the the rest of the cell in the range where I need them ranked L3:L19 and they returned 1

I must be doing something wrong

 

[Silo]

Yes, then I copied the formula to the rest of the cell's in the range I need L3:L19.

Returns a 1 in each cell.

I need the Text Values in range A3:A19 and the values in I3:I19 in decending order.

Bacislly I like to know by Name, Joe and his Units sold Joe 145 and so on by employee and their repective value in decending order.

Thanks everyone

 

[barry houdini]

Hello Silo,

So in A3:A19 you have 17 different names and you want to list them in order depending on the values in I3:I19?

I'd use a "helper" column, e.g. in L3 copied down to give a "unique rank"

=RANK(I3,I$3:I$19)+COUNTIF(I$3:I3,I3)-1

Then in M3 copied down to give names in order

=INDEX(A$3:A$19,MATCH(ROWS(M$3:M3),L$3:L$19,0))

and associated units sold in N3 copied down

=INDEX(I$3:I$19,MATCH(ROWS(M$3:M3),L$3:L$19,0))

hide column L if you wish......