Rank Value of Column Based on Current Date (Week)

I'm setting up a weight loss tracking sheet and in Column A I'd like to indicate the Rank of a user as the weeks progress. With that being said, I only want the rank to reflect the current week and used the WEEKNUM() and TODAY() functions to try and set this up properly.

I have two issues:

1) My Rank formula is just cluttered -- there has to be a better way of doing this than nesting so many IF statements.

Please Login or Register  to view this content.

2) Because of the New Year transition and my use of WEEKNUM(), 01/01/2021 - 01/02/2021 are in a bit of a limbo -- it's not the end of the world, but during those two days the rank function will not display.

https://docs.google.com/spreadsheets...it?usp=sharing

Try in A5 and copy down:

Formula:

Please Login or Register  to view this content.

Good luck!

Sorry for the delayed response. It appears to work exactly as I'd hoped. I'm a little embarrassed to admit that I'm not fully understanding WHY it's working. In any case, thank you very much again.