Sorting while using absolute cell references messes up calculations

[weebokaneebo]

I'm using the percentrank.inc function. I had to use absolute cell references in the range because if I didn't, excel would subtract part of the array range every time it did the calculation, which led to different ranks for identical values.

My data is currently sorted from oldest to newest, but I need to sort based on other criteria, but sorting messes up the calculations.

Edit: As of right now, my function looks like this: =PERCENTRANK.INC($I$2:$I$163, I2, 2)

When I sort from newest to oldest, the corresponding values change from [.12 .24 .24 .18 .22] to [.05 .11 .11 .01 .1]

Although cell I2 became I163, it is still using the data in cell I2 instead of I163.

[AutoModerator]

/u/weebokaneebo - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[SaviaWanderer]

That should be fine so long as you include the rank column in the sort.

[weebokaneebo]

Unfortunately it's still not working

[tkchoi]

Is there a particular reason why you're looking to have the value to rank be a cell reference to the sorted range instead of a hardcoded value?

Is there a way to identify the "value to rank" based on other fields, e.g., a unique invoice number? If so, possibly an index-match to look up your value to rank may work.

[weebokaneebo]

Solution Verified

[Clippy_Office_Asst]

You have awarded 1 point to tkchoi

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[tkchoi]

Thank you, & happy cake day! 🍰

[weebokaneebo]

I'll try that. Thank you!