Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

This is a discussion on How to Convert "Yes" and "No"into values within the Excel Questions forums, part of the Question Forums category; I want to develop a spread sheet in excel that has some questions that can be answered "yes" or "no". ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    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. #2
    New Member
    Join Date
    Aug 2002
    Posts
    2

    Default

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

  3. #3
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    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. #4
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,186

    Default

    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. #5
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    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. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,415

    Default

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


    ******** ******************** ************************************************************************>
    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
    =

    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. #7
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    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

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,186

    Default

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

  9. #9
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    Thanks to you both. I will try it.

    Jim

  10. #10
    New Member
    Join Date
    Aug 2002
    Posts
    7

    Default

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com