How to use Excel to calculate mean and standard deviation

If you’re working with statistical data, there are a number of scenarios in which averages like the data’s mean, as well as its standard deviation, can be quite useful. Of course, inputting tiresome math formulae into cells can’t be the only way to handle attain this data. Functions are key.

Solution A (mean value):

  1. Select a cell
  2. Open functions dropdown
  3. Select the average function
  4. Input the start and endpoint for your values
  5. Press enter
  6. Review your output

Solution B (standard deviation):

  1. Select a cell
  2. Open the functions dropdown
  3. Select more functions
  4. Select STDEV
  5. Input the start and endpoint for your values
  6. Select OK
  7. Review your output

In this post, we will be providing an image-guided walkthrough on attaining mean values and standard deviations within the Microsoft Excel application. This much will additionally be provided for Excel’s online counterpart. Please follow along.

Contents:

  1. Finding the mean value in Excel
  2. Finding the standard deviation in Excel
  3. End note

Finding the mean value in Excel

Step 1 – Open your Excel worksheet

  • Open the worksheet you’d like to calculate an average for, or if you’ve not yet got one: copy the sheet in use below.

Step 2 – Insert the average function

  • After selecting a cell to insert the function into, navigate to the Home tab. From the home tab select the Σ button and click Average from its dropdown.

Step 3 – Input the start and endpoint for your values

  • In this case our column of data starts at B2 and ends at B6. As such, for the function we have passed the parameter B2:B6. In doing so, we can tell Excel to select everything between (and including) those two points. After inputting your range, please press Enter to exit the cell editor mode.

Step 5 – Check your values

  • Have a look at your value and ensure you have an expectable output. If you don’t see a numeric output try the following:
    1. Ensure all the input data is of the Number type.
    2. Double check the functions were typed correctly.
    3. Increase the cell size incase your output value is potentially too big to fit.

Finding the standard deviation in Excel

Step 1 – Open your Excel worksheet

  • Open the worksheet you’d like to calculate a standard deviation for, or if you’ve not yet got one: copy the sheet in use below.

Step 2 – Open your Excel worksheet

  • First select a cell to insert a function into. Continue by selecting the Home tab from the tab ribbon. Lastly, open the function dropdown via the Σ button and select More functions.

Step 3 – Find and select the standard deviation function

  • Within the main scollable text field is a series of function names. Within it you will find the standard deviation function- under the name STDEV. Select said function, followed by the OK button at the bottom of the window.

Step 4 – Enter the selection mode

  • Click the displayed icon beside the Number1 text field. Alternatively, if you’re certain of where you data starts and ends you can simply enter the starting cell and end cell separated by a colon within the Number1 text field. In our case that would be B2:B6. Please note you should skip step 5 if you do this.

Step 5 – Make your selection

  • Select the start of your data and drag your selection down/across to its last value. After doing so, select the selection icon once again to return to the fullsize window.

Step 6 – Confirm your settings

  • This one is forunately quite simple. If you’re happy, select OK. If not, we’re sorry. Bad times pass. But click the button.

Step 7 – Confirm your settings

  • Have a look at your value and ensure you have an expectable output. If you don’t see a numeric output try the following:
    1. Ensure all the input data is of the Number type.
    2. Double check the functions were typed correctly.
    3. Increase the cell size incase your output value is potentially too big to fit.

End note

Recap

You now know that (as demonstrated) a range of value’s mean and standard diviation can be returned via the AVERAGE AND STDEV functions. These functions are accessed from the home tab via the Σ button on.

Farewell

As usual, this section is dedicated to thanking readers for their support. We’d be pleased to know you found this post useful and/or informative. If you have any more Office 365 related queries, please consult our related articles (listed below). However, if you have other queries, consider looking at the rest of the site. We’re frequently posting new stuff each week and may just have the guide or troubleshooting tips you need. Regardless, we wish you a good day/night. Farewell readers. 

Our related articles: 

  • Excel won’t allow insert row: Troubleshooting guide – Microsoft Excel offers tables as a means to structure our data and accelerate the process of adding additional data. Unfortunately, this does not always operate correctly- sometimes in such a way that we can’t find a cause. That much is true for your newfound inability to insert new rows (or perhaps even columns) to your tables. If you are looking for a guide on preventing row insertion failures, then this post is ideal. Here we will be providing a step-by-step guide with pictures for two solutions to this problem.
  • Excel: How to get day of week from date – If you’re working on scheduling or tracking data involving dates, you might want to include the day. Of course, that doesn’t sound particularly straightforward- at least not initially. Fortunately, it’s really quite simple. In this post we will be walking you through getting the day of week from a date in Excel. We hope you find this step-by-step guide useful.
  • Can’t click into cells in Excel: Troubleshooting tips – Microsoft Excel is powered by cells. Cells store values, functions and formulae we use within Excel. Due to this, a failure in their operation is detrimental to your experience with Excel. To make matters worse, this issue has more than once cause. Despite this, you needn’t worry, for we’ve means to resolve the issues you may be facing. In this post we’ll be guiding you on each of the solutions to each of the aforementioned problems- in which you may find yourself unable to click your cells. Each guide will provide additional assistance to readers via images at the end of each step. If you’re ready, follow along and let’s get started.
  • Excel isn’t returning the sum of cells: Troubleshooting guide – Calculation play a large part in may Excel user’s worksheets. Despite this, they are not excused from behaving unexpectadely. The SUM function in particular can be quite problematic — having multiple means to cease functioning correctly. In this post, we’ll be instructing you how to go about fixing each of the causes for SUM to operate incorrectly. First off, resolving a potential calculation configuration mistake, second updating a cell’s content type, and lastly, changing the Windows display language.

Jack Mitchell

Jack Mitchell has been the Operations manager at telecoms and MSP Optionbox for more than 4 years. He has played a crucial role in the company, from marketing to helpdesk, and ensures that the IT requirements of over 300 clients are continuously met. With his innate passion for technology and troubleshooting and a particular interest in Apple products, Jack now delivers the most comprehensive tech guides to make your life easier. You can connect with Jack on LinkedIn.

Recent Posts