2
$\begingroup$

When I refer to all these 6 values, I am using STDEVP (or whatever exactly it is called in the English version of Excel--I got various hits for the "whole" stdev variant).

  1. What should I use for calculating a CI with \alpha = 0.05 then, CONFIDENCE.NORM or CONFIDENCE.T?

  2. If I refer to 4 of said 6 values and then use STDEV.S, would that be correct? Also, if I like to calculate the CI (alpha=0.05) for those 4 values, I have to switch the formula to using the stdev.s value, right?

Improve this question
$\endgroup$
5
  • 1
    $\begingroup$ Can you edit your question (use the "edit" link at lower left) to say what kind of data you have (i.e what kinds of values your data take)? $\endgroup$ – Alexis Aug 11 '14 at 13:47
  • $\begingroup$ @Alexis Edited the op. Is that ok? $\endgroup$ – henry Aug 11 '14 at 13:50
  • $\begingroup$ The proper answer is "no." Frankly, I wouldn't trust Excel's builtin functions, having read too many careful analyses showing that some of them produce incorrect (albeit usually in the 4th or 5th place) results. $\endgroup$ – Carl Witthoft Aug 11 '14 at 19:47
  • 1
    $\begingroup$ That's a safe default position, @Carl, but not everybody has the luxury of adopting it. For subtler and more constructive criticism of Excel (and other spreadsheets) consider our thread on Excel as a statistics workbench. $\endgroup$ – whuber♦ Aug 11 '14 at 20:51
  • $\begingroup$ @whuber True 'dat :-( . I plead guilty to being a minor-league evangelist, attempting to bring the "news" to folks that there are far better alternatives to Excel. $\endgroup$ – Carl Witthoft Aug 12 '14 at 11:17
5
$\begingroup$

For continuous measurements, such as force in Newtons, where you do not know the population variance (as opposed to sample variance, which is what you have to work with – implying STDEV.S), the confidence interval of the mean based on the t distribution is what you are after.

You would use the confidence interval based on the normal distribution only if you had the population variance a priori.

Improve this answer
$\endgroup$
1
$\begingroup$

Taking a quick look at the documentation, you should be using CONFIDENCE.T since you are estimating the standard deviation of your sample.

For CONFIDENCE.NORM, the standard deviation must be known (aka not estimated from your data).

Finally, you should be using STDEV.S for all calculations.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.