Sentiment analysis - assigning positive, negative or neutral score

[misstashnz]

I'm trying to complete a sentiment analysis on product reviews. I have used a 'bag of words' method, and what I'm trying to achieve is to give each review a rating of '1' for positive, '0' for neutral, or '-1' for negative. There's ~35k reviews, so need a formula that I can just replicate down the list.

I've managed to get as far as giving a review a tick as positive if i contains one of my positive words, same as for negative, and then created an IF OR formula to specify it is positive if it only has positive words, negative if only negative words, and neutral if it contains both. So each review has individual columns for positive, neg, and neutral - only one column will display 'Yes'. From here, I'm really stuck in figuring out how to create a formula with three variable outputs - everything is just 'value if true' or 'value if false'.

Any bright ideas would be immensely appreciated!

Assessment 1 - Qualitative review.xlsx
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM
1	Positive words																											Negative words
2	Fast	Easy	Great	Good	Love	Recommend	Enjoy	Amaz..	Happy	Excellent	Handy	Glad	Convenient	Impress..	Beaut..	Quick	Perfect	Nice	Useful	Awesome	Sturdy	Easily	Wonderful	Fantastic	Inexpensive	Positive?	Positive Score	Slow	Hard	Hate	Dislike	Freeze	Issue	Don't like	Difficult	Inconvenient	Annoying	Heavy	Unnecessary	Flaw	Pricey	Problem	Awkward	Hurt	Ugly	Bulky	Bad	Do not like	Complicate	Unable	Poor	Glitch	Negative?	Negative Score	Overall Positive	Overall Negative	Overall Neutral
3	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
4	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
5	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	Yes	1	No	No	Yes
6	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	1	0	0	0	0	0	0	0	Yes	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	Yes	1	No	No	Yes
7	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
8	1	1	1	1	0	0	1	1	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	Yes	8	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
9	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	No	Yes	No
10	0	0	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
11	1	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	1	0	0	0	0	0	Yes	2	No	No	Yes
12	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	Yes	2	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	No	No	Yes
13	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	Yes	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
14	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	Yes	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	No	0	Yes	No	No
15	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	Yes	3	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Yes	1	No	No	Yes
exercise 1

Cell Formulas
Range	Formula
I3:I15	I3	=COUNTIF(H3,"*fast*")
J3:J15	J3	=COUNTIF(H3,"*easy*")
K3:K15	K3	=COUNTIF(H3,"*great*")
L3:L15	L3	=COUNTIF(H3,"*good*")
M3:M15	M3	=COUNTIF(H3,"*love*")
N3:N15	N3	=COUNTIF(H3,"*highly recommend*")
O3:O15	O3	=COUNTIF(H3,"*enjoy*")
P3:P15	P3	=COUNTIF(H3,"*amaz*")
Q3:Q15	Q3	=COUNTIF(H3,"*happy*")
R3:R15	R3	=COUNTIF(H3,"*excellent*")
S3:S15	S3	=COUNTIF(H3,"*handy*")
T3:T15	T3	=COUNTIF(H3,"*glad*")
U3:U15	U3	=COUNTIF(H3,"*convenient*")
V3:V15	V3	=COUNTIF(H3,"*impress*")
W3:W15	W3	=COUNTIF(H3,"*beaut*")
X3:X15	X3	=COUNTIF(H3,"*quick*")
Y3:Y15	Y3	=COUNTIF(H3,"*perfect*")
Z3:Z15	Z3	=COUNTIF(H3,"*nice*")
AA3:AA15	AA3	=COUNTIF(H3,"*useful*")
AB3:AB15	AB3	=COUNTIF(H3,"*awesome*")
AC3:AC15	AC3	=COUNTIF(H3,"*sturdy*")
AD3:AD15	AD3	=COUNTIF(H3,"*easily*")
AE3:AE15	AE3	=COUNTIF(H3,"*wonderful*")
AF3:AF15	AF3	=COUNTIF(H3,"*fantastic*")
AG3:AG15	AG3	=COUNTIF(H3,"*inexpensive*")
AH3:AH15	AH3	=IF(OR(I3=1,J3=1,K3=1,L3=1,M3=1,N3=1,O3=1,P3=1,Q3=1,R3=1, S3=1, T3=1, U3=1, V3=1, W3=1, X3=1, Y3=1, Z3=1, AA3=1, AB3=1, AC3=1, AD3=1, AE3=1, AF3=1, AG3=1),"Yes","No")
AI3:AI15,BJ3:BJ15	AI3	=SUM(I3:AG3)
AJ3:AJ15	AJ3	=COUNTIF(H3,"*slow*")
AK3:AK15	AK3	=COUNTIF(H3,"*hard*")
AL3:AL15	AL3	=COUNTIF(H3,"*hate*")
AM3:AM15	AM3	=COUNTIF(H3,"*dislike*")
AN3:AN15	AN3	=COUNTIF(H3,"*freez*")
AO3:AO15	AO3	=COUNTIF(H3,"*issue*")
AP3:AP15	AP3	=COUNTIF(H3,"*don't like*")
AQ3:AQ15	AQ3	=COUNTIF(H3,"*difficult*")
AR3:AR15	AR3	=COUNTIF(H3,"*inconvenient*")
AS3:AS15	AS3	=COUNTIF(H3,"*annoying*")
AT3:AT15	AT3	=COUNTIF(H3,"*expensive*")
AU3:AU15	AU3	=COUNTIF(H3,"*unnecessary*")
AV3:AV15	AV3	=COUNTIF(H3,"*flaw*")
AW3:AW15	AW3	=COUNTIF(H3,"*pricey*")
AX3:AX15	AX3	=COUNTIF(H3,"*problem*")
AY3:AY15	AY3	=COUNTIF(H3,"*awkward*")
AZ3:AZ15	AZ3	=COUNTIF(H3,"*hurt*")
BA3:BA15	BA3	=COUNTIF(H3,"*ugly*")
BB3:BB15	BB3	=COUNTIF(H3,"*bulky*")
BC3:BC15	BC3	=COUNTIF(H3,"*bad*")
BD3:BD15	BD3	=COUNTIF(H3,"*do not like*")
BE3:BE15	BE3	=COUNTIF(H3,"*complicate*")
BF3:BF15	BF3	=COUNTIF(H3,"*unable*")
BG3:BG15	BG3	=COUNTIF(H3,"*poor*")
BH3:BH15	BH3	=COUNTIF(H3,"*glitch*")
BI3:BI15	BI3	=IF(OR(AJ3=1,AK3=1,AL3=1,AM3=1,AN3=1,AO3=1,AP3=1,AQ3=1,AR3=1,AS3=1,AT3=1,AU3=1,AV3=1,AW3=1,AX3=1,AY3=1,AZ3=1,BA3=1,BB3=1,BC3=1,BD3=1,BE3=1,BF3=1,BG3=1,BH3=1),"Yes","No")
BK3:BK15	BK3	=IF(AND(AH3="Yes",BI3="No"),"Yes","No")
BL3:BL15	BL3	=IF(AND(AH3="No",BI3="Yes"),"Yes","No")
BM3:BM15	BM3	=IF(AND(AH3="Yes",BI3="Yes"),"Yes","No")

 

