Calculating Confidence Interval from a Histogram
An example of calculating confidence intervals from a histogram in a spreadsheet. This is useful for calculating error bars for a survey (e.g., NPS survey) in Excel or Google Sheets.
I needed a straightforward way to calculate error bars for a histogram distribution. I did not have access to the raw data but only to the histogram. There was not a built-in function to do this. One way of doing this would be to expand the histogram into “raw data” and use the built-in statistical function on that. That would be a messy and cumbersome way to do it.
In the end, I was able to calculate the error bars using array formulas. There is an example1 below using Google Sheets, however, you can use the same technique in Excel as well. (See array formulas for Excel and ArrayFormula for Google Sheets for reference.)
In this example, we have a survey with participants choosing a score between 0-10 (e.g., NPS survey).
As a final note, this would not work as well if histograms were calculated for bands of values and there is a one-to-one way to map the scores back to raw values. In those cases, the results would be an approximation.
Hope you will find this useful. Feel free to reach out with your feedback.