Excel Rank Function not working properly due to weird cell values
Hi All,
I have a weird situation in Excel Rank.
Please see attached file and rows highlighted, they have value 8.5 in the cells.
But one ranks as 102 & the other as 101. If I manually type 8.5 in these cells then the rank works alright.
Can anyone tell me if there is anything hidden inside these numbers which we don't see ?
BR,
Last edited by ibuhary; 07-26-2021 at 03:41 AM. Reason: Title Change
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, in detail.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Done
Thank you.
That's better.
Because this information was imported elsewhere, it may have a non-printable character. Select column "A", go to Data>Text to columns and press Finish directly without any other actions
Diana Tanase
If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
It looks like standard floating point error. I put something like =(A100-8.5) into a convenient cell, format as scientific, and copied down to cover the suspect cells. I see that the value in A102 is slightly smaller than the value in A106 -- to the tune of about 3E-15. The RANK() function sees that slight difference which is reflected in the output.
Solutions vary, and can depend on the strategy you want to use to deal with floating point errors. In Excel, the usual first recommendation is to incorporate a ROUND() function in the sequence. Maybe something like RANK(ROUND(A2,n),...) Where n is a number indicating your expected precision.
Not entirely sure why you think this is wrong. I've made the data into a Table and sorted the numbers into descending order. I've added various comparisons. Looks right to me.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Ah, now what is weird is that I can change a number to the same value as that below it and the ranking doesn’t change. But if I re-enter the number below they get the same rank.
Maybe the character set? What's the source of the data?
Selecting the whole range of numbers and using Text to Columns with no separators also seems to work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks