A heartfelt complaint about Excel's Confidence() function

Post bug reports and suggestions for PlanMaker 2008 for Windows.

Moderator: SoftMaker Team

LanceGary
Posts: 102
Joined: Sun Dec 09, 2007 9:55 pm

A heartfelt complaint about Excel's Confidence() function

Postby LanceGary » Sat Nov 07, 2009 1:15 pm

see

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

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

Lance

ws
SoftMaker Team
SoftMaker Team
Posts: 100
Joined: Mon Nov 24, 2003 4:57 pm
Location: Nürnberg

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

Postby ws » Tue Nov 10, 2009 10:32 am

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
Werner Schmidt
SoftMaker Software GmbH

User avatar
jimjutte
SoftMaker Volunteers
SoftMaker Volunteers
Posts: 848
Joined: Sun Oct 28, 2007 9:08 pm
Location: Shanghai, China
Contact:

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

Postby jimjutte » Tue Nov 10, 2009 6:28 pm

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.
Cheers
Jim

LanceGary
Posts: 102
Joined: Sun Dec 09, 2007 9:55 pm

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

Postby LanceGary » Tue Nov 10, 2009 10:53 pm

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


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...

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

LanceGary
Posts: 102
Joined: Sun Dec 09, 2007 9:55 pm

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

Postby LanceGary » Tue Nov 10, 2009 11:03 pm

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.


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.

Lance

User avatar
jimjutte
SoftMaker Volunteers
SoftMaker Volunteers
Posts: 848
Joined: Sun Oct 28, 2007 9:08 pm
Location: Shanghai, China
Contact:

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

Postby jimjutte » Wed Nov 11, 2009 9:08 pm

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. :)
Cheers

Jim

LanceGary
Posts: 102
Joined: Sun Dec 09, 2007 9:55 pm

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

Postby LanceGary » Thu Nov 12, 2009 9:52 am

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. :)


Jim -

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

User avatar
jimjutte
SoftMaker Volunteers
SoftMaker Volunteers
Posts: 848
Joined: Sun Oct 28, 2007 9:08 pm
Location: Shanghai, China
Contact:

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

Postby jimjutte » Fri Nov 13, 2009 6:05 am

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

french
Posts: 29
Joined: Wed Feb 14, 2007 4:03 am

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

Postby french » Fri Nov 13, 2009 4:54 pm

...lies, damned lies, and statistics.

http://en.wikipedia.org/wiki/Lies,_damned_lies,_and_statistics

User avatar
jimjutte
SoftMaker Volunteers
SoftMaker Volunteers
Posts: 848
Joined: Sun Oct 28, 2007 9:08 pm
Location: Shanghai, China
Contact:

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

Postby jimjutte » Sat Nov 14, 2009 12:56 am

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

Jim


Return to “PlanMaker 2008 for Windows”

Who is online

Users browsing this forum: No registered users and 1 guest

cron