Calculate Power Level in Excel for 2 sample proportional test I have a need to calculate statistical power (the chance of making a Type II error) within Excel for a 2 sample proportional Z test.
Here's a example to better explain. Say I have two unequal samples n1 and n2. Within each sample, I have a number of individuals who have performed a specific conversion event. Lets call these converting individuals x1 and x2.
n1=6500  n2=6000  x1=88  x2=50
From these numbers I can calculated two conversion rates (p1 and p2), and using an alpha of 5% on a two tailed unpooled test, get my Z-score and p value. Again, all within Excel.
p1 = x1/n1 = 0.0135
p2 = x2/n2 = 0.0083
std error = SQRT(p1*(1-p1)/n1 + p2*(1-p2)/n2) = 0.0019
observed difference of means = p2-p1 = 0.0052
Z-score= observed difference of means/std error = 2.8097
p-value = 2*NORM.S.DIST(-ABS(Z-score),TRUE) = 0.005
As p is less that my alpha, I know my results are significant. Great! From there I can also calculate my Effect Size (the magnitude of difference between my two groups) using Cohen's H via the formula below.
Cohen's H=2*(ASIN(SQRT(p1))-ASIN(SQRT(p2))) = 0.0504
And because my Cohen's H value is less than .2, I know I am detecting a very small difference between these two groups.
Lastly, I need to demonstrate the chance of not making a Type II error (statistical power). This is where I am stuck. Using R (and the "pwr" package), this would be something like this with a result of 80.38%:
if(!"pwr" %in% installed.packages()){install.packages("pwr")} library(pwr) 
pwr.2p2n.test(h = 0.0504, n1 = 6500, n2 = 6000, sig.level = 0.05, alternative = 'two.sided')

Here, n1 and n2 are my sample means, and h is my Effect Size (Cohen's H), but as mentioned, I cannot replicate this 80.38% number with an Excel formula.
 A: Some Background
What you want to do is a post hoc power analysis using the observed estimates, which means you use the your estimators as "true" parameters and calculate the probability of obtaining a significant result from this distribution.
This is a widely used but also somewhat questionable practice.  For the critic here's a blog post by Andrew Gelman, who's a fairly famous statistics professor:
https://statmodeling.stat.columbia.edu/2018/09/24/dont-calculate-post-hoc-power-using-observed-estimate-effect-size/
Here is the published Letter: https://journals.lww.com/annalsofsurgery/Citation/2019/01000/Don_t_Calculate_Post_hoc_Power_Using_Observed.46.aspx
There also exist some further follow ups on that.
Your Problem
First of i won't use Excel formulas, but the only thing you need is $\Phi(x)$, which is the cumulative distribution function of the standard normal distribution, which you already used and its inverse $\Phi^{-1}(p)$.
That being out of the way: your p-value calculation and the pwr::pwr.2p2n.test function use slightly different convergences towards a standard normal test statistic.
For the p-value you used what is essentially a Wald-Test(https://en.wikipedia.org/wiki/Binomial_proportion_confidence_interval#Normal_approximation_interval_or_Wald_interval), while the R-function with Cohen's h builds on on $\arcsin$ https://en.wikipedia.org/wiki/Binomial_proportion_confidence_interval#Arcsine_transformation
Power Wald-Test
For the Power of your Wald-Test we assume that the observed $Z$-statisic of $\hat{z} = 2.8097$ is the true parameter so for any repetition of the experiment the new $Z$-statistic $\hat Z$ will be distributed according to $\mathcal{N}(\hat{z}, 1)$ and it will be counted as significant if $|\hat Z| > \Phi^{-1}(1 - \alpha/2) = 1.959964$, for $\alpha = 5\%$ in a tow sided test. Calculating that is straight forward, but don't forget the small probability, that you come out the other side and get a significant result of opposite sign: $$
P(|\hat Z| > 1.96) = 1 - \Phi(1.96 - \hat z) + \Phi(- 1.96 - \hat z) = 0.802265
$$
Pretty close to your result...
Power with Cohen's h
Basically the same but from the Wikipedia link we can learn, that $\mathrm{VAR}[\arcsin(\sqrt{\frac{x}{n}})] \approx \frac{1}{4n}$, from which we can conclude that the variance of Cohen's h is approximated by $\frac{1}{n_1 + n_2}$. Now the new $Z$-statistic is
$$
\hat z = \frac{0.0504}{\sqrt{\frac{1}{n_1 + n_2}}} = 2.815191
$$
This is slightly bigger, so the Power will be slighter higher
$$
P(|\hat Z| > 1.96) = 1 - \Phi(1.96 - \hat z) + \Phi(- 1.96 - \hat z) = 0.8037881
$$
Perfection!
The calculations in R
I don't have Excel at home, but it should be straight forward to translate these over. pnorm is $\Phi$ and qnorm is $\Phi^{-1}$
qnorm(0.975)

pnorm(1.959964, mean = 2.8097)
1 - pnorm(1.959964, mean = 2.8097) + pnorm(-1.959964, mean = 2.8097)

n1=6500;  n2=6000;  x1=88;  x2=50;

p1 = x1/n1
p2 = x2/n2

h <- (asin(sqrt(p1))-asin(sqrt(p2)))
sd_h <- sqrt((1/(4*n1)) + (1/(4*n2)))
h/sd_h
0.0504/2/sd_h
1 - pnorm(1.959964, mean =0.0504/2/sd_h) + pnorm(-1.959964, mean =0.0504/2/sd_h)

