How to assign a score, dependant on a sum falling within a range?
 

Hi there, i'm currently setting up a questionnaire within which i want to
assign a skin type dependant on the answers to specific questions.

Basically the way i want it to work is to assign values per question to give
a SUM at the end eg 21.

Then i want excel to look up 21 in a table and see that if the value falls
between 20 and 25 then a value of 2 is assigned to skin type. Or if the value
is between 26-35, a value of 3 is assigned etc etc

Which command is used to do this?

Ok, your mapping table should have the score in the left most column and the
corresponding value on the right. Apply the starting value only against each
score, and make sure that it is sorted in ascending order by the score.

You would apply a vlookup around your e.g. 21. =VLOOKUP(21,Sheet2!A1:B10,2,1)

Whe Sheet2!A1:B10 is your mapping table, A1:A10 hosts the score and
B1:B10 the correspinding value.

Note: The e.g. 21 could actually be the formula that calculates the e.g. 21.

The ,2, specifies the column index to return from the mapping table, where
(A) is 1 and (B) is 2.

Regards,
A

"andythescientist" wrote:

Hi there, i'm currently setting up a questionnaire within which i want to
assign a skin type dependant on the answers to specific questions.

Basically the way i want it to work is to assign values per question to give
a SUM at the end eg 21.

Then i want excel to look up 21 in a table and see that if the value falls
between 20 and 25 then a value of 2 is assigned to skin type. Or if the value
is between 26-35, a value of 3 is assigned etc etc

Which command is used to do this?

VLookup(total_cell,table_name,2,True)

where table was a two column list of low-score and scale (ie, 20 and 2
then 26 and 3 etc), the table must be sorted and can either be named
(my preference) or a range stated as:
VLookup(total_cell,Y1:Z20,2,True)


andythescientist Wrote:
Hi there, i'm currently setting up a questionnaire within which i want
to
assign a skin type dependant on the answers to specific questions.

Basically the way i want it to work is to assign values per question to
give
a SUM at the end eg 21.

Then i want excel to look up 21 in a table and see that if the value
falls
between 20 and 25 then a value of 2 is assigned to skin type. Or if the
value
is between 26-35, a value of 3 is assigned etc etc

Which command is used to do this?


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=393252