Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My Excel skills are a little rusty and I was trying to assign scores in a table based on the value of 3 variables (Var) in Excel 2010. I put an example below. The idea is the higher score of Var 1 and Var 2 are better. I also want to add a third value which is an alphanumeric and they are different but essentially of equal value. Here is a table and in the last 2 columns I have put in scores that I would assign the value in this table. If the values don't fall into the assigned ranges I would assign an N/A. Any help anyone can give me would be greatly appreciated in coming up with a formula. Thanks! -- Steve

For example here are 3 scenarios and the score I would want to come out.

Var 1 = 108, Var 2 = 0.5, Var3 = Red, Score = 3
Var 1 = 117, Var 2 = 1.7, Var 3 = Yellow, Score =  8
Var 1 = 98, Var 2 = 2.0, Var 3 = Red, Score = N/A

                                                                Scores  Scores
Var 1 >=    Var 1 <     Var 2 >=    Var 2 <       Var 3        Red      Yellow

100         105         1           2           Red or  Yellow  1       2
105         110         0           1           Red or  Yellow  3       4
110         115         1           2           Red or  Yellow  5       6
115         120         1           2           Red or  Yellow  7       8
100         105         2           3           Red or  Yellow  9       10
105         110         2           3           Red or  Yellow  11      12
share|improve this question
add comment

1 Answer

Combine the columns Var 1 < and Var 2 < such as =B2&D2 for the demo, I overwrote the Var3 column, as it's not needed for this, as the Var3 in the vlookup will define which score column to use.

sort this new column to be in ascending order

New Spreadsheet:

Var1 >=|Var 1 < |Var 2 >= |Var 2 < |Var 3 |Red |Yellow  
100     105      1            2     1052    1   2  
100     105      2            3     1053    9   10  
105     110      0            1     1101    3   4  
105     110      2            3     1103    11  12  
110     115      1            2     1152    5   6  
115     120      1            2     1202    7   8  

now use vlookup on your result
=VLOOKUP(VAR1&VAR2,$E$2:$G$20,if(Var3='Red',2,3),TRUE)

you will have to create a no-score value, as it will look for the largest value above the scores), and your 3rd example with the data given would find the first value in the table

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.