software

What Excel function or expression gives the same results
as the TI83 2-SampTTest function -- at least the p-value?

Ostensibly, TTEST() seems to be the right thing. But it
seems to want to work with the original data.

I want to enter just the means and std devs of the two
samples and compute the p-value.

With a lot of effort, I suppose I could create two normal
distributions that have the same mean and std devs. But
that is quot;killing an ant with a sledgehammerquot;.

If I had the math formulas for computing the p-value, I
suppose I could create a macro. But sigh, my stat text
is unclear to me now, some time later -- like what should
delta be in the Excel help sheet for quot;About statistical
analysis tools gt; t-testquot;. Besides, even that is more work
than I think should be necessary.

joeu2004 -

I don't have a T183, and if you don't want to wait for a reply from someone
who does, please specify which of Excel's three t-tests you want to use.

Delta refers to the hypothesized difference between the population means,
usually zero. In the Analysis ToolPak versions of the tests, delta is called
quot;hypothsized mean differencequot; in the dialog boxes.

The specific calculations depend on the specific test (one of the three):
You use delta, the sample means, the sample standard deviations, and the
sample sizes to compute a t-statistic. You use the sample sizes to compute
degrees of freedom for the t-statistic. And you use the TDIST worksheet
function to get the p-value.

- Mike
www.mikemiddleton.com

quot; gt; wrote
in message ...
gt; What Excel function or expression gives the same results
gt; as the TI83 2-SampTTest function -- at least the p-value?
gt;
gt; Ostensibly, TTEST() seems to be the right thing. But it
gt; seems to want to work with the original data.
gt;
gt; I want to enter just the means and std devs of the two
gt; samples and compute the p-value.
gt;
gt; With a lot of effort, I suppose I could create two normal
gt; distributions that have the same mean and std devs. But
gt; that is quot;killing an ant with a sledgehammerquot;.
gt;
gt; If I had the math formulas for computing the p-value, I
gt; suppose I could create a macro. But sigh, my stat text
gt; is unclear to me now, some time later -- like what should
gt; delta be in the Excel help sheet for quot;About statistical
gt; analysis tools gt; t-testquot;. Besides, even that is more work
gt; than I think should be necessary.
quot;Mike Middletonquot; wrote:
gt; I don't have a T183, and if you don't want to wait for a reply
gt; from someone who does

I specified my requirement in TI83 terms partly because that
was the tool I used and partly to provide an unambiguous
specification of my requirement, lest I use the terminology
incorrectly or we disagree on the terminology.

I am still hoping someone with knowledge of both TI83 and
Excel statistics will respond.

gt; please specify which of Excel's three t-tests you want to use.

I believe the two-sample assuming equal (population?) variance
test. But honestly, I do not recognize that terminology. As I
said, I have two sample means and two sample std devs. The
null hypothesis is that the population means are equal.

gt; Delta refers to the hypothesized difference between the
gt; population means, usually zero.

Well, duh! Yes, I should have realized that zero works in my
case.

However, if my null hypothesis was quot;u1 gt;= u2quot; -- with no
presumption about the difference -- I do not know what would
be appropriate for delta in the t-score formula given in the
quot;about statistical analysisquot; help page.

gt; You use the sample sizes to compute degrees of freedom
gt; for the t-statistic. And you use the TDIST worksheet function
gt; to get the p-value.

That's a lot of complexity. Are you confirming that there is no
Excel function(s) to compute the p-value given only the sample
means and std dev?

I am surprised.

The example on p.481 of the TI-83 Plus/TI-83 Plus Silver Edition Graphing
Calculator Guidebook
education.ti.com/guidebooks/graphing/83p/83m$book-eng.pdf
is equivalent to Excel's
=TTEST({12.207,16.869,25.05,22.429,8.456,10.589},{ 11.074,9.686,12.064,9.351,8.182,6.642},2,3)

TI's option mu1lt;gt;mu2 is equivalent to specifying a 2 for Excel's 3rd (tails)
parameter. Specifying a 1 for Excel's 3rd (tails) parameter should give you
the smaller of TI's 1-sided hypothesis p-values.

