Would you have any chance of getting the rank position like this as shown in the image? obeying the order from largest to smallest and repeating positions if there are repeated values?
Add a comment
|
1 Answer
I believe this is what your asking for:
=SUMPRODUCT((B65<=B$65:B$83)/COUNTIF(B$65:B$B83,B$65:B$83))
This will continuously count rank starting from 1. I attempted to adjust the ranges, manually, to match your sheet. Please update if needed.
Original source: https://www.xelplus.com/excel-rank-without-skipping-numbers/
-
Yes! That was the purpose. I had tried to make so many formulas and I couldn't solve it. But now it worked! Did you create this formula yourself? or it was from another user, what would be its source? Commented Mar 19, 2020 at 18:19
-
1I used it in one of my files and have been copying and editing. Was from a website initially will credit when back on work computer and I find original source.– Ed BCommented Mar 20, 2020 at 5:03
-
Interesting she, I debugged the formula to understand how it works and she performs an initial calculation by entering "1" for each result in true and then dividing by the counted amount of values in each line that matches the "true" condition Commented Mar 20, 2020 at 14:16
-
When true, the division is granted and the result of it is "0.5" for each result in true that he finds, after that it realizes a complete sum of all the values that were "0.5", giving the total, greater or equal to "1" depending on the counted quantity Commented Mar 20, 2020 at 14:19
-
1I have updated the answer with the source that I used. I hope this helps. I do like the formula there are other ways to do it per the internet but this one is so clean in my opinion. It also breaks down the formula and how it works.– Ed BCommented Mar 20, 2020 at 16:55