A Complicated Look up Function?

Good Morning,

I've been asked to build a "corporate scorecard" type calculator. This scorecard has various areas for you to put values into, (i.e. 8.3 out of 10 for "plays well with others"), and when the score for this area is entered it must return a pre-defined value for the weighted score. This sound complicated to simplify:

The company has provided me tables for what the values returned to be. I.E if the score is above 5.2 return a value of 20, above 6.1 return a value of 23, and so on and so forth. This information is housed in the spreadsheet in cells A18:B39. "A" column being the breakpoint value and "B" Column being the value to be returned.

so if someone's raw score for one category 5.5 it will go to the table, find the largest value that the score exceeds (in this case 5.2), and then return the corresponding value (20). I know this can be done with a chain of IF() functions but these tables range from 25 to 100 breakpoints making it plausible but not realistic.

Any Ideas?


Thank you for your time and consideartion.

-YaK

Try something like:

Please Login or Register  to view this content.

Cheers,