Max Value
 

Is there a formula or function I can use to change the Value in Column B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2 and so
on. When 2 numbers are same then they get equal points.


Thanks
AM

Try the RANK() function:

=RANK(A2,$A$2:$A$10,0)



"Andy" wrote in message
...
Is there a formula or function I can use to change the Value in Column B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2 and
so
on. When 2 numbers are same then they get equal points.


Thanks
AM

Thanks Steve, 1 problem with the result. When I have 2 same values it assign
the same rank which is good(thats what I want) but then it skip the the next
rank number( See beloe its missing rank 5)
24 8
53 1
40 2
34 6
38 4
38 4
25 7
23 9
39 3
Any other ideas ?

Thanks
AM

"Steve R" wrote:

Try the RANK() function:

=RANK(A2,$A$2:$A$10,0)



"Andy" wrote in message
...
Is there a formula or function I can use to change the Value in Column B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2 and
so
on. When 2 numbers are same then they get equal points.


Thanks
AM

Andy
My guess is that you'll need a customs function but, let's see if someone
out there proves me wrong.

Steve

"Andy" wrote in message
...
Thanks Steve, 1 problem with the result. When I have 2 same values it
assign
the same rank which is good(thats what I want) but then it skip the the
next
rank number( See beloe its missing rank 5)
24 8
53 1
40 2
34 6
38 4
38 4
25 7
23 9
39 3
Any other ideas ?

Thanks
AM

"Steve R" wrote:

Try the RANK() function:

=RANK(A2,$A$2:$A$10,0)



"Andy" wrote in message
...
Is there a formula or function I can use to change the Value in Column
B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2
and
so
on. When 2 numbers are same then they get equal points.


Thanks
AM

Any Clue how can I do that ? We will wait for other response if I don't get
any response let me know about the custome finction.

Thanks
AM


"Steve R" wrote:

Andy
My guess is that you'll need a customs function but, let's see if someone
out there proves me wrong.

Steve

"Andy" wrote in message
...
Thanks Steve, 1 problem with the result. When I have 2 same values it
assign
the same rank which is good(thats what I want) but then it skip the the
next
rank number( See beloe its missing rank 5)
24 8
53 1
40 2
34 6
38 4
38 4
25 7
23 9
39 3
Any other ideas ?

Thanks
AM

"Steve R" wrote:

Try the RANK() function:

=RANK(A2,$A$2:$A$10,0)



"Andy" wrote in message
...
Is there a formula or function I can use to change the Value in Column
B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2
and
so
on. When 2 numbers are same then they get equal points.


Thanks
AM

Let A1:A10 house the scores of interest, with a label (something like
Score) in A1.

Labels and Formulas...

B1: I-Rank

B2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),"",RANK(A2,$A$2: $A$10))

C1: F-Rank

C2, copied down:

=IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),VLOOKUP(A2,$A$2: B2,2,0),RANK(B2,$B$2:$B$10,1))

Andy wrote:
Thanks Steve, 1 problem with the result. When I have 2 same values it assign
the same rank which is good(thats what I want) but then it skip the the next
rank number( See beloe its missing rank 5)
24 8
53 1
40 2
34 6
38 4
38 4
25 7
23 9
39 3
Any other ideas ?

Thanks
AM

"Steve R" wrote:


Try the RANK() function:

=RANK(A2,$A$2:$A$10,0)



"Andy" wrote in message
...

Is there a formula or function I can use to change the Value in Column B
based on the value changed in Column A Pelase see e.g below
Col A Col B
24 6
53 1
40 2
34 5
38 4
38 4
25 5
23 5
39 3
Whenever I see the highest Value it should get 1 then next highest 2 and
so
on. When 2 numbers are same then they get equal points.


Thanks
AM