The TI manual does not explain the pooled option, but I will guess that
Pooled=No is equivalent to Excel's Type=3 and that Pooled=Yes is equivalent
to Excel's Type=2 (4th argument).

Excel does not have a pre-programmed equivalent of TI's Input=Stats mode,
and TI does not appear to have a pre-programmed equivalent of TI's Type=1.
Writing a VBA function to work from Stats instead of Data would be a
straightforward exercise. There does not seem to be an MS KB equivalent of
p.603 of the old Excel 5 User's Guide (the last time MS gave really good
documentation of its statistical functionality), but formulas are widely
available, for example
www.statsdirect.com/help/para...ethods/utt.htm

The Satterthwaite approximate df for unequal variances (Type=3) will
generally not be an integer. Excel's TDIST() function does not support
fractional df. Excel's Analysis ToolPak resolves this by truncating to the
next smaller integer. The TI93 and Excel's TTEST function evaluate using the
more accurate fractional df, which you can mimic by using the identity that
TDist(x,df,2) = BetaDist(df/(df x²),ï€*df/2, 0.5)

Trying to find someone who is knowledgeable in both a pocket calculator and
Excel may be a daunting task. For instance, I haven't used a pocket
calculator in any serious way for over 20 years.

Jerry

quot; wrote:

gt; quot;Mike Middletonquot; wrote:
gt; gt; I don't have a T183, and if you don't want to wait for a reply
gt; gt; from someone who does
gt;
gt; I specified my requirement in TI83 terms partly because that
gt; was the tool I used and partly to provide an unambiguous
gt; specification of my requirement, lest I use the terminology
gt; incorrectly or we disagree on the terminology.
gt;
gt; I am still hoping someone with knowledge of both TI83 and
gt; Excel statistics will respond.
gt;
gt; gt; please specify which of Excel's three t-tests you want to use.
gt;
gt; I believe the two-sample assuming equal (population?) variance
gt; test. But honestly, I do not recognize that terminology. As I
gt; said, I have two sample means and two sample std devs. The
gt; null hypothesis is that the population means are equal.
gt;
gt; gt; Delta refers to the hypothesized difference between the
gt; gt; population means, usually zero.
gt;
gt; Well, duh! Yes, I should have realized that zero works in my
gt; case.
gt;
gt; However, if my null hypothesis was quot;u1 gt;= u2quot; -- with no
gt; presumption about the difference -- I do not know what would
gt; be appropriate for delta in the t-score formula given in the
gt; quot;about statistical analysisquot; help page.
gt;
gt; gt; You use the sample sizes to compute degrees of freedom
gt; gt; for the t-statistic. And you use the TDIST worksheet function
gt; gt; to get the p-value.
gt;
gt; That's a lot of complexity. Are you confirming that there is no
gt; Excel function(s) to compute the p-value given only the sample
gt; means and std dev?
gt;
gt; I am surprised.

quot;Jerry W. Lewisquot; wrote:
gt; The example on p.481 of the TI-83 Plus/TI-83 Plus Silver
gt; Edition Graphing Calculator Guidebook
gt; education.ti.com/guidebooks/graphing/83p/83m$book-eng.pdf
gt; is equivalent to Excel's
gt; =TTEST({12.207,16.869,25.05,22.429,8.456,10.589},
gt; {11.074,9.686,12.064,9.351,8.182,6.642},2,3)

If I had the raw data, I wouldn't be asking the question, now
would I?! The TI83 also has the option of entering just two
sample means and std devs. That is the __only__ case I want
to discusss.

gt; Excel does not have a pre-programmed equivalent of TI's
gt; Input=Stats mode

Okay, I'll take your word for it. I really am very surprised.

gt; There does not seem to be an MS KB equivalent of p.603 of
gt; the old Excel 5 User's Guide

Is that available online today? I don't know how to get it.

gt; but formulas are widely available, for example
gt; www.statsdirect.com/help/para...ethods/utt.htm

That seems to be the same formulas that are in the Excel
quot;about statistical toolsquot; help page. And I realize now that they
would work for my case of a quot;u1 = u2quot; null hypothesis. I am
just (still) surprised that I must enter all the formulas. I would
think this is a not-uncommon requirement, albeit perhaps also
not necessarily the most common one either.

