Gradual scoring based on value in a cell

**Sjand** · 05-16-2012, 03:38 PM

Hi,

I need help creating a formula for the following scenario. I need to assign a gradual decreasing score by 1 starting at 25 for any Make busy %age attained >12%. So for Agents with > 12%MKB,first ranked gets 25 points and there on 1 point deducted for each lower rank. Decrease by 1 point until 0 points is reached. See Example below.
MKB% Score
13 25
14 24
15 23
16 22
17 21
18 20
19 19
20 18
21 17
22 16
23 15

Thanks for the help.

**JBeaucaire** · 05-16-2012, 04:18 PM

Assuming that table is laid out in columns A:B, and you enter a value in cell D1, in E1 this formula would "score" the value you entered in D1:

=VLOOKUP(D1, A:B, 2, 1)

Press F1 and read up on VLookup

**Sjand** · 05-17-2012, 08:33 AM

Thanks Jerry! instead of Vlookup, I was looking more for some kind of IF formulas. The data in columns is just to show the example and not the real data. The actual MKB% could be any number (with decimals points). So MKB% acheived above 12% needs to be assigned a score starting from 25 scores for first above target MKB% and scores gardually decreasing by 1 point there on till 0 score is acheived (which would be with 38% or above MKB%). I hope I explained it clearly this time.

Thanks for the help!!

**JBeaucaire** · 05-17-2012, 12:28 PM

VLOOKUP is still the correct choice. I set the final parameter to 1 in that formula so that "match" in the vlookup is fuzzy, taking into account all the numbers above the base at each tier. You really need to read up on VLOOKUP including the TRUE (1) option at the end.

Post a sample workbook if you want help making it work. Be sure to setup an ACTUAL reference table showing what the scores are at each specific MKB% like I explained originally.