Novice Needs Help: Broken Formula

[mbrown279]

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.

=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,)))))))

[TABLE="width: 500"]
<tbody>[TR]
[TD]Survey Type[/TD]
[TD]Answer[/TD]
[TD]Raw[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Post-Chat[/TD]
[TD]Very Satisfied[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

This might be a better approach than a long, nested IF statement.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G
1
2						Very Satisfied	5
3						Satisfied	4
4						Neither Satisfied Nor Dissatisfied	3
5	Survey Type	Answer	Raw			Dissatisfied	2
6	Post-Chat	Very Satisfied	5			Very Dissatisfied	1
7	Poll	Dissatisfied	2
8	Interview
Sheet20

Cell Formulas
Range	Formula
C6	=IFNA(VLOOKUP([@Answer],$F$1:$G$5,2,0),"")

[/FONT]

 

[Eric W]

How about:

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

 

[mbrown279]

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]

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.