## A heartfelt complaint about Excel's Confidence() function

**Moderator:** SoftMaker Team

### A heartfelt complaint about Excel's Confidence() function

see

http://www.jeremymiles.co.uk/randomness/index.html

Does PlanMaker use the t- or the normal distribution in this function?

Lance

http://www.jeremymiles.co.uk/randomness/index.html

Does PlanMaker use the t- or the normal distribution in this function?

Lance

### Re: A heartfelt complaint about Excel's Confidence() function

Hi,

PlanMaker uses the normal distribution in the confidence function. We will check if we switch to the t-distribution when we review the extended and improved functions in Excel2010. (Thanks for the hint )

However, I wouldn't overestimate the effect, for reasonable sample sizes the results are not so different, this is also stated in the article. The complaint was about a sample size of 6, which is imho not enough to do serious statistics.

ws

PlanMaker uses the normal distribution in the confidence function. We will check if we switch to the t-distribution when we review the extended and improved functions in Excel2010. (Thanks for the hint )

However, I wouldn't overestimate the effect, for reasonable sample sizes the results are not so different, this is also stated in the article. The complaint was about a sample size of 6, which is imho not enough to do serious statistics.

ws

Werner Schmidt

SoftMaker Software GmbH

SoftMaker Software GmbH

- jimjutte
- SoftMaker Volunteers
**Posts:**848**Joined:**Sun Oct 28, 2007 9:08 pm**Location:**Wenzhou, China-
**Contact:**

### Re: A heartfelt complaint about Excel's Confidence() function

Hello Werner,

I was initially unconcerned with this post until you mentioned not being concerned about small differences in t and normal statistics. That is a no-no in statistics. If I am teaching a stats course, my students BETTER be getting the right answers or they will lose marks. Further, if I am working with levels of toxins that are also very small amounts, then publishing incorrect numbers will have severe implications on a career.

I'm not suggesting that people use PM over SAS or other stats software, but to collect data is quite easy in PM and especially new students who are looking for a small office package that is inexpensive, they will rely on this... Better to know that it is not the right tool, than for a student to be under the impression that they have the right tool... yet get the wrong answers.

I was initially unconcerned with this post until you mentioned not being concerned about small differences in t and normal statistics. That is a no-no in statistics. If I am teaching a stats course, my students BETTER be getting the right answers or they will lose marks. Further, if I am working with levels of toxins that are also very small amounts, then publishing incorrect numbers will have severe implications on a career.

I'm not suggesting that people use PM over SAS or other stats software, but to collect data is quite easy in PM and especially new students who are looking for a small office package that is inexpensive, they will rely on this... Better to know that it is not the right tool, than for a student to be under the impression that they have the right tool... yet get the wrong answers.

Cheers

Jim

Jim

### Re: A heartfelt complaint about Excel's Confidence() function

Thanks for the reply. If you followed the links in the article you would have seen that MS is thinking of revising their functions so it would be good if PM also made use of industry's best practices...ws wrote:Hi,

PlanMaker uses the normal distribution in the confidence function. We will check if we switch to the t-distribution when we review the extended and improved functions in Excel2010. (Thanks for the hint )

However, I wouldn't overestimate the effect, for reasonable sample sizes the results are not so different, this is also stated in the article. The complaint was about a sample size of 6, which is imho not enough to do serious statistics.

ws

As for the small sample well spreadsheets do get used for serious jobs including stats, and they often get used by people who need some guidance (students, businessmen without stats training, etc), so it is best if the functions available really are correct.

Lance

### Re: A heartfelt complaint about Excel's Confidence() function

I was once asked to help out a "consultant" who had bought a personnel assessment program that ran in Excel and was employed to assess staff at a bank. The groups of employees who were being assessed ranged from 8 to 15 members. On such small samples this consultant happily detected "outliers" and drew confidence bands which served as the basis for rewarding staff with bonuses or giving staff members warning letters about under performing. Given the small sample sizes and the fact that the assessments of the staff were largely ratings by untrained bosses I was horrified that such real world decisions were being taken on the basis of very poor statistical information. The point I'm making is that well trained statisticians are not likely to make errors using small samples, but statistics has escaped from the grasp of academic statisticians and is now routinely employed by businesses and students and government officials - most of them relying on the functions in a spreadsheet.jimjutte wrote:Hello Werner,

I'm not suggesting that people use PM over SAS or other stats software, but to collect data is quite easy in PM and especially new students who are looking for a small office package that is inexpensive, they will rely on this... Better to know that it is not the right tool, than for a student to be under the impression that they have the right tool... yet get the wrong answers.

Lance

- jimjutte
- SoftMaker Volunteers
**Posts:**848**Joined:**Sun Oct 28, 2007 9:08 pm**Location:**Wenzhou, China-
**Contact:**

### Re: A heartfelt complaint about Excel's Confidence() function

Hi Lance,

Not sure if you meant to respond to me, but I'll comment anyway. It is certainly not infrequent that people use and abuse statistics at their peril, but regardless, if one starts with incorrect numbers, one has a lower probability (pun intended) of arriving a correct answer vice starting with correct numbers. Further, if one is already using the correct numbers there is a good chance that the might actually know what they are doing and the limitations of their study.

Not sure if you meant to respond to me, but I'll comment anyway. It is certainly not infrequent that people use and abuse statistics at their peril, but regardless, if one starts with incorrect numbers, one has a lower probability (pun intended) of arriving a correct answer vice starting with correct numbers. Further, if one is already using the correct numbers there is a good chance that the might actually know what they are doing and the limitations of their study.

Cheers

Jim

Jim

### Re: A heartfelt complaint about Excel's Confidence() function

Jim -jimjutte wrote:Hi Lance,

Not sure if you meant to respond to me, but I'll comment anyway. It is certainly not infrequent that people use and abuse statistics at their peril, but regardless, if one starts with incorrect numbers, one has a lower probability (pun intended) of arriving a correct answer vice starting with correct numbers. Further, if one is already using the correct numbers there is a good chance that the might actually know what they are doing and the limitations of their study.

Thanks for the thoughtful reply. There is so much concealed by that word "correct". My point was that the world is full of people with a little information about stats and they make decisions that affect you and I, and they use spreadsheet software to make their decisions. No piece of software is ever going to make them fully comprehend what they are doing, but the software should itself be correct so that the wideness of (say) their confidence interval alerts them to (say) the problem of a small sample size.

Thanks

Lance

- jimjutte
- SoftMaker Volunteers
**Posts:**848**Joined:**Sun Oct 28, 2007 9:08 pm**Location:**Wenzhou, China-
**Contact:**

### Re: A heartfelt complaint about Excel's Confidence() function

I believe we are in agreement... Nevertheless, I love the quote by I believe it was Mark Twain... There is Statistics, Statistics and damned lies

Cheers

Jim

Jim

- jimjutte
- SoftMaker Volunteers
**Posts:**848**Joined:**Sun Oct 28, 2007 9:08 pm**Location:**Wenzhou, China-
**Contact:**

### Re: A heartfelt complaint about Excel's Confidence() function

LOL... and now you know why I was never an English major

Cheers

Jim

Jim