• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

simple formula for weighted scoring

H

Hellomoto

New Member
I created a table consisting of 5 questions (columns B,C,D,E,F) x number of survey responders (estimated to be 100+ rows). Each cell will be validated so that each respondent will select from the following: No, yes, maybe, and N/A.


Each response is weighted, example: No= -3, yes=+3, maybe=+1, N/A=0. (in a separate DATA table two columns one for the responses and one for the weights)


Column G will be the overall score for each respondent (sum of columns B:F). Each row will have conditional format to highlight respondents that yielded a high score from their responses to each question. Example, highlight row Green if sum > 10.


The formula I am using now is a SUM of IF functions. Each IF compares the cell value to the value from the DATA table and returns to appropriate value. There must be a simpler formula. Similar to weighted averages but I'm not looking for an average.


So finally, the question is, does excel have a function that allows the assigning of scores to predetermined text? So that I can simply sum(B2:F2)


Thanks in advance
 
You could use this array formula:

=SUM(LOOKUP(B2:F2,DATA!$A$2:$B$5))


Notes: The Lookup table on your DATA sheet needs to be sorted ascending by col A like so:

Maybe

N/A

No

Yes


To confirm an array formula, hold Ctrl+Shift, and then hit Enter (as opposed to just using Enter).
 
Thanks Luke,


I didn't know that the first argument in lookup can be a range... always learning something new at chandoo.org. I spent hours coming up with creative ways to use sumproduct and other functions, all worked but not elegant. Should've posted this question sooner. Again, thanks Luke.
 
Hi Luke, it didn't work for me in the first place until I see the "{" and "}" sign after hitting F2 key in the cell, then crtl+shift+enter.


why didn't

=sum(lookup(b2:f2,data!$A$2:$B$5)) work but


{=sum(lookup(b2:f2,data!$A$2:$B$5))} work? does it have to do with Excel 2007?


In what condition should I use {} in a formula?
 
@Fred

The {} indicates that the formula is an Array Formula as opposed to a normal formula.

Array Formulas are evaluated for every member of the array or Range which is how the above formula works for multiple values in the start of the lookup.

Array formulas are normally constructed to do things where you want the same operation to occur in every cell of a range and mostly give a different or incorrect answer if not entered as an Array Formula
 
You must log in or register to reply here.
Back
Top