Solving formula for confidence intervals on two proportions I'm interested in comparing two independent proportions from two samples. The formula for doing so is available in the Internet (e.g. here). So far, so good.
But my main purpose is not to build a confidence interval. Rather I want to assess how big the second proportion has to be to be significant higher than the first proportion (given an alpha level and both sample sizes).
Suppose I conducted a study and found that 80% of 154 respondents used product X. Suppose further that I will do a second study with the same questions and I'm going for n=200 respondents. How big has this second proportion to be, to be signifcantly higher on a 90% alpha level?
Could anyone provide me with the specific formula which is solved for the second proportion? I know there are online calculators or GPower for exactly such purposes, but unfortunately I have to build up theses formula in Excel and thus have to put them in on my own.
(I originally posted on Stack Overflow, but was told that for statistics I might be better with posting my question here again, so sorry for crossposting!)
 A: If you did want to create a confidence interval for the difference of two proportions, there are better procedures than the one you are using: you might consider making use of the Wilson procedure. The Wikipedia article on binomial confidence intervals mentions this and several other possibilities, but does not show the method applied to differences in proportions: for that, consult a reference such as  Newcombe, Robert G., "Interval Estimation for the Difference Between Independent Proportions: Comparison of Eleven Methods," Statistics in Medicine, 17, 873-890 (1998).
However it appears that you don't want to create a confidence interval, but rather perform a hypothesis test. The formula you would want to use is a rearranged version of the given one. Let me write $p_a$ and $p_b$ for the proportions in groups A and B, and their sample sizes as $m$ and $n$ respectively. Then your test statistic is:
$$z = \frac{p_a - p_b}{\sqrt{\frac{p_a(1-p_a)}{m}+\frac{p_b(1-p_b)}{n}}}$$
Your result will be significant if this exceeds the upper critical value $z_\text{crit} = \Phi^{-1}(1 - \alpha/2)$ (where $\alpha$ is your significance level) or if it is below the lower critical value, which by symmetry of the normal distribution is $-z_\text{crit}$.  If you were interested in a 90% confidence interval, note that this is equivalent to setting $\alpha$ as 10%, not as 90%! These two sides of significance correspond to whether the proportions differ because $p_a$ exceeds $p_b$ (positive $z$) or vice versa (negative $z$). If you are interested in the critical values for $p_a$ which make this just significant, then you need to solve this equation as equal to $\pm z_\text{crit}$. Although your question mentions only the uppper critical value I will write as if you are interested in both possibilities - partly because this corresponds better to what you mentioned about the upper and lower bounds of a 90% confidence interval, and partly because two-sided testing is usually a good idea in general. The upper limit you get by testing with $\alpha$ as 10% is equivalent to the one-sided test you'd get if you tested at 5%, so no great variation in the method is required.
If you feel daunted by the task of algebraic rearrangement, one option is to use a computer algebra system to do the work for you. One freely available, open source product is Sage (which is actually rather more powerful than just a CAS). Rearranging to make one variable the subject, is essentially the same as solving the equation for that variable in terms of the other variables. A brief tutorial on how to solve equations symbolically in Sage is here. This would then give you a formula you can set up in Excel.
A paid-for product is Mathematica, but many basic features of Mathematica are freely available online at Wolfram Alpha. Go there and type:
solve z=(a-b)/sqrt((a(1-a))/m + (b(1-b))/n) for a

The output will be:

Here I have written $z_\text{crit}$ as $z$, $p_a$ as $a$ and $p_b$ as $b$ but I hope the meaning is still clear. Simply by changing $m$, $n$, $z$ and $b$ into appropriate cell references you can easily implement this formula in Excel. If cell A1 contains your level of signficance, $\alpha$, then then the cell you use for the critical $z$-score should contain the formula =NORM.S.INV(1-A1/2) so you should get the famous 1.96 (to two decimal places) if you set $\alpha$ at the 5% level, or 1.64 if you test at 10%.
Note that we actually find two solutions arise, corresponding to the two critical values for $a$, without having to check solve -z=(a-b)/sqrt((a(1-a))/m + (b(1-b))/n) for a for the case with negative $z$. It is clear that the first line of the rearrangement is must be:
$$z_\text{crit}^2 = \frac{(p_a - p_b)^2}{\frac{p_a(1-p_a)}{m}+\frac{p_b(1-p_b)}{n}}$$
Beyond this point it no longer matters whether we used the positive or negative value for $z_\text{crit}$. It's not so hard to see where Mathematica derives it solution from. Multiply by the denominator and we obtain:
$$z_\text{crit}^2 \left(\frac{p_a(1-p_a)}{m}+\frac{p_b(1-p_b)}{n}\right)= (p_a - p_b)^2$$
Then multiply by $mn$:
$$z_\text{crit}^2 \left(np_a(1-p_a) + mp_b(1-p_b)\right)= mn(p_a - p_b)^2$$
Once the brackets are multiplied out and terms are collected together, this will be a quadratic in $p_a$. The form of Mathematica's solutions were just the two roots to the quadratic formula but it's easier to let it deal with the simplification!
