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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,532
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,143,907
Messages
5,721,445
Members
422,363
Latest member
Bogus_Potatoes

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