Duplicate ranks while ranking with Countif for different numbers

sgr791990

New Member
Joined
Feb 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi All,

I am trying to rank a set of numbers with countif but it gives me the same rank for different numbers while rank function works fine as shown below:

1612800828679.png


Here are the formulae used for the first row. The same formulae have been dragged down to rest of the rows.
=COUNTIF($E$2:$E$4,">"&E2)+1
=RANK.EQ(E2,$E$2:$E$4)

Has anyone faced this issue before or has any ideas how to solve this?
I need to use countifs because i am ranking based on multiple fields in my original dataset and cannot use the rank function.

Any help is appreciated.

Regards,
Syed
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,562
Welcome to MrExcel!

It actually works for me (see below). This appears to be a kind of rounding error. The way Excel stores numbers causes some issues when you have decimals. It is a bit rare when comparing a cell to itself, but it can happen. You can try to use SUMPRODUCT instead, see column H.

Book1
EFGH
1ValuesCountifRankSumproduct
248737.76222
336113.11333
454287.76111
Sheet5
Cell Formulas
RangeFormula
F2:F4F2=COUNTIF($E$2:$E$4,">"&E2)+1
G2:G4G2=RANK.EQ(E2,$E$2:$E$4)
H2:H4H2=SUMPRODUCT(--(ROUND($E$2:$E$4,2)>ROUND(E2,2)))+1
 
Solution

sgr791990

New Member
Joined
Feb 8, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Welcome to MrExcel!

It actually works for me (see below). This appears to be a kind of rounding error. The way Excel stores numbers causes some issues when you have decimals. It is a bit rare when comparing a cell to itself, but it can happen. You can try to use SUMPRODUCT instead, see column H.

Book1
EFGH
1ValuesCountifRankSumproduct
248737.76222
336113.11333
454287.76111
Sheet5
Cell Formulas
RangeFormula
F2:F4F2=COUNTIF($E$2:$E$4,">"&E2)+1
G2:G4G2=RANK.EQ(E2,$E$2:$E$4)
H2:H4H2=SUMPRODUCT(--(ROUND($E$2:$E$4,2)>ROUND(E2,2)))+1
Thanks. I just used the round function with countif and it works. Its weird but it works.
Here is what i used: =COUNTIF($E$2:$E$4,">"&ROUND(E2,2))+COUNTIF(E$2:E2,ROUND(E2,2))

Thanks again. you saved me some urgent manual work :)
 

Forum statistics

Threads
1,181,892
Messages
5,932,669
Members
436,850
Latest member
Jasperlee93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top