Statistical question - confidence intervals

[bruty]

I have some data I have inherited from several years back and no-one who was around back then is still around to ask.

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]

Do you have the full dataset?

 

[bruty]

I do for the 2018 data where I need to add this.

 

[steve the fish]

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]

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]

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.

 

[bruty]

Thanks for the help.