Novice Needs Help: Broken Formula

mbrown279

New Member
Joined
Sep 16, 2018
Messages
10
Hello All, Novice Here

I am trying to use a formula that will assign a value based on the words it finds in the table. I am not sure what I am missing. I want the resulting value 1-5 to appear in the "Raw" column. The table below is the mock up of what I am looking to do.


Code:
=IF([@Answer]="", "",IF([@Answer]"Very Satisfied",5,IF([@Answer]"Satisfied",4,IF([@Answer]"Neither Satisfied Nor Dissatisfied"
,3,IF([@Answer]"Dissatisfied",2,IF([@Answer]"Very Dissatisfied",1,)))))))

Survey TypeAnswerRaw
Post-ChatVery Satisfied5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,289
Office Version
  1. 365
Platform
  1. Windows
This might be a better approach than a long, nested IF statement.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Very Satisfied</td><td style="text-align: right;background-color: #FFF2CC;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Satisfied</td><td style="text-align: right;background-color: #FFF2CC;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Neither Satisfied Nor Dissatisfied</td><td style="text-align: right;background-color: #FFF2CC;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Survey Type</td><td style=";">Answer</td><td style=";">Raw</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Dissatisfied</td><td style="text-align: right;background-color: #FFF2CC;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Post-Chat</td><td style=";">Very Satisfied</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="background-color: #FFF2CC;;">Very Dissatisfied</td><td style="text-align: right;background-color: #FFF2CC;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Poll</td><td style=";">Dissatisfied</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Interview</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet20</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C6</th><td style="text-align:left">=IFNA(<font color="Blue">VLOOKUP(<font color="Red">[@Answer],$F$1:$G$5,2,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
How about:

=IF([@Answer]="","",MATCH([@Answer],{"Very Dissatisfied","Dissatisfied","Neither Satisfied nor dissatisfied","Satisfied","Very satisfied"},0))
 

mbrown279

New Member
Joined
Sep 16, 2018
Messages
10
How about:

=IF([@Answer]="","",MATCH([@Answer],{"Very Dissatisfied","Dissatisfied","Neither Satisfied nor dissatisfied","Satisfied","Very satisfied"},0))

You sir are a god, thank you so much, that fixed it. Would you be able to explain how this works?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
Sure, it's just a basic MATCH function. If you look for a value ([@Answer]) in a list, MATCH returns the position of that value. Usually that list is kept in a range of cells on the sheet, like DRSteele has, but you can include the list as part of the function by surrounding it with the { and } symbols. Then you just have to make sure the list is in the order you want, and give it the 0 parameter to tell it to search sequentially.

Glad we could help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,877
Members
409,668
Latest member
mitunsLax

This Week's Hot Topics

Top