How to Calculate Minimum Detectable Effect in Google Sheets

Key Takeaways

• Minimum Detectable Effect (MDE) defines the smallest improvement you want to detect with confidence in an A/B test.
• Aligning on MDE before launch helps teams avoid premature conclusions and keeps test timelines realistic.
• Your baseline conversion rate, desired uplift, and statistical power determine how long a test must run and how much traffic is needed.
• Statistical significance (typically 95%) protects against false positives, while statistical power (typically 80%) guards against false negatives.
• You can build a simple MDE calculator in Google Sheets using only a few formulas and no expensive tools required.
• Tests should be scoped to reflect your business constraints. If traffic is low, consider pre/post designs or larger effect sizes.
• Use MDE as a communication tool to set expectations with leadership and frame testing as a disciplined decision-making process.

What Is Minimum Detectable Effect (MDE) and Why It Matters

MDE in the Context of Business and Test Planning

When teams talk about A/B testing, they usually want to move fast. The test is launched, the early numbers look promising, and the question quickly becomes: “Can we ship this now?” That’s why Minimum Detectable Effect (MDE) is one of the most important guardrails in experimentation. MDE defines the smallest improvement in performance you want to detect with statistical confidence. MDE should serve as your goalpost for when you can responsibly make a decision.

In my experience leading high-velocity digital programs, I have learned to define MDE early and sell its value to stakeholders. Before a test launches, we align on our desired lift, how long it will take to detect it, and what thresholds constitute success. That way, when someone asks, “When can we move forward?” the answer is already documented: when we reach the minimum detectable effect with statistical significance. Skipping this conversation leads to tests getting called too early, only to backfire when a result fails to replicate.

Baseline Conversion Rates and Sample Size Realities

MDE is not calculated in a vacuum. You need to know your baseline conversion rate and then decide what change would be considered meaningful. I usually start with past performance data and sanity check that number against industry benchmarks. If your site converts at 2%, you might decide that a 10% lift (to 2.2%) is the minimum effect size worth pursuing.

Here’s where it gets tricky: the sample size required to detect small effects is often larger than teams expect. Smaller businesses frequently underestimate how much traffic or budget they need. A 1% improvement may sound small, but it could take months and thousands of visitors to validate. When we run into those limits, especially with lower-budget clients, we sometimes switch to pre/post tests instead of A/B testing. It is not perfect, but it reflects a core truth: testing is only valuable if it fits the reality of your traffic and timeline.

Testing velocity matters, but it only matters if it leads to smart decisions. MDE helps you scope the effort, define timelines, and protect your results from being misread. With a good handle on conversion rates and sample size, you can plan tests that are both ambitious and feasible.

The Role of Statistical Significance and Power in MDE

Significance Thresholds, P-Values, and False Positives

It is tempting to treat statistical terms like p-value or confidence level as purely academic, but in testing environments where real money is on the line, these choices carry weight. I generally default to a significance level of 0.05 (95% confidence) because that gives me confidence that my observed effect is not just a fluke. That threshold strikes a practical balance—tight enough to avoid most false positives, flexible enough to work within common traffic constraints.

That said, choosing the right threshold is ultimately a question of business risk. For lower-impact UI changes, you may be able to justify a more relaxed standard. But for high-impact pricing or conversion changes, I advise sticking with the classic 95%. That is the level where we can most confidently say, “If we run this test 100 times, we’d expect this result—or better—at least 95 times.”

When early test results look promising, stakeholders understandably want to call it. In those situations, I avoid sounding defensive or overly technical. Instead, I frame the decision around confidence: “We’re seeing positive trends, but we need more time to reach statistical significance. If we ship early, let’s mark that in Google Analytics and continue to monitor. If the numbers drift, we will revisit the test with full rigor.” That kind of diplomacy keeps momentum going while reinforcing the value of valid data.

Statistical Power and Avoiding Underpowered Tests

Beyond significance, the most overlooked concept in A/B testing is statistical power. Where significance tells you whether a result is real, power tells you whether you have enough data to detect a real result in the first place. A test with low power might fail because the test was underpowered and you simply didn’t give it enough runway.

