![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart.png\")
\n<\/div>\nClearly, some offices outperform others: conversion rates range from 57.6% down to 17.6%. It is also apparent that the number of leads varies widely across offices: from 33 leads in “Midtown” to 706 leads in “Astoria.”<\/p>\n
Most people would stop here, but there is a straightforward way to use the concept of a “confidence interval” or “standard deviation (SD)” to reveal something that isn’t immediately obvious.<\/b><\/p>\nEstimating sample size and SD<\/h2>\n
Because the number of leads differs dramatically across offices (“Midtown” and “Astoria” differ by a factor of nearly 22), our confidence in the calculated conversion rate will not be uniform. For “Astoria,” the 36.1% figure is based on a sample of 706 leads and can be considered quite reliable. For “Midtown,” the 57.6% result comes from a small sample of just 33 leads, so there is no guarantee that, given their own 706 leads over time, they would sustain the same conversion rate.<\/p>\n
Naturally, we need to estimate the confidence interval for each sales office based on the number of leads — that is, the sample size.<\/p>\n
Here is the familiar formula for the standard deviation (SD), or σ<\/i>:<\/p>\n where p<\/i> is the conversion rate and n<\/i> is the number of leads.<\/p>\n We compute it in column E:<\/p>\n The resulting standard deviation shows the margin of error in the conversion estimate. As expected, it is larger where the sample is smaller. The less data we have, the less reliable the calculated result, and the less confident we can be in our assessment of the corresponding office’s performance.<\/p>\n Let us extend our table with the lower and upper bounds of a 90% confidence interval. In other words, we estimate the range of conversion rates for each office such that there is a 90% probability the true<\/i> conversion rate falls within that range.<\/p>\n Knowing that the bounds of a 90% confidence interval lie at ±1.645 SD, we subtract and add 1.645 SD to obtain the lower and upper bounds, respectively. For “Astoria,” this tells us that the true<\/i> conversion rate lies between 33.1% and 39.1%. (One time out of ten it may still fall outside our interval, but nine times out of ten our estimate holds.)<\/p>\n In Excel 2013 we can use a “stock chart,”<\/b> substituting the upper and lower confidence interval bounds for the high<\/i><\/b> and low prices,<\/i><\/b> and the conversion rate calculated earlier for the close price:<\/i><\/b><\/p>\n The enhanced chart does not change the conclusions we drew at the outset. However, it subtly reminds an observant manager that the reported conversion rates are not final<\/b> — and are especially “not final” where the confidence bounds are wider.<\/p>\n “Midtown” appears to have outperformed “SoHo”, but while “SoHo” strong result is reliable thanks to its narrow interval, the result for “Midtown” is highly approximate. Definitive conclusions can therefore only be drawn for some of the offices; for the rest, more data is needed, and until then their rankings should be considered preliminary — or, as noted above, not final.<\/p>\n",
"summary": "Not all conversion rates are created equal. A 57% rate from 33 leads is not the same as 36% from 706. Ivan Baldin reflects on how to add confidence interval bounds to your sales conversion charts using standard deviation and Excel stock chart candlesticks.",
"date_published": "2021-05-25T02:13:00+03:00",
"date_modified": "2026-04-12T03:24:52+03:00",
"tags": [
"analytics",
"conversion",
"diagrams and charts",
"Excel",
"sales funnel",
"statistics"
],
"image": "https:\/\/ivanbaldin.blog\/pictures\/conversion_simple.png",
"_date_published_rfc2822": "Tue, 25 May 2021 02:13:00 +0300",
"_rss_guid_is_permalink": "false",
"_rss_guid": "1",
"_e2_data": {
"is_favourite": false,
"links_required": [],
"og_images": [
"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple.png",
"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart.png",
"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn.png",
"https:\/\/ivanbaldin.blog\/pictures\/conversion_SD.png",
"https:\/\/ivanbaldin.blog\/pictures\/conversion_SD_max-min.png",
"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart_type.png",
"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart_modified.png"
]
}
},
{
"id": "2",
"url": "https:\/\/ivanbaldin.blog\/all\/wilson-score-confidence-interval-for-binomial-distribution\/",
"title": "Wilson Score Confidence Interval for Binomial Distribution",
"content_html": " You listen to 10 random call center calls. In 4 of them, the operator never asks the client where they heard about you from. So now you have a number: 40% of calls are missing the advertising source. That’s an issue. You bring it to your manager, maybe flag it in a report.<\/p>\n But here’s the thing. Is it really 40%?<\/p>\n 10 calls is not a lot. You picked them at random, and randomness is fickle. The true<\/i> failure rate could be 20%. It could be 60%. You just don’t know — and the small sample is the reason.<\/p>\n There is, of course, a standard way to express this uncertainty. You calculate the SD of the binomial distribution:<\/p>\n You get SD = 15.49%. Then you build a 90% confidence interval: 40%±1.645 SD, which gives you a range from 14.52% to 65.48%.<\/p>\n That’s a huge spread. But at least now you’re honest about it: with 90% probability, the true failure rate lies somewhere in that range.<\/p>\n The 40% figure is just your best guess, not a fact.<\/p>\n So far, so good.<\/p>\n Except there’s a catch.<\/p>\n The ±1.645 SD<\/i> approach works well enough — until it doesn’t. When your sample is small or your proportion is extreme (very close to 0% or 100%), the formula starts to wobble. There’s a simple rule of thumb: if np<\/i> < 10, you’re in trouble. In our case, np<\/i> = 4. We’re in trouble.<\/p>\n Think of it this way. If “tails” came up fewer than 10 times, either you haven’t flipped the coin enough, or you’re dealing with a biased coin that rarely lands on tails. Either way, the standard formula isn’t built for this.<\/p>\n Many statisticians consider the Wilson method<\/b> to be the most reliable approach for estimating confidence intervals for proportions <...>. This method not only handles very small and very large proportions, but also works well with small sample sizes.<\/i><\/p>\n<\/blockquote>\n The method was proposed in 1927. It has been around for nearly a century. And yet most analysts still reach for ±1.645 SD<\/i> by default.<\/p>\n The lower and upper bounds of the confidence interval p = 1–α\/2<\/i> are computed using the following formulas:<\/p>\n where p<\/b><\/i> is the observed proportion, N<\/b><\/i> is the number of trials, and z<\/b><\/i> is the z-<\/i>score (e. g., 1.960 for a 95% confidence interval, or 1.645 for 90%).<\/p>\n Let’s revisit our 10 calls. The standard method gave us a confidence interval from 14.52% to 65.48%. Now let’s apply Wilson.<\/p>\n The bounds tighten: with 90% probability, the true failure rate lies between 19.42% and 64.84%. The effective SD drops to 13.80%.<\/p>\n The difference may look subtle, but notice what happened at the lower end: it shifted from 14.52% to 19.42%. That’s not a rounding error. In a real business decision — say, whether to retrain your call center team — that shift matters.<\/p>\n And this is the point. When your data is scarce, every percentage point of precision counts. The Wilson score interval doesn’t give you more data. It just makes better use of the data you already have.<\/p>\n Calculator<\/b><\/a> in Google Sheets (menu “File” → “Make a copy”).<\/p>\n Online calculator<\/a> for a 95% confidence interval Here’s a puzzle. Two employees get rated by their clients on a 1-to-5 scale. Their average scores are identical:<\/p>\n Johnson — 5, 5, 5, 5, 5, 2, 2, 2, 2, 2. Average = 3.50.<\/b> A tie. Except — it doesn’t feel like a tie, does it? Johnson has ten ratings, all over the map. Miller has four, tightly clustered. Shouldn’t that matter?<\/p>\n It does. And a fascinating article on EvanMiller.org, “Ranking Items With Star Ratings,”<\/a> shows exactly how. (Judging by the author’s tone, the whole business of ranking things by ratings is far thornier than it looks<\/a>, and he keeps coming back to it.)<\/p>\n The core problem is simple: a plain average ignores how many votes went into it. A 4.8 rating from 300 reviews is not the same as a 4.8 from 6 reviews. The first one you can trust. The second one — who knows.<\/p>\n The solution comes from Bayesian statistics<\/a> (whatever that might mean in this context exactly), et voilà:<\/p>\n Johnson — 2.72<\/b> Somehow, either the tighter spread of Miller’s scores (SD of 0.58 vs. 1.58), or the smaller number of ratings (4 vs. 10), or both together refined<\/b> the average, giving Miller the edge by a few hundredths. The tie is broken.<\/p>\n Brace yourself. It’s gonna sting a little.<\/p>\n We assume<\/a> there are K<\/b><\/i> possible ratings, indexed by k,<\/b><\/i> where each rating is worth sk<\/sub><\/b><\/i> points (“1” = 1 point, “2” = 2 points, and so on). Given N<\/b><\/i> total ratings for each item, with nk<\/sub><\/b><\/i> ratings for each k,<\/b><\/i> we can compute the item’s score using the following formula:<\/p>\n Where zα\/2<\/sub><\/b><\/i> is the 1−α\/2<\/b><\/i> quantile of the normal distribution. The resulting score is the lower bound of a normal approximation to the Bayesian confidence interval for the average rating. Setting, say, α=0.10 (z=1.65), the score S<\/b><\/i> means that in 95% of cases, the item’s true average rating will be above S<\/b><\/i>.<\/p>\n In plain English: the “advanced” calculation gives you a conservative estimate of what the average rating would converge to if you had more data. And — as we saw with Johnson and Miller — it lets you rank items even when their plain averages are identical.<\/p>\n I took 2,000 ratings on a 1-to-5 scale across a set of regional sales offices and computed both the plain average and the “advanced” score for each one.<\/p>\n Flushing dropped from 2nd to 4th place. The likely reason: its 66 ratings don’t inspire enough confidence that the average is truly that high. In 90% of scenarios, its predicted rating is no better than 4.55 — roughly 4th place territory.<\/p>\n Tribeca, on the other hand, was formally 13th. But thanks to a solid base of 249 ratings, the model predicts — with 90% confidence — an average of at least 4.4, which bumps it up to 10th.<\/p>\n My impression is that the formula works most convincingly for short rating scales, like the 1-to-5 used here.<\/p>\n In any case, here is a Google Sheets workbook<\/a> — it should handle rating scales up to 100, import up to 10,000 rows of individual ratings, and lets you adjust the confidence level (90% in our example).<\/p>\n https:\/\/www.evanmiller.org\/ranking-items-with-star-ratings.html<\/a><\/p>\n Advanced rating calculator<\/a> in Google Sheets<\/p>\n",
"summary": "Two employees, same average rating — but should we trust 10 scattered scores the same as 4 consistent ones? Inspired by Evan Miller's work on ranking items with star ratings, this post walks through a Bayesian approach that accounts for both spread and sample size.",
"date_published": "2019-09-21T15:59:00+03:00",
"date_modified": "2026-04-12T03:29:09+03:00",
"tags": [
"Bayes",
"equations",
"Google Sheets",
"ratings",
"statistics"
],
"image": "https:\/\/ivanbaldin.blog\/pictures\/ratings00.png",
"_date_published_rfc2822": "Sat, 21 Sep 2019 15:59:00 +0300",
"_rss_guid_is_permalink": "false",
"_rss_guid": "3",
"_e2_data": {
"is_favourite": false,
"links_required": [
"jquery\/jquery.js",
"fotorama\/fotorama.css",
"fotorama\/fotorama.js"
],
"og_images": [
"https:\/\/ivanbaldin.blog\/pictures\/ratings00.png",
"https:\/\/ivanbaldin.blog\/pictures\/ratings01.png",
"https:\/\/ivanbaldin.blog\/pictures\/ratings02.png"
]
}
},
{
"id": "29",
"url": "https:\/\/ivanbaldin.blog\/all\/4-for-4-against-confidence-interval\/",
"title": "4 for, 4 against: the confidence interval hiding behind a 50\/50 split",
"content_html": " I was recently listening to Vesti FM (a Russian national talk-radio station), where the hosts were going over the results of Russia’s nationwide election day on September 8<\/a>.<\/p>\n One remark from the host caught my attention (at 01:45:05<\/a>):<\/p>\n By the way, listeners from Khabarovsk Krai are texting in, and going by the number of texts, they’re splitting about 50\/50. Half of them think they made a positive choice, half think things got worse and it was a negative choice. This isn’t a public opinion survey, obviously. I’m just seeing eight or ten texts come in, and they split roughly down the middle. Still, it’s interesting.<\/i><\/p>\n<\/blockquote>\n Credit where it’s due: the disclaimer is exactly right, this is not a survey. But the question is still worth asking. What can you actually say about how opinion splits in reality<\/i> when all you have in hand is 4 texts in favor and 4 against? How much does a 50\/50 result on a sample of 8 tell you about the same split in the population?<\/i><\/p>\n Let’s reproduce the setup in a spreadsheet:<\/p>\n That day, 4 listeners texted in favor and 4 texted against. But no two days are the same. Today it’s one set of listeners, tomorrow it’s another, and the score could just as easily come out 3 to 5, 5 to 3, 2 to 6, or 7 to 1 — any combination is possible in principle. If we assume the audience really is evenly divided, then 4-to-4, 3-to-5 and 5-to-3 are somewhat more likely, while 8-to-0 or 1-to-7 are much less likely.<\/p>\n Technically, this is a binomial<\/i> setup: out of 8 texts we expect<\/i> 4 in favor, but we don’t know in advance how many we’ll actually get. If the probability that any given text is “for” is 50% (let’s assume exactly half the audience is in favor), then the standard error (SE, often written σ)<\/i><\/b> of the observed share is:<\/p>\n where p<\/i><\/b> = 50% and n<\/i><\/b> = 8.<\/p>\n Let’s compute it:<\/p>\n So if the probability of a “for” text is 50%, then on a sample of 8 texts the standard error comes out to 17.68%<\/b>.<\/p>\n What does that mean in practice?<\/p>\n It means that because the sample (8 texts) is tiny, the amount of randomness baked into the “4 for, 4 against” result is enormous, and we cannot confidently claim a strict 50\/50 split across the entire Vesti FM audience. The only thing we can state with any confidence is that the true share of “for” votes lies somewhere within an interval<\/i> around 50%. And the wider we want that interval’s reliability to be, the wider the interval itself gets.<\/p>\n Say we want to be 90% confident in our interval — in other words, we accept being wrong 1 time in 10. Since the normal distribution is a decent approximation of the binomial once n<\/i> is large enough, the interval works out to 50% ± 1.645 SE.<\/b><\/p>\n That’s an easy calculation in Google Sheets:<\/p>\n The true share of “for” votes lies in the interval 50% ± 29.08%, that is, anywhere from 20.92% to 79.08%.<\/b> Roughly like this:<\/p>\n So we’re nowhere near being able to say that “listeners are splitting about 50\/50.” At best — and even then we’re allowing a 10% chance of being wrong — all we can say is somewhere between 21% and 79%.<\/b><\/p>\n That said, the p<\/i> ± 1.645SE<\/i><\/b> interval is itself a crude approximation, and it’s at its worst exactly where we’re using it: small samples. There are more sophisticated and somewhat more accurate ways to set the bounds.<\/p>\n Running the numbers through the Wolfram Alpha calculator<\/a> gives these bounds:<\/p>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_SD.png\")
\n<\/div>\nComputing the bounds of a 90% confidence interval<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_SD_max-min.png\")
\n<\/div>\nEnhancing the chart with “candlesticks”<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart_type.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/conversion_simple_chart_modified.png\")
\n<\/div>\nThe trouble with small samples<\/h2>\n
\n<\/div>\nWhen the standard method breaks down<\/h2>\n
\n
The Wilson method<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/wilson01.png\")
\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/wilson02.png\")
\n<\/div>\nBack to the call center<\/h2>\n
See also:<\/h2>\n
\nCalculator<\/a> on WolframAlpha.com
\nBinomial confidence intervals and contingency tests<\/a> (pp. 4–5)
\nhttps:\/\/influentialpoints.com\/Training\/confidence_intervals_of_proportions.htm#wils<\/a>
\nWilson score interval<\/a> on Wikipedia<\/p>\n",
"summary": "The standard ±1.645 SD approach to binomial distribution confidence intervals breaks down with small samples or extreme proportions. The Wilson score interval, proposed in 1927, handles both cases gracefully. This post walks through a practical call center QA example with formulas and a ready-made calculator.",
"date_published": "2020-04-01T17:54:00+03:00",
"date_modified": "2026-04-12T22:51:34+03:00",
"tags": [
"analytics",
"binomial",
"statistics"
],
"image": "https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn.png",
"_date_published_rfc2822": "Wed, 01 Apr 2020 17:54:00 +0300",
"_rss_guid_is_permalink": "false",
"_rss_guid": "2",
"_e2_data": {
"is_favourite": false,
"links_required": [
"jquery\/jquery.js",
"fotorama\/fotorama.css",
"fotorama\/fotorama.js"
],
"og_images": [
"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn.png",
"https:\/\/ivanbaldin.blog\/pictures\/wilson01.png",
"https:\/\/ivanbaldin.blog\/pictures\/wilson02.png"
]
}
},
{
"id": "3",
"url": "https:\/\/ivanbaldin.blog\/all\/a-smarter-way-to-calculate-ratings\/",
"title": "A Smarter Way to Calculate Ratings",
"content_html": "
\nMiller — 4, 4, 3, 3. Average = 3.50.<\/b><\/p>\n
\nMiller — 2.63<\/b><\/p>\nThe formula behind the advanced rating<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/ratings00.png\")
\n<\/div>\nThe advanced rating in action<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/ratings01.png\")
\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/ratings02.png\")
\n<\/div>\nSee also<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski05[1].JPG\")
\n<\/div>\n\n
Working it out in Google Sheets<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski00.PNG\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn[1].png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski01.PNG\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski03.PNG\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski02[1].PNG\")
\n<\/div>\nSharpening the estimate<\/h2>\n
![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/4smski04[1].png\")
\n<\/div>\n![\""the](\"https:\/\/ivanbaldin.blog\/pictures\/vc_quote.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/1000-1000.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/sqrt.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn02.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/sd_difference.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/z-score.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/kupivip_robot_05.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/p-value.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/goal_seek_01.png\")
\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/goal_seek_02.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/every5th01.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/every5th02.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/every5th03.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/poisson01.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/create-a-1080-1080-pixels-flat-style-cartoon-showi-(1).png\"){kind=tracking}
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/samplesize00.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/samplesize03.png\")
\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/norm_dist.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/CodeCogsEqn-1.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/sd01.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/sd02.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/001.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/002.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/003.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel01.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_9.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_binom.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_0763.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_1-9.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_binom_d3c3_whit.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_binom_whit.png\")
\n<\/div>\n![\"\"](\"https:\/\/ivanbaldin.blog\/pictures\/funnel_56-67.png\")
\n<\/div>\n