I'm pretty good with excel and formulas, but I'm having some issues getting a simple rank formula to work on hypothetical data. I have a workbook that contains a list of several team members, ranked by their various levels of performance on one tab. On another tab, I have a "What-If" analysis table built, where a leader could input a hypothetical value for any one of the measured metrics, and be able to see where that agent's rank would be if the performance matched the hypothetical value entered. Essentailly, this will allow for a what-if analysis ranking, helping to show where a person would be if their metrics were better.

The problem I'm running into is that if I use a value less than or greater than any other values already in the list, I get a #N/A error. I also get the same error if I enter a number between other numbers on the existing list. For instance, if the list has a min of 50% and a max of 100%, and I enter 45% into the hypothetical table, it returns the #N/A error instead of ranking it in last place. Similarly, the list containes the value 99.38% for one agent and another shows 99.5%, if I enter 99.4% into the hypothetical rank table, I get the #N/A error (presumably due to the fact that the value does not exist in the values list). How can I get around this error while still allowing the leader to input any value into cell C7 and have it ranked against the values in the factual list ('Coaches Report'!F10:F125)?

My formula so far is as follows:

=RANK(C$7,'Coaches Report'!F$10:F$125,0)