Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nesting formula (MS Office Professional)

    Hi All:
    I need to develop a formula to convert text into a number. This is the formula I need in Laymans terms if someone could convert to Excel speak that would be much appreciated.

    IF B2 = "Strongly Agree" then B3 = 5, If B2 = "agree" then B3 = 4, If B2 = "Neutral" then B3 = 3, If B2 = "disagree" then B3 = 2, If B2 = "Strongly Disagree" then B3 = 1

    Thank you.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nesting formula (MS Office Professional)

    Welcome to Woody's Lounge!

    Place this formula in B3:

    =IF(B2="strongly disagree",5,IF(B2="agree",4,IF(B2="neutral",3,IF(B 2="disagree",2,IF(B2="strongly disagree",1,"")))))

    or a shorter version

    =MATCH(B2,{"strongly disagree";"disagree";"neutral";"agree";"strongly agree"},0)

    or you can create a small table:

    <table border=1><td></td><td align=center>D</td><td align=center>E</td><td align=center>9</td><td>strongly agree</td><td align=right>5</td><td align=center>10</td><td>neutral</td><td align=right>3</td><td align=center>11</td><td>agree</td><td align=right>4</td><td align=center>12</td><td>disagree</td><td align=right>2</td><td align=center>13</td><td>strongly disagree</td><td align=right>1</td></table>
    and use this formula:

    =VLOOKUP(B2,D9:E13,2,FALSE)

  3. #3
    New Lounger
    Join Date
    Oct 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nesting formula (MS Office Professional)

    Hans,
    Thank you for the reply. Unfortunately I cannot get any of the three formulas to work. The first formula leaves the cell blank and the 2nd and 3rd formulas put a #N/A in the cell. The version is Office XP I thought it was office professional but could that be the problem?? I checked the Capitalization to ensure it was correct.
    I have tried horizontal as well as vertical. IE. =IF(F2="strongly disagree",5,IF(F2="agree",4,IF(F2="neutral",3,IF(F 2="disagree",2,IF(F2="strongly disagree",1,""))))) Placed in the G2 cell. Excel recommended the addition of the 4 closed parens at the end.

    I am trying to convert survey response data into a numerical representation. Any further advice would be greatly appreciated.

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nesting formula (MS Office Professional)

    Have you got any redundant spaces after the text?

    An IF statement will return FALSE when comparing "strongly agree " (with a trailing space) to "strongly agree" (without a trailing space).

    stuck

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nesting formula (MS Office Professional)

    I have attached a simple workbook that demonstrates the formulas.

    By the way, I had forgotten the 4 closing parentheses; I have corrected my earlier reply. Sorry about that.

Posting Permissions

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