Excel 2007: Applying a value to a rating scale number

[cassique]

Can I do this in Excel?

What I want to do is take a rating value I have in row "D" (0-5) and convert each number to a specific value (0=1290, 1=1550, etc). I have these values stored in cells "I9-I14". Then in Column "L" I want to have a formula that uses the value (ex. "(c1-D1)/3500"). In that example D1 has the rating of 2, but in the formula I want that to mean 1550. Is this possible?

The values of the rating scale might change over time as I collect more data to make it more accurate to my specific need. So I would rather use a rating scale number in the formula so if I have to change the value it will adjust without me having to go into each cell to change it.

 

[Michael M]

Not 100% sure I follow but...
If you have a value in C1 and your 0-5 in D1
use the numbers from I9 - I14 in this formula...you'll need to replace the numbers I put in

=(C1-LOOKUP(D1,{0,1,2,3,4,5},{1290,1550,1875,2000,2100,2250})/3500)

 

[Michael M]

If you want the values in the LOOKUP range in I9-I14 to be changeable....create a Named Range out of those cells....let's call the Named Range "jim"
Then change the formula to

=(C1-LOOKUP(D1,{0,1,2,3,4,5},jim))/3500)

 

[cassique]

Not 100% sure I follow but...
If you have a value in C1 and your 0-5 in D1
use the numbers from I9 - I14 in this formula...you'll need to replace the numbers I put in

=(C1-LOOKUP(D1,{0,1,2,3,4,5},{1290,1550,1875,2000,2100,2250})/3500)

Thanks. That sort of helped. For some reason I didn't get the right answer but I am playing around with it now and found out about using arrays and LOOKUP functions. Lot's to learn.

 

[Michael M]

Ok, are your D1 values ranging from zero to 5
Thes 2 ranges inside the curly brackets have to be the same length, ie, 5 in one and 5 in the other
AND
they have to exactly match the LOOKUP array, eg, the value in D1 must be exactly 1 or exactly 5.......not 1.3 or 1.5 or 5.2....etc !