I have put together a spreadsheet for tracking performance I am trying to
figure out how to construct a formula that will look at ranges within several
cells and return a value based on an entry in another cell.


Objectives 5 6 Weight Raw Score Points
Budget 75%-79% 80%-84% 20 82% 120

Looking at info above, I enter raw score of 82%. I want the formula to
determine that 82% falls within the range for the score of "6". I then want
the formula to multiple that "6" times the weight of "20" and give me total
points of "120". This is just a slice from the bigger spreadsheet. The want
to be able to protect the sheet with the only cell for data entry is Raw
Score. I know how to do this. I just don't know how to write the formula
that will do the rest.

create a table that has the upper limit (percent) for each category in the
first column and points in the second. You can then use the vlookup
function to return percent converted to points.

vlookup(%,table,2,True)

see help for details.

--
Regards,
Tom Ogilvy


"mmcintire" wrote in message
...
I have put together a spreadsheet for tracking performance I am trying to
figure out how to construct a formula that will look at ranges within
several
cells and return a value based on an entry in another cell.


Objectives 5 6 Weight Raw Score Points
Budget 75%-79% 80%-84% 20 82% 120

Looking at info above, I enter raw score of 82%. I want the formula to
determine that 82% falls within the range for the score of "6". I then
want
the formula to multiple that "6" times the weight of "20" and give me
total
points of "120". This is just a slice from the bigger spreadsheet. The
want
to be able to protect the sheet with the only cell for data entry is Raw
Score. I know how to do this. I just don't know how to write the formula
that will do the rest.