# Thread: How to Convert "Yes" and "No"into values

1. 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

2. EDIT/REPLACE to replace YES by 1 and NO by 0

3. thanks but that won't solve my problem. I want to send out a spreadsheet with formulas embeded in it so that when people answer certain questions (most of the answers will be numberical but unfortunately a couple are yes/no) it automatically calculates some related values. I can't figure out how to build the formulas for the ones that will have yes or no text answers.

Jim

4. In another column

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

Just curious, what other formula are you using the "1" and "0"?

[ This Message was edited by: Brian from Maui on 2002-08-24 13:17 ]

5. 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

6. On 2002-08-24 12:41, JimCWalker wrote:
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
( 1.) Activate Insert|Name|Define.
( 2.) Enter Yes in the Names in Workbook box.
( 3.) Enter the following in the Refers to box:

1

( 5.) Enter No in the Names in Workbook box.
( 6.) Enter the following in the Refers to box:

0

( 7.) Click OK.

http://longre.free.fr/english/index.html

Function Eval(Arg As String) As Variant
Eval = Evaluate(Arg)
End Function

I believe the EVAL from morefunc to be faster...

The foregoing makes it possible that you can apply formulas to ranges housing a bunch of Yes and/or No like in:

=SUM(EVAL(A1:A2))

=AVERAGE(EVAL(A1:D1))

=COUNT(EVAL(A1:D1))

but also...

=COUNTA(A1:D1)

=COUNTIF(A1:D1,"yes")

See the figure...

******** ******************** ************************************************************************>
 Microsoft Excel - aaSymsInFormulas JimCWalker.xls ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A3A4A5A6A7 =

A
B
C
D
1
yesyesyesno
2
no
3
1
4
0.75
5
4
6
4
7
3
8
 Sheet2

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[ This Message was edited by: Aladin Akyurek on 2002-08-24 13:37 ]

7. I am still trying to figure this out but if I can get your suggestion to work I may just add a hidden column that converts the text and then use those values to do a simple calculation.

In this case, I need to compare the value in two different columns and then multiply it by the value in another cell. The result might be something like (+c23-c26)*a2.

Thanks

Jim

9. Thanks to you both. I will try it.

Jim

10. 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

Jim

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•