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,793
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,793
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,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top