In: Math
As the list is not furnished, I just take a sample of my own. The "POSITIVE" response are replaced by 1 and the "NEGATIVE" response are replaced by 0. This is done in Excel with the formula
=IF(A2="positive",1,0) in cell B2
=IF(A3="positive",1,0) in cell B3
select both the cells and drag it down to B301.
Response | Response(converted) |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
negative | 0 |
positive | 1 |
negative | 0 |
positive | 1 |
positive | 1 |
positive | 1 |
negative | 0 |
negative | 0 |
negative | 0 |
negative | 0 |
positive | 1 |
positive | 1 |
Now the counts of positive and negative responses are made.
The counts for positive response are obtained by the formula
=countif(b2:b301,1)
The counts for negative response are obtained by the formula
=countif(b2:b301,0)
Now the corresponding proportions of the responses are counted.
This is made by dividing the corresponding cell in which the count is stored by 300(total number of observations).
Now the 80% CV is calculated by the following formula
=NORM.INV(0.9,0,1)
The standard error is calculated by the formula
=SQRT(F11*F12/F8)
where F11 is the proportion of positive responses
F12 is the proportion of negative responses
F8 is the total number of observations=300
The margin of error is calculated by
=F14*F15
where F14 is 80% CV
F15 is the standard error
The upper confidence interval is calculated by the sum of the proportion of the positive responses and the margin of error.
The lower confidence interval is calculated by the difference of the proportion of the positive responses and the margin of error.
Hence the confidence interval is
(0.3933691,0.4666309)
A screenshot of the excel sheet is attached for betterment of understanding.
In case of any query, do comment. If this helps, give a thumbs up. Thanks.