RANK with several conditions (like for the SORT function)?

[Guest]

In the RANK() function I can rank a value depending on one set of values (one
condition), but when you SORT data you can choose a second (and a third)
condition.

Is there a way to RANK with two sets of values (two conditions)?

 

[T. Valko]

Give us the details and we'll see what we can do! Be specific! The more info
you provide the better your chance of getting a solution.

Biff

 

[Bob Phillips]

You could always concatenate the two conditions in a helper column and rank
on that column.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Here're two alternatives I want to achieve:

1st alternative
: : Value 1 : Value 2 : Rank
A : 8 : 5 : 1
B : 8 : 6 : 2
C : 8 : 7 : 3
D : 10 : 5 : 4
E : 10 : 6 : 5
F : 10 : 7 : 6
G : 15 : 5 : 7
H : 15 : 6 : 8
I : 15 : 7 : 9

2nd alternative
: : Value 1 : Value 2 : Rank
A : 8 : 7 : 1
B : 8 : 6 : 2
C : 8 : 5 : 3
D : 10 : 7 : 4
E : 10 : 6 : 5
F : 10 : 5 : 6
G : 15 : 7 : 7
H : 15 : 6 : 8
I : 15 : 5 : 9

(I hope it shows correctly)

 

[Guest]

I really don't want to do that. Excel should be able to manage this.
But if I do - how do I do it?

 

[T. Valko]

For alternative 1:

=SUMPRODUCT(--((A1&B1)+0>(A$1:A$9&B$1:B$9)+0))+1

Copy down as needed.

For alternative 2:

Hmmm.....that may take awhile to figure out!

Biff

 

[T. Valko]

Based on your alternative1 sample:

......A.....B.....C
1...8......5........
2...8......6........
3...8......7........

In column C enter this formula and copy down:

=(A1&B1)+0

Then:


......A.....B.....C......D
1...8......5.....85.......
2...8......6.....86.......
3...8......7.....87.......

In column D use the Rank function (copied down):

=RANK(C1,C$1:C$3,1)

If you choose to not use this method you can't use the Rank function. It's
not very versatile and won't handle arrays. See my other reply.

Biff

 

[Guest]

Thanx, alt. 1 works.
I hope you can solve no 2 also.

I do have some questions t understand what's happening:

1) What does the "--" do?

2) What does the "+0" do?

3) When I remove them it give the rank order:

7
8
9
1
2
3
4
5
6
Why?

4) How should I think in the over all understanding of what the formula do?

The part where you adding the number together with "&" I can understand
because I tried only that and got values in a new column that I in a second
step could do a normal RANK(). To go from there to combine this values with
the ranking evaluation I don't get - and you do that wiyhout using RANK.
What's with the comparingwith ">" for example?

It's not so easy to get, even though you've been a great help.

 

[Bob Phillips]

I do have some questions t understand what's happening:

1) What does the "--" do?

It coerces TRUE/FALSE values to 1/0 so tha you can do math on them. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

2) What does the "+0" do?

The & creates a string, the +0 coerces that string to its numeric value

3) When I remove them it give the rank order:

7
8
9
1
2
3
4
5
6

Becuase the formula isn't working as designed now.

4) How should I think in the over all understanding of what the formula

do?


I am wondering if it is correct. For instance, if you replace line 3 from 8
7 to 8 15, it goes to 9 th in rank. Is this correct or should it still be 3?
If the latter try

=SUMPRODUCT(--((A1*1000+B1)>(A$1:A$9*1000+B$1:B$9)))+1

Note that because I am now using * and + instead of &, I don't need the +0.