In most scenarios, I aim for 80% power, meaning we have an 80% chance of detecting the true effect if one exists. Tools like Evan Miller’s calculator are great for this, but since you’re here, I recommend building the calculator directly in Google Sheets or Excel, which we will walk through next. These tools allow you to input your baseline conversion rate, desired MDE, and significance level to instantly get the sample size you need to reliably detect that change.

A properly powered test saves you from the frustration of a test that runs for weeks, generates excitement, and then gets dismissed as inconclusive. Building this into the test plan up front is a signal to leadership that you are not just chasing statistical language, you are creating a decision-making engine they can trust.

How to Calculate MDE in Google Sheets

Inputs You Need: Conversion Rate, Power, and Effect Size

Before building your MDE calculator, you need to gather three key inputs: your baseline conversion rate, your desired minimum detectable effect, and your preferred statistical assumptions, typically 95% significance level and 80% statistical power. These are the foundational elements in calculating your required sample size and determining whether your experiment is designed to reliably detect a meaningful change.

Let’s define each:

• Baseline Conversion Rate: Your control group’s current performance, e.g., 5% form fill or purchase rate.
• Minimum Detectable Effect (MDE): The smallest improvement you care about. For example, if your current rate is 5%, and you want to detect a 10% improvement, your desired rate would be 5.5% (5% x 1.10).
• Significance Level: Usually set to 0.05 (95% confidence). This is your tolerance for false positives.
• Power: Usually set to 0.80. This is your test’s ability to avoid false negatives.

These four factors determine how long your test needs to run and how much traffic (or spend) is required. With this information, you can use Google Sheets or Excel to calculate the sample size needed to detect your expected uplift.

Build Your Own MDE Calculator Using Google Sheets

You do not need expensive software to calculate your minimum detectable effect. Here’s how to do it in Google Sheets with common formulas and assumptions.

Step 1: Set Up Your Inputs

Step 2: Calculate the Variant Conversion Rate

=A1 * (1 + A2/100)
Put this in B5, label A5 “Variant Conversion Rate”

Step 3: Compute the Pooled Conversion Rate

=(A1 + B5) / 2
Put this in B6, label A6 “Pooled Conversion Rate”

Step 4: Compute Standard Error

=SQRT(2 * B6 * (1 - B6))
Put this in B7, label A7 “Standard Error”

Step 5: Compute Z-Scores

Z for alpha = NORMSINV(1 - A3/2)
Z for beta = NORMSINV(A4)
Place in B8 and B9, label as “Z Alpha” and “Z Beta” respectively

Step 6: Calculate Required Sample Size (Per Group)

=((B8 + B9)^2 * B7^2) / (A1 - B5)^2
Place in B10, label A10 “Sample Size Per Variant”

This gives you the minimum number of users required in each group (control and treatment) to detect your effect with statistical rigor. Multiply by 2 for total traffic. Adjust the lift (%) in A2 to explore tradeoffs; lower MDEs mean much higher sample sizes.

If you expect multiple variations, add more columns and repeat these steps to determine the impact of additional treatment variants. You may also want to layer in cost per visitor if you are estimating required media spend.

By building this calculator directly in Google Sheets, you give yourself, and your stakeholders, a transparent and customizable way to explore what it really takes to test effectively. It supports data-driven decisions grounded in both math and marketing reality.

Run Tests That Actually Teach You Something

The real value of experimentation is in learning from them with confidence. By calculating your Minimum Detectable Effect (MDE) before you begin, and pairing it with proper assumptions about statistical power, sample size, and conversion rates, you dramatically increase your odds of making informed decisions that stick.

Whether you are using Google Sheets, a dedicated testing tool, or a homegrown dashboard, the principles remain the same: plan ahead, align your team on what success looks like, and never let convenience override rigor. You just need to ask the right questions before you invest time, money, or credibility in a test.

With the framework and step-by-step guide outlined here, you now have the tools to run smarter, more effective experiments. Tests should teach you something real. Start with a proper MDE, and the rest will follow.

Pages

• Articles
• About James