Can you please help me...

In O9:O67 I want the returned Rank to be from 0 to -14, so any number
i.e. -19, -23, -29 will be returned as -14.

In H9 to H67 I have a list of numbers i.e.
116
112
115
117
116
117
122
116
121
120
118
121
120
117
113
119
117
120
122
and in O9:O67 I have
=COUNT(H9)-RANK(H9,$H$9:$H$67,0) i.e.
-19
-29
-23
-12
-19
-12
-12
-19
-2
-4
-9
-2
-4
-12
-27
-7
-12
-4
0

Thank You,

Eamon

Eamon,

Try

=MAX(-14,COUNT(H9)-RANK(H9,$H$9:$H$67,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Eamon Byrne" wrote in message
...
Can you please help me...

In O9:O67 I want the returned Rank to be from 0 to -14, so any number
i.e. -19, -23, -29 will be returned as -14.

In H9 to H67 I have a list of numbers i.e.
116
112
115
117
116
117
122
116
121
120
118
121
120
117
113
119
117
120
122
and in O9:O67 I have
=COUNT(H9)-RANK(H9,$H$9:$H$67,0) i.e.
-19
-29
-23
-12
-19
-12
-12
-19
-2
-4
-9
-2
-4
-12
-27
-7
-12
-4
0

Thank You,

Eamon

Thanks Bob, works perfect.

Best Regards,

Eamon


"Bob Phillips" wrote in message
...
Eamon,

Try

=MAX(-14,COUNT(H9)-RANK(H9,$H$9:$H$67,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Eamon Byrne" wrote in message
...
Can you please help me...

In O9:O67 I want the returned Rank to be from 0 to -14, so any number
i.e. -19, -23, -29 will be returned as -14.

In H9 to H67 I have a list of numbers i.e.
116
112
115
117
116
117
122
116
121
120
118
121
120
117
113
119
117
120
122
and in O9:O67 I have
=COUNT(H9)-RANK(H9,$H$9:$H$67,0) i.e.
-19
-29
-23
-12
-19
-12
-12
-19
-2
-4
-9
-2
-4
-12
-27
-7
-12
-4
0

Thank You,

Eamon