+1 vote
in General IT Tips by (35.2k points)
I have some values in a column and want to calculate the 95% confidence interval. What formula should I use in LibreOffice Calc?

1 Answer

+1 vote
by (176k points)
selected by
 
Best answer

The formula for 95% confidence interval (CI) when the sample size is greater than 30 is

95% CI = mean ± z_score*standard deviation for the sample/square_root(sample size) ... (1)

z-score for 95% confidence interval is 1.96. It is recommended to use t-score instead of z-score if the sample size is less than 30.

95% CI = mean ± t_score*standard deviation for the sample/square_root(sample size) ... (2)

t-table is available on the internet. You need to use Degrees of freedom (df =sample size-1) to find the value of the t-score for the given sample size.

E.g., t-score for a 95% confidence interval if the sample size (n) is 20 = 2.093.

Let's say you have data in column A from A1:A20.

  • You can compute the mean in LibreOffice Calc using the formula

=AVERAGE(A1:A20)

  • Compute the ratio of standard deviation and the square root of the sample size using the following formula.

=STDEV(A1:A20)/SQRT(20)

Once you have the above two values, you can use them to find 95% confidence interval for the given sample using the formula 1 or 2, depending on the sample size.

Related questions

+1 vote
1 answer
+3 votes
1 answer
+3 votes
1 answer

...