# Thread: Nesting formula (MS Office Professional)

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