ExcelBanter - rank function error

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - rank function error (https://www.excelbanter.com/excel-worksheet-functions/155773-rank-function-error.html)

yshridhar

rank function error

Rank() function is giving errors. eg
data rank
100 1
100 1
98 3
any suggestion please.
Thank you
Sridhar

T. Valko

rank function error

That's how RANK works. What result did you expect?

data rank
100 1
100 1
98 2

If that's what you want try this:

=IF(A2="","",SUMPRODUCT(--(A2<A$2:A$4),1/COUNTIF(A$2:A$4,A$2:A$4&""))+1)

--
Biff
Microsoft Excel MVP

"yshridhar" wrote in message
...
Rank() function is giving errors. eg
data rank
100 1
100 1
98 3
any suggestion please.
Thank you
Sridhar

David Hilberg

rank function error

That is deliberate behavior, not an error. It is as if two Olympic
athletes come in first with the same score, and the next runner up is
given a bronze medal, not silver.

- David

yshridhar wrote:
Rank() function is giving errors. eg
data rank
100 1
100 1
98 3
any suggestion please.
Thank you
Sridhar

yshridhar

rank function error

Thank you Mr. Bill. What I am expecting is
data rank expect
100 1 1
100 1 1
99 3 2
regards
Sridhar

"T. Valko" wrote:

That's how RANK works. What result did you expect?

data rank
100 1
100 1
98 2

If that's what you want try this:

=IF(A2="","",SUMPRODUCT(--(A2<A$2:A$4),1/COUNTIF(A$2:A$4,A$2:A$4&""))+1)

--
Biff
Microsoft Excel MVP

"yshridhar" wrote in message
...
Rank() function is giving errors. eg
data rank
100 1
100 1
98 3
any suggestion please.
Thank you
Sridhar

All times are GMT +1. The time now is 05:31 AM.