Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign a macro to Command Button Object | Excel Discussion (Misc queries) | |||
"Drop the lowest" in computing average | Excel Worksheet Functions | |||
sports score sheet | Excel Discussion (Misc queries) | |||
Assign values to names in a drop-down list? | Excel Discussion (Misc queries) | |||
Assign to macro missing | Excel Discussion (Misc queries) |