How to Convert "Yes" and "No"into values

[JimCWalker]

I want to develop a spread sheet in excel that has some questions that can be answered "yes" or "no". I then want to be able to convert these to 1 and 0 respectively so I can use them in some formulas. I realize that I could just tell the users to use 1 for yes and 0 for no but I was hoping that there was a more "elegent" way to do this.

Jim

 

[smozgur]

On 2002-08-24 13:26, JimCWalker wrote:
Tried it. I get a #VALUE error. Tried it without the quotes and got a #NAME error.

Here is what I put in.
=IF(A1="Yes","1"),IF(A1="No","0")

Am I missing something?

Thanks

Jim

Yes, you are. You are not writting the formula correctly.

=IF(A1="yes","1",IF(A1="no","0",""))

as Brian said. Just copy and paste the formula.

Suat

 

[JimCWalker]

Thanks. I got it now.

Jim

 

[JayMenon]

Hi Jim :

Not getting you quite clear. If you are just looking for converting Yes or No into 1 and 0, trying using Data|/Validation to force the user to respond to it so. For example :

[1] Key in Yes, No in C1, C2
[2] Choose range say, B5:B15
[3] Data|Validation, pick List and highlight C1:C2
[4] Key-in in C5 =IF(B5="Yes",1,0), drag the cell handle down to C15
[5] Now you have user response either as 1 or 0

Does it solve ? Your comments !!

Thanks

 

[Brian from Maui]

On 2002-08-24 18:09, JimCWalker wrote:
I tried the add-in download and it works well. I still have a question. Is there some way that rather than a sum I can simply get it to subtract "yes" from "no" or visa versa.

Thanks



=COUNTIF(A1:A5,"=yes")-COUNTIF(A1:A5,"=no")

Jim

Jim,

Try this:

ABS(COUNTIF(A1:A5,"=yes")-COUNTIF

(A1:A5,"=no")), where it's always a positive value or this:

=COUNTIF(A1:A5,"=yes")-COUNTIF(A1:A5,"=no") if it doesn't matter.

However, if you have a large range, it's going to be hard to identify the sum as being more "yes" or more "no". If that being the case, you need to use two cells with one having the COUNTIF for "yes" the other for "no".
This message was edited by Brian from Maui on 2002-08-24 23:24

 

[Aladin Akyurek]

On 2002-08-24 22:51, Brian from Maui wrote:

On 2002-08-24 18:09, JimCWalker wrote:
I tried the add-in download and it works well. I still have a question. Is there some way that rather than a sum I can simply get it to subtract "yes" from "no" or visa versa.

Thanks



=COUNTIF(A1:A5,"=yes")-COUNTIF(A1:A5,"=no")

Jim

Jim,

Try this:

ABS(COUNTIF(A1:A5,"=yes")-COUNTIF

(A1:A5,"=no")), where it's always a positive value or this:

=COUNTIF(A1:A5,"=yes")-COUNTIF(A1:A5,"=no") if it doesn't matter.

However, if you have a large range, it's going to be hard to identify the sum as being more "yes" or more "no". If that being the case, you need to use two cells with one having the COUNTIF for "yes" the other for "no".
This message was edited by Brian from Maui on 2002-08-24 23:24

Also:

=SUM(EVAL(A1:A5))-COUNTIF(A1:A5,"No")

When the result is > 0, there is more of Yes. When the result< 0, there is more of No.