N/A conditional field exclusion from simple formula?

[pwrisley]

What seems simple, is driving me crazy . In short, cell A4 contains conditional survey responses of YES, NO, N/A and the below formula then multiplies a weighting from cell F4 to compute a raw score in the cell. My problem is when N/A is selected in the dropdown in A4, it is treating it as a 0. Is there a way to NOT include N/A at all and only assign the typical Yes a 1 and No a 0? Sorry for the newbie question, learning is a process, ha-ha.

=IF(A4="Yes",1,0)*NUMBERVALUE(LEFT(F4,2))

 

[JPARKHURST]

So, just to double check, are you multiplying a 'no' by 0? If so, then wouldn't you just need to check for yes?

Obviously I'm a little slow on the draw! Looks like you've got some good help going.

Jon

 

[pwrisley]

When I insert the formula in the above post #5 in H4, it leaves the cell blank, but it is still computing as if it is a NO response in the math/formula? I need a N/A response in A4 to not equal 1 or 0, essentially, I need an N/A response in A4 to equal nothing if that is possible.

 

[pwrisley]

I found a solution I believe.... I input a "1" in cell A39 and directed a nested IF statement to look at that cell when N/A is selected in the data validation in cell A4. It seems to work for what I need. Thank you so much for helping, your direction certainly helped with finding a way around the issue. Here is the formula in H4 that works for me and I can copy and paste down from there and the calc works the way I need.

=IF(A4="Yes",1,IF(A4="No",0,IF(A4="n/a",$A$39,0)))*NUMBERVALUE(LEFT(F4,2))

 

[JPARKHURST]

Just a logic note:

For a computer, a 0 is a nothing, so in reading "n/a" as a 0 mathematically, it is doing what you are asking.

 

[Aladin Akyurek]

H4:

=(A4="Yes")*LEFT(F4)

 

[JPARKHURST]

H4:

=(A4="Yes")*LEFT(F4)

That's kinda what i was thinking, Occams.