But according to my (remedial) stat book, the complete numerator
for the t-score is (m1 - m2) - (u1 - u2), where m1 and m2
are the sample means, and u1 and u2 are the population
means. That is the genesis of quot;deltaquot; on the Excel help page.
Of course, we do not know u1 and u2. So it is unclear how I
would use that formula for a quot;u1 lt;= u2quot; null hypothesis.

Oh well, that question is better discussed in a stat newsgroup.
All I was asking here was what Excel function did the job. I
am hearing a resounding quot;none!quot; :-(.quot;Jerry W. Lewisquot; wrote:
gt; Excel's TDIST() function does not support fractional df.
gt; [....] Excel's TTEST function evaluate using the more
gt; accurate fractional df, which you can mimic by using the
gt; identity that TDist(x,df,2) = BetaDist(df/(df x²),ï€*df/2, 0.5)

I am interested in using the BetaDist() function. But in my
news reader, there is a character before quot;df/2quot; which looks
like a box, usually indicating an unsupported special character.
I'm not sure it reproduced in the citation above.

My question: is it simply quot;df/2quot;, or is it something else?

If the latter, please spell it out in English, since not all news
readers support all character sets and special characters.

joeu2004 -

gt; But according to my (remedial) stat book, the complete numerator for the
gt; t-score is (m1 - m2) - (u1 - u2), where m1 and m2 are the sample means,
gt; and u1 and u2 are the population means. That is the genesis of quot;deltaquot; on
gt; the Excel help page. Of course, we do not know u1 and u2. So it is
gt; unclear how I would use that formula for a quot;u1 lt;= u2quot; null hypothesis. lt;

One possible explanation is that classical statisticians may use a single
point, not an interval, for the null hypothesis. That single point is the
basis for the hypothesized sampling distribution from which the p-value is
derived. The direction of the test is indicated by the alternative
hypothesis, either left-tail, two-tail, or right-tail.

- Mike
www.mikemiddleton.com

quot; gt; wrote
in message ...
gt; quot;Jerry W. Lewisquot; wrote:
gt;gt; The example on p.481 of the TI-83 Plus/TI-83 Plus Silver
gt;gt; Edition Graphing Calculator Guidebook
gt;gt; education.ti.com/guidebooks/graphing/83p/83m$book-eng.pdf
gt;gt; is equivalent to Excel's
gt;gt; =TTEST({12.207,16.869,25.05,22.429,8.456,10.589},
gt;gt; {11.074,9.686,12.064,9.351,8.182,6.642},2,3)
gt;
gt; If I had the raw data, I wouldn't be asking the question, now
gt; would I?! The TI83 also has the option of entering just two
gt; sample means and std devs. That is the __only__ case I want
gt; to discusss.
gt;
gt;gt; Excel does not have a pre-programmed equivalent of TI's
gt;gt; Input=Stats mode
gt;
gt; Okay, I'll take your word for it. I really am very surprised.
gt;
gt;gt; There does not seem to be an MS KB equivalent of p.603 of
gt;gt; the old Excel 5 User's Guide
gt;
gt; Is that available online today? I don't know how to get it.
gt;
gt;gt; but formulas are widely available, for example
gt;gt; www.statsdirect.com/help/para...ethods/utt.htm
gt;
gt; That seems to be the same formulas that are in the Excel
gt; quot;about statistical toolsquot; help page. And I realize now that they
gt; would work for my case of a quot;u1 = u2quot; null hypothesis. I am
gt; just (still) surprised that I must enter all the formulas. I would
gt; think this is a not-uncommon requirement, albeit perhaps also
gt; not necessarily the most common one either.
gt;
gt; But according to my (remedial) stat book, the complete numerator
gt; for the t-score is (m1 - m2) - (u1 - u2), where m1 and m2
gt; are the sample means, and u1 and u2 are the population
gt; means. That is the genesis of quot;deltaquot; on the Excel help page.
gt; Of course, we do not know u1 and u2. So it is unclear how I
gt; would use that formula for a quot;u1 lt;= u2quot; null hypothesis.
gt;
gt; Oh well, that question is better discussed in a stat newsgroup.
gt; All I was asking here was what Excel function did the job. I
gt; am hearing a resounding quot;none!quot; :-(.
gt;
quot; wrote:

gt; If I had the raw data, I wouldn't be asking the question, now
gt; would I?!

Your tone is heartwarming after I went to all that trouble for you. Your
previous post said that you didn't understand Excel's terminology. That part
of my post was included to relate Excel's terminology to TI83 terminlology.

gt; Oh well, that question is better discussed in a stat newsgroup.
gt; All I was asking here was what Excel function did the job. I
gt; am hearing a resounding quot;none!quot; :-(.

As you wish. FYI, thus far you have been in discussion here with two PhD
statisticians.

gt; I am interested in using the BetaDist() function. But in my
gt; news reader, there is a character before quot;df/2quot; which looks
gt; like a box, usually indicating an unsupported special character.
gt; I'm not sure it reproduced in the citation above.
gt;
gt; My question: is it simply quot;df/2quot;, or is it something else?
gt;
gt; If the latter, please spell it out in English, since not all news
gt; readers support all character sets and special characters.

The unsupported character was a space. It became a box because it was in
the Symbol font in my original document. I changed the other symbol
characters from a greek nu to quot;dfquot;, but my browser did not complain about the
space ... If you implement both formulas for vaious test cases with integer
df, you will see that the formula as received is correct, with the difference
being that the BetaDist formula is not restricted to integer df.

Jerry

quot;Jerry W. Lewisquot; wrote:
gt; quot; wrote:
gt; gt; If I had the raw data, I wouldn't be asking the question,
gt; gt; now would I?!
gt;
gt; Your tone is heartwarming after I went to all that trouble
gt; for you.

Sorry. I also go to a lot of trouble to try to specify my question
precisely and narrowly so as to focus the discussion. But I see
now what you were trying to do. I over-reacted.

gt; gt; Oh well, that question is better discussed in a stat newsgroup.
gt; [....]
gt; As you wish. FYI, thus far you have been in discussion here
gt; with two PhD statisticians.

Great! Then I am content to keep the discussion here, where
the context should already be clear.

Previously you wrote:
gt; TDist(x,df,2) = BetaDist(df/(df x²), df/2, 0.5)

What would be the BetaDist() equivalent for TDist(x,df,1)?
Or doesn't the question make sense to ask?

Finally, I confess that all I know about the beta distribution
is what I read in wikipedia and similar web pages.

Based on the wikipedia plots and my vague recollection of
the t-distribution plots, shouldn't alpha (df/2) be less than
beta (0.5) in order to get a curve similar to the t-distribution?quot; wrote:

gt; Previously you wrote:
gt; gt; TDist(x,df,2) = BetaDist(df/(df x²), df/2, 0.5)
gt;
gt; What would be the BetaDist() equivalent for TDist(x,df,1)?
gt; Or doesn't the question make sense to ask?

The t distribution is symmetric about zero, so
TDist(x,df,1) = TDist(x,df,2)/2 …

gt; Based on the wikipedia plots and my vague recollection of
gt; the t-distribution plots, shouldn't alpha (df/2) be less than
gt; beta (0.5) in order to get a curve similar to the t-distribution?

Again, the easiest way to verify the relationship between cumulative beta
and t distributions is to calculate both formulas for various {x,df} pairs
where df is an integer.

The first Wikipedia beta distribution formula implies that
f(x,a,b) = f(1-x,b,a)

Excel’s BetaDist function has no cumulative=false option, but you can
calculate the beta pdf directly from that Wikipedia formula as
= EXP(GAMMALN(a b)-GAMMALN(a)-GAMMALN(b))*x^(a-1)*(1-x)^(b-1)
= EXP(GAMMALN(a b)-GAMMALN(a)-GAMMALN(b) LN(x)*(a-1) LN(1-x)*(b-1))

Note that this beta pdf is not the t pdf. By the chain rule
mathworld.wolfram.com/ChainRule.html
you would multiply the Beta pdf corresponding to TDist(x,df,2) by
ABS(df)/(df x^2)^2
To get the t pdf.

Jerry