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))

 

[philwojo]

What do you want to happen if it is N/A, your If is only testing and looking for YES, if it doesn't find that, which means anything else is present, then it gives a zero.

So you need to change your formula to test for both Yes, No and N/a, and then tell it what to do for each case.

 

[pwrisley]

I'd like the N/A to not even be included in the calc? Put another way, I'd like the N/A to equal "nothing" I guess. Does that make sense?

 

[philwojo]

Sorry, let me repost, at work, trying to get this right, what I posted is off.

 

[philwojo]

Ok, try this out:

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

 

[pwrisley]

Unfortunately, it is the same result. The N/A option is still equating to 0 and impacting the score.

 

[philwojo]

Can you copy and paste the formula you have in your sheet as well sa the values from A4 and F4 in your sheet.

Based on what I understand of your description the formula I posted show look at A4 and if it is "Yes" it will multiple the left 2 numbers in F4 by 1.

If A4= "No" it will essentially give you a zero (0) as you said to multiple the left 2 numbers of F4 by zero.

If there is anything else in A4 it should come back with a blank cell, "".

I'm not sure why you would be getting a zero for "N/A" being in the field.

Are you sure you have "N/A" and A4 doesn't have an #N/A in it, as that is different than "N/A" being typed in A4.

 

[pwrisley]

Formula in H4 is =IF(A4="Yes",1,0)*NUMBERVALUE(LEFT(F4,2))

Values in A4 are the data validation responses of YES, NO and N/A (not #N/A).

Value in F4 contains 4 options via data validation. I realize now that is crucial to the question so my apologies. In F4, there are the following 4 options...
4 - Critical
3 - High
2 - Medium
1 - Low

Sorry for the confusion.


 

[philwojo]

Well you don't have the formula I put out in post #5 in H4, so have you tried that to see if it works?

If you haven't tried it I would recommend starting there. If you have tried it then I am not sure why it wouldn't work, but based on my testing it should, follow-up with us and let us know, we can go from there once you try out the other formula.

Phil

 

[JPARKHURST]

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))

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

Finally, is f4 stable, or does the value change with the line (A4,.F4; A5,F5; A6,F6) OR (A4,F4; A5,F4; A6,F4)

If not, does this work for you:

=IF(A6="yes",1*NUMBERVALUE(F6),IF(A6="no",SUM(2*NUMBERVALUE((F6))),))