excel and statistics functions

[sjvicker]

I was recently marked down on a lab project for using the excel functio
to find the confidence interval for 95% certainty. I properly used th
function but retrieved a different answer than my professor who use
Descriptitive Statistics under the Data Analysis-Tools feature.

The only way I can recover the points is to figure out why the answer
were different.

The sample size is 20, Mean 25.87, Max Value 28, Min Value 24.2
Standard Deviation 1.01, Range 3.80, Mode 26 and Median 25.75

Any ideas why our values were different? did excel possibly us
different tables?

-Spence

 

[Guest]

Read Help for the CONFIDENCE function. Does the the equation it specifies
give you any clues?

Most respondants in these newsgroups will not do your assignment for you,
but will try to help you figure it out for yourself.

Jerry

 

[Guest]

I was recently marked down on a lab project for using the
excel function to find the confidence interval for 95% certainty.
I properly used the function but retrieved a different answer
than my professor who used Descriptitive Statistics under
the Data Analysis-Tools feature.
The only way I can recover the points is to figure out why
the answers were different.

Why can't you ask your prof? Or are you saying that the prof
will give you the points back if __you__ can explain the
difference -- even if you are wrong(!)?

If the latter is the case -- the prof is requiring that __you__
to figure it -- I think it is unethical of you to ask for help. At
the same time, I think it is poor for the prof not to explain
("teach you") your mistake -- of course without giving you
any points back.

The sample size is 20, Mean 25.87, Max Value 28, Min Value 24.2,
Standard Deviation 1.01, Range 3.80, Mode 26 and Median 25.75
Any ideas why our values were different? did excel possibly use
different tables?

Nearly impossible to say, since you fail to tell us what the two
different error bounds are: yours and your prof's. Are you
familiar with the mantra "show your work"? (Klunk!)

Some possible explanations that you might consider: (1) you
used a z-score instead of a t-score (NORMINV v. TINV);
(2) you used the 95% t-score instead of the 97.5% t-score;
(3) you failed to use "1 -" appropriately in your TINV
computation.

Those are some common mistakes. Of course, it could be
anything. If you had posted your answer and the correct
(prof's) answer, the nature of your mistake might be more
clear.

On the other hand, if your discovery of your mistake is the
condition by which the prof will "recover the points" (I'd be
surprised!), I would be loathe to point you in the right
direction since the prof is obviously expecting you to learn
the material completely on your own :-(.

 

[sjvicker]

basically it boils down to him recieving a different answer than half o
the class. He recreated our results in class using the same method as u
but because it wasn't done using his method it was wrong. I talked wit
him and he told me that he couldn't figure out why the answers wer
different and that if I could I would be able to get my points back.
dont understand excel well enough to know what table's its pulling fro
so I figured someone would have a quick answer for me. At least I'
headed in a direction and that's better than nothing.

thanks for the help,
-Spence

 

[Mike Middleton]

Spencer -

Here's part of my reply to a related question on Feb. 4, 2006:

The CONFIDENCE worksheet function "gives the half-width of a confidence
interval. It is based on the normal distribution, and it is appropriate when
the population standard deviation is known, i.e., when the standard
deviation does not have to be estimated from the sample data.

If sample data are used to estimate standard deviation, the confidence
interval should be computed using the t distribution, not the normal
distribution. According to the book "Data Analysis Using Microsoft Excel:
Updated for Office XP" by Michael R Middleton, the half-width is

=TINV(1-Conf_Level,n-1)*STDEV(Data)/SQRT(n)
....

What does it mean?

The half-width and the sample mean are used to construct a confidence
interval from (mean - half-width) to (mean + half-width). Referring to the
Middleton book, here are two interpretations.

Subjective Probability Interpretation: Based on the sample there is a 99%
chance that the population mean is between (mean - half-width) and (mean +
half-width).

Relative Frequency (classical statistics) Interpretation: If we repeatedly
selected random samples of size 164 from this population and constructed a
confidence interval for each sample, approximately 99% of the confidence
intervals would contain the true population mean."

- Mike
www.mikemiddleton.com