Back to Forms in Excel VBA archive index

Back to archive home

Help!

I am (trying) to use the Vlookup function to return values for a scorecard application. The scorecard compares actuals against a 1-5 rating scale. The problem is that it works fine for those metrics that are in ascending order (i.e. 10 = 1 15 = 2 20 = 3 etc.) BUT for thosse metrics with desending order (i.e. 5 = 1.95; 4 = 2.0; 3 = 2.05; 2 = 2.1; 1 = 2.15) I can not get a correct value.

For example in the following range

Value Rating

79.96 4

84.05 3

I would want a rating of 3 to be returned for a score of 80.0 as a 4 is not earned until the actual value FALLS to 79.96.

I know WHY this is, I was just hopeful that someone here has encountered the same "opportunity" and resolved it, either with a trick with vlookup or some other function.

Any help MOST welcome!

PS: Please post answer here - my email is down!

Check out our Excel Resources | ||||

Hi Rick

Care to provide the table of values and ratings with descending order. I assume that you don't have problems with ratings that are in an ascending order. I also assume that you have 2 separate tables and 2 separate series of scores for which you want to lookup the ratings.

Aladin

RICK, I think you were talking about ascending and descending to better expose your problem. I believe you have just descending rates. So I will use the eample you used (instead of, alas, your actual rating-values).

Your example of scores and ratings: {1.95,5; 2.0,4; 2.05,3; 2.1,2; 2.15,1}

You want to assign 5 for 1.98 ... If so, I propose rewriting this ratings table as follows:

Enter in A from A1 on:

0

2

2.05

2.1

2.15

Enter in B from B1 on:

2

2.05

2.1

2.15

100 [ This is an arbitrarily big score ]

Enter in C from C1 on:

5

4

3

2

1

Name the range A1:C5 RATINGS via the Name Box.

I'll assume actual scores are in D from D1 on:

In E1 enter: =IF(AND(D1>=VLOOKUP(D1,RATINGS,1),D1<VLOOKUP(D1,RATINGS,2)),VLOOKUP(D1,RATINGS,3),"") [ Copy down as far as needed ]

Recall that RATINGS is the range $A$1:$C$5.

I reckon you can rebuild your table the way that the above method requires.

Note. This formula is copy-righted.

Is this what you are looking for?

Aladin

That pesky script is messing things up when use a less-than symbol. You want to assign 5 for 1.98 ... If so, I propose rewriting this ratings table as follows: 2 2.05 2.1 2.15 2.05 2.1 2.15 100 [ This is an arbitrarily big score ] 4 3 2 1

Substitute the appropriate symbol for LessThan.

G'Morning Aladin!

Thanks for the reply. I have not had a chance to look closely at your script yet, but will later in the AM. (Groan....I have a 2 hr meeting starting in aprox. 35 seconds!)

Thx!

Rick

G'Afternoon Aladin,

Whew! Another long meeting with TONS of action items. Oh well.....

I had a chance to review your suggestion and wanted to thank you for your time and effort. Indeed, this will work. If fact, it is very close, but much clearner, than a solution that I came up with last night after taking a brief break. What I did was use the AND statement in five different cells titled 1 - 5 combined with an IF comparing the result of the AND to TRUE. Where TRUE existed I set it to the value of the column heading (1,2,3,4 or 5) where it was FALSE I set the value to 0. I then summed across the colunns. Of course I would only get one value and so the sum would be my score of either 1,2,3,4 or 5.

Messy, but it worked! Yours needless to say is more efficient.

By the way, I also had to compute a "fractional value" to add to the rating, i.e. in the range of values between a 3 and a 4 was the rating a 3.1, 3.01, 3.47? etc. To do this I expanded the vlookup tables to include the variance between the ranges, for example the variance between the start of rating 4 and rating 3 in your example is .05, looked up the variance for the awarded rating and computed the variance between the actual value and the start of the next rating and divided the result. This gave me the fraction to add to the whole number rating.

I had to go to through the above, because for !35! metrics that I set up the variance between the ratings was not always the same. In fact there wasn't even the same variance between the RATINGS within a metric most of the time!

Well, again thanks for your help! IF in the future I have to either update this spreadsheet OF encounter this issue again I will look to your's as the better solution!

Thx!

Rick

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.