Rank Hypothetical value against list of actual values

[idahoboi]

Greetings Mr. Excel forum!

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)

 

[Gerald Higgins]

I think for RANK() to work, the value in C7 has to exist in the range F10:F125.

Think about it this way.
Let's say you had a very small array, of the numbers 10, 11, and 12.

You could use the RANK function to determine the position of any single one of those three numbers within that array.

But it just wouldn't make sense to ask for the rank of numbers such as 8, 15, or 10.5.

If you want to force the function to return something other than an error, you could use various error handling techniques.

For example, you could use something like this

=if(iserror(vlookup(c$7,'coaches report'!f$10:f$125,1,0)),"Invalid value for C7",RANK(C$7,'Coaches Report'!F$10:F$125,0))

Alternatively, maybe RANK() isn't the way to go here. There may be other solutions which kind of give you what you want, such as reporting on how close C7 is to the maximum or minimum value.

 

[idahoboi]

Thanks for your reply Gerald! Unfortunately, that won't really work for what my leaders are looking for. they want the ability to punch in a number and see how that might improve the agent's rank compared to the other individual performance. While the rank formula may not be able to accomodate this, can you think of any other options that might work? I'm open to any suggestions.

Thanks!

 

[idahoboi]

I think I found a workaround! If I add a vlookup to the source data that pulls the hypothetical data into the source table, well below the rows that are ranked on the main report, the hypothetical ranking works. I just had to extend the formula to look at the cells in row 400. The source data only goes to row 287.

New formulas are as follows:

Coaches Report Tab:
=VLOOKUP(B400,'What-If Analysis'!A7:S7,3)

What-If Analysis Tab:
=RANK(C$7,'Coaches Report'!F$10:F$400,0)