Binomial Proportions: Two-Sample Test

[Guest]

Is there a function in Excel for conducting the Two-Sample Test for Binomial
Proportions (normal theory method)?

 

[Stan Brown]

Is there a function in Excel for conducting the Two-Sample Test for Binomial
Proportions (normal theory method)?

I couldn't find one directly.

However, you can compute the z statistic and then use it to run a
ZTEST. (Usual caveats about Excel's normal calculations apply.)

Assume P1 and P2 contain the proportion of "yes" responses in each
sample and N1 and N2 contain the size of each sample. Then compute
the pooled sample proportion in P3 as

( P1*N1 + P2*N2 ) / ( N1 + N2 )

and the z statistic (for a null hypothesis that the two population
proportions are equal) is

(P1-P2) / SQRT( P3 * (1-P3) * (1/N1 + 1/N2) )

 

[Guest]

I've used the function BINOMDIST before. I'm not sure if that's what you
need.

I created a table for the Binomial Distribution with a probability of R or
FEWER occurances with N (A5) trials and R (B5) successes. The probability of
occurrances of each trial is C4 in my spreadsheet.

Probability of R or fewer occurrances.
=BINOMDIST($B5,$A5,C$4,TRUE)

THe equation for Probability > R is

=1-BINOMDIST($B5,$A5,C$4,TRUE)

Probability Exactly R

=BINOMDIST($B5,$A5,C$4,FALSE)

Probability R or Greater is

Probability Exactly R + Probability > R

 

[Guest]

Many thanks!
Ken Silver

I couldn't find one directly.

However, you can compute the z statistic and then use it to run a
ZTEST. (Usual caveats about Excel's normal calculations apply.)

Assume P1 and P2 contain the proportion of "yes" responses in each
sample and N1 and N2 contain the size of each sample. Then compute
the pooled sample proportion in P3 as

( P1*N1 + P2*N2 ) / ( N1 + N2 )

and the z statistic (for a null hypothesis that the two population
proportions are equal) is

(P1-P2) / SQRT( P3 * (1-P3) * (1/N1 + 1/N2) )

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/
"I feel a wave of morning sickness coming on, and I want to
be standing on your mother's grave when it hits."

 

[Guest]

Many thanks!
Ken Silver

I've used the function BINOMDIST before. I'm not sure if that's what you
need.

I created a table for the Binomial Distribution with a probability of R or
FEWER occurances with N (A5) trials and R (B5) successes. The probability of
occurrances of each trial is C4 in my spreadsheet.

Probability of R or fewer occurrances.
=BINOMDIST($B5,$A5,C$4,TRUE)

THe equation for Probability > R is

=1-BINOMDIST($B5,$A5,C$4,TRUE)

Probability Exactly R

=BINOMDIST($B5,$A5,C$4,FALSE)

Probability R or Greater is

Probability Exactly R + Probability > R