Statistical question - confidence intervals

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top