Statistical question - confidence intervals

bruty

Active Member
Joined
Jul 25, 2007
Messages
443
I have some data I have inherited from several years back and no-one who was around back then is still around to ask.

Code:
Year	              Question #	   Lower 95% confidence interval	    Scored percentage	   Upper 95% confidence interval	      No of responses for this question and trust
2014	               Q8	                77.8%	                                80.1%	                82.4%	                                1143
2014	               Q9	                74.0%	                                76.3%	                78.5%	                                1410
2014	               Q10	                82.2%	                                84.1%	                86.0%	                                1412
2014	               Q11	                67.4%	                                69.8%	                72.1%	                                1422
2014	               Q12	                58.3%	                                60.8%	                63.4%	                                1408
2014	               Q13	                83.5%	                                85.3%	                87.2%	                                1403

2018	               Q8		                                                82.0%		                                                862
2018	               Q9		                                                76.3%		                                                1354
2018	               Q10		                                                84.5%		                                                1422
2018	               Q11		                                                71.6%		                                                1439
2018	               Q12		                                                67.9%		                                                1234
2018	               Q13		                                                86.9%		                                                1296
For 2014 there is a lower and higher confidence interval entered that I need to duplicate out for the 2018 data, but I have no idea how. Can anyone give any advice?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,822
Office Version
365
Platform
Windows
Ok lets say you have your Q1, Q2 Q3 etc etc in column A and your percentages scores in column B your lower % for Q1 is:

=AVERAGEIF($A$2:$A$1000,"Q1",$B$2:$B$1000)-(1.96*STDEV.S(IF($A$2:$A$1000="Q1",$B$2:$B$1000))/SQRT(COUNTIF($A$2:$A$1000,"Q1")))

Q1 can be replaced by a cell ref if you want. Upper is just a plus rather than a minus from the mean.
 

bruty

Active Member
Joined
Jul 25, 2007
Messages
443
Ok lets say you have your Q1, Q2 Q3 etc etc in column A and your percentages scores in column B your lower % for Q1 is:

=AVERAGEIF($A$2:$A$1000,"Q1",$B$2:$B$1000)-(1.96*STDEV.S(IF($A$2:$A$1000="Q1",$B$2:$B$1000))/SQRT(COUNTIF($A$2:$A$1000,"Q1")))

Q1 can be replaced by a cell ref if you want. Upper is just a plus rather than a minus from the mean.
Thanks for this. So for each question it's (the average of the scores for that question) minus (1.96 time the standard deviation of the scores for that question, divided by the number of answers for the question)?

Where does the 1.96 come from?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,822
Office Version
365
Platform
Windows
The 1.96 is something called the z score and is for the 95%. Its all to do with the normal distribution which test scores would fall into. Type z score 1.96 into google and you will find loads of information.
 

Forum statistics

Threads
1,085,056
Messages
5,381,461
Members
401,740
Latest member
jphermans

Some videos you may like

This Week's Hot Topics

Top