Assigning unique values to "yes" or "no"

**herosol** · 10-14-2014, 01:12 PM

Hi first post here. I am having trouble coming up with a good function for my needs.

I have survey data where every 5th column (ie. 1E, 1J, 1O, etc) there is a "yes" or "no." I want to sum each row for these such as 1E+1J+1O BUT, "yes" in column 1E has a different value then a "yes" for 1J.

How would I go about adding up these columns, but the "yes" generate a unique value. For example if a yes in 1E = 1.5 and a yes in 1J = .25, my formula would generate 1.75 by simply using "yes" as reference?

Any help is appreciated

**Jonmo1** · 10-14-2014, 01:16 PM

How do you identify the different values of each yes?

**Speshul** · 10-14-2014, 01:20 PM

First of all, welcome to the forum.

You should get in the habbit of stating cell references in Column/Row format, for example A6, of F12. This is the format Excel uses, as well as anyone who works within excel

Without seeing the data, you could try something like this

=SUM(IF(E1="yes",1.5,0),IF(J1="yes",0.25,0),IF(O1="yes",0.1,0))

Red is the values to 'assign', you didn't specify what a value in O1 would be so I guessed .1 If you want to make a value for No as well, just change the Zeros right after the Red text within each IF statement.

To break that down a bit,

=SUM( IF(E1="yes",1.5,0) , IF(J1="yes",0.25,0) , IF(O1="yes",0.1,0) )

I am getting a SUM. =SUM will sum up all of the values placed in it, separated by commas. In this case I have three different IF formulas seperated by commas

IF(E1="yes",1.5,0)

IF(J1="yes",0.25,0)

IF(O1="yes",0.1,0)

So, if it is yes yes no, the SUM formula see the following, =SUM(1.5,.25,0) which totals 1.75

IF function works by if(logical_test, value_if_true, value_if_false)

Hope this helps.