In this article:

How to Calculate Confidence Intervals in Google Sheets in 2024

How to Calculate Confidence Intervals in Google Sheets

Step 1: Organize Your Data

Ensure your sample data is organized in Google Sheets. You should know your sample's mean, standard deviation, and size. If you don't have the mean and standard deviation, you can calculate them using =AVERAGE(range) and =STDEV(range) functions respectively, where range is your data range.

Step 2: Determine Your Confidence Level

Decide on the confidence level you want to use for your interval (e.g., 90%, 95%, 99%). The confidence level reflects how sure you are that the true mean falls within the calculated interval. Remember, a 95% confidence level has an alpha of 0.05 (1 - 0.95).

Step 3: Use the CONFIDENCE.T Function

With your data organized and your confidence level chosen, you can now use the CONFIDENCE.T function. The syntax is:

=CONFIDENCE.T(alpha, standard_dev, size)

Where:

  • alpha: The significance level (1 - confidence level). For a 95% confidence level, alpha is 0.05.
  • standard_dev: The standard deviation of your sample.
  • size: The sample size.

Step 4: Calculate the Confidence Interval

The CONFIDENCE.T function will return the margin of error. To find the confidence interval, you need to subtract and add this margin to the sample mean:

Lower bound: Sample Mean - Margin of Error

Upper bound: Sample Mean + Margin of Error

You can do this directly in your spreadsheet by referencing the cell containing the sample mean and the cell with the CONFIDENCE.T function result.

Example

Let's use actual numbers to calculate the confidence interval using the CONFIDENCE.T function in a simulated environment.

  • Sample mean (x̄): 75 inches (mean height of a sample of basketball players)
  • Standard deviation (s): 8 inches
  • Sample size (n): 25 players
  • Confidence level: 95% (which means alpha (α) = 1 - 0.95 = 0.05)

We have our sample mean in cell B1, our standard deviation in cell B2, and our sample size in cell B3, and we're calculating a 95% confidence interval (B4).

The syntax is: =CONFIDENCE.T(0.05, B2, B3)

confidence interval google sheets

Our margin of error (B6) would thus be 3.302237699.

We will then calculate the lower bound and upper bounds: 

Lower bound: =B1 - B6

Upper bound: =B1 + B6

google sheets confidence interval

The margin of error calculated is approximately 3.30 inches. Therefore, the confidence interval for the true population mean height of basketball players, with 95% confidence, is between 71.70 inches and 78.30 inches.

This means we can be 95% confident that the true average height of the population of basketball players falls within this range.

We hope that this article has helped you and given you a better understanding of how to calculate confidence intervals in Google Sheets. If you enjoyed this article, you might also like our articles on how to do a sensitivity analysis in Google Sheets and how to assign a task in Google Sheets.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started