[Peter_SSs]

Is column H freeform text? If so, your current formulas could be giving misleading results. For example "My glove has an ugly fastener" would count two positive scores and one negative, despite it not containing any of your positive "words" as words. Is that a problem for you? That is, do we need to check for whole words rather than just text strings? If looking for whole words though I can see a problem with your examples like "Amaz" where I presume you are trying to cover off several possibilities.

If a text contained 6 positive words and one negative word can you confirm that you still want that result as 'Neutral'?

 

[misstashnz]

Is column H freeform text? If so, your current formulas could be giving misleading results. For example "My glove has an ugly fastener" would count two positive scores and one negative, despite it not containing any of your positive "words" as words. Is that a problem for you? That is, do we need to check for whole words rather than just text strings? If looking for whole words though I can see a problem with your examples like "Amaz" where I presume you are trying to cover off several possibilities.

If a text contained 6 positive words and one negative word can you confirm that you still want that result as 'Neutral

Yeah, this is for one of my papers at uni and we're currently looking at ways to code qualitative data. We're aware that this method will not provide accurate results, but just learning how it works and understanding pros and cons (such as what you've mentioned).

Unfortunately the internet has not been kind in showing me ways to do this that don't use existing software or excel add-ins, so doing the best I can!

 

[Peter_SSs]

What about something like this then?

misstashnz.xlsm
	H	I	J	K	L	M	N
1						Positive	Negative
2	Text	Result				Fast	Slow
3	It was fast and easy	Positive				Easy	Hard
4	Tom hates eggs	Negative				Great	Hate
5	This is amazingly difficult and not fast	Neutral				Good	Dislike
6	Nothing here at all	Neutral				Love	Freeze
7	Slow, hard, hate, dislike	Negative				Recommend	Issue
8						Enjoy	Don't like
9						Amaz	Difficult
10						Happy	Inconvenient
11						Excellent	Annoying
12						Handy	Heavy
13						Glad	Unnecessary
14						Convenient	Flaw
15						Impress	Pricey
16						Beaut	Problem
17						Quick	Awkward
18						Perfect	Hurt
19						Nice	Ugly
20						Useful	Bulky
21						Awesome	Bad
22						Sturdy	Do not like
23						Easily	Complicate
24						Wonderful	Unable
25						Fantastic	Poor
26						Inexpensive	Glitch
Sheet2

Cell Formulas
Range	Formula
I3:I7	I3	=INDEX({"Neutral","Positive","Negative"},MOD((COUNT(SEARCH(M$2:M$26,H3))>0)+2*(COUNT(SEARCH(N$2:N$26,H3))>0),3)+1)