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

 

[orangoetang]

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

 

[JimCWalker]

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

 

[Brian from Maui]

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

 

[JimCWalker]

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

 

[Aladin Akyurek]

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

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

0

( 7.) Click OK.
( 8.) Download the morefunc add-in from

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

and install/activate the add-in via Tools|Add-Ins.

If your users cannot somehow install this add-in, then add the following code to your WB:

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

aaSymsInFormulas JimCWalker.xls
	A	B	C	D
1	yes	yes	yes	no
2	no
3	1
4	0.75
5	4
6	4
7	3
8
Sheet2

This message was edited by Aladin Akyurek on 2002-08-24 13:37

 

[JimCWalker]

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

 

[Brian from Maui]

Just for the record, you're not copying the formula correctly. But follow Aladin's advice.

 

[JimCWalker]

Thanks to you both. I will try it.

Jim

 

[JimCWalker]

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