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

JimCWalker

New Member
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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
EDIT/REPLACE to replace YES by 1 and NO by 0

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

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

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

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.

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

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
ABCD
1yesyesyesno
2no
31
40.75
54
64
73
8
Sheet2

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

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

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

Thanks to you both. I will try it.

Jim

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

Replies
7
Views
360
Replies
5
Views
160
Replies
5
Views
131
Replies
1
Views
246
Replies
10
Views
267

1,219,687
Messages
6,149,716
Members
450,910
Latest member
DianeG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back