A free Excel p-value significance calculator

Do you feel confident, punk?

Rik Higham
May 17, 2016 · 3 min read
Image for post
Image for post

A solid hypothesis, a power analysis, and statistical significance: the holy trinity of A/B testing.

A solid hypothesis bases your proposed change on insight, keeping you honest and grounding your experiment in quantitative or qualitative data. A power analysis gives you the minimum sample size required to be reasonably confident of identifying a change and minimising inherent statistical errors. Statistical significance helps you understand how compelling your experimental data is and whether you can reject the null hypothesis.

The result of an experiment is statistically significant if it is unlikely to occur by chance alone. By unlikely we mean that the p-value (which is calculated from the measured experimental data) is less than a threshold we define before the experiment begins, usually 5%. If the p-value is less than 5% there is less than a 5% probability that the observed difference between A and B is due to chance. These odds are conventionally accepted to be “good enough” to reject the null hypothesis and accept the B variant.

There are many online tools to calculate the p-value. However, when you are exporting data from Google Analytics or Mixpanel it becomes very repetitive entering the numbers manually into online calculators. Particularly if you are doing multiple calculations. So I made an Excel sheet that calculates the two-tailed p-value. You can download this for free from ExperimentationHub.com. It has a template based on GA/Mixpanel exports with a single cell formula for calculating the p-value that you can copy into your experiment analysis Excel sheet.

This is the formula:

Image for post
Image for post

Where conv. is the number of conversions and visit. is the number of visitors (or sessions) for variant A or B. NORM.S.DIST is an Excel function that returns the standard normal distribution.

For this spreadsheet configuration:

Image for post
Image for post

The p-value formula looks like this:

Image for post
Image for post


Want to improve your A/B tests?

Check out my new site: ExperimentationHub.com for free tools and advice to help you run better experiments and become more confident in your results.

Image for post
Image for post
ExperimentationHub.com

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store