Finding the Mean, Median, and Mode in Microsoft Excel

Updated on October 2, 2014

Calculating Measures of Central Tendency in Microsoft Excel

The mean, median, and mode are often referred to as measures of central tendency, meaning that they tell us information about the center of a distribution. In this hub, I will cover how to find these measures using Microsoft Excel. However, it is first useful to have a firm grasp of the mean, median, and mode before learning how to locate them using computer software. I will briefly overview these concepts. For more detailed information on what they are and how to calculate them by hand, check out this hub.

Finding the Mean: The "Center of Gravity" of a Distribution

The mean is simply the average of all values for a particular interval/ratio level variable. Using the data on age from the GSS 2008 subsample we could calculate the mean by adding together every value for age and dividing by the total number of cases (there are 20). The calculation would look like this:

(48+ 47+ 32+ 37+ 62 + 40 + 26+ 36 + 37 + 61 + 32 + 42 + 42 + 45 + 66 + 37 + 46 + 47 + 48 + 49) ÷ (20) = 43.95

With large data sets, a manual calculation of the mean is extremely tedious and with so many values there is a greater chance for error. Luckily, Microsoft Excel has a built in function designed to calculate the mean. To perform this computation in Excel, select all of the cells for which you would like to calculate the mean. In this example we would be selecting cells B2 to B21. Click and hold down your mouse in cell B2, then drag down the box that appears to cell B21. Once all data have been highlighted, click the downward arrow next to the “AutoSum” (∑) button and select “Average.” Once you click “Average,” the mean will appear directly below the selected data (in cell B22) by default. The computed average should also be 43.95.

Finding the Median: The Center Value of a Distribution

The median is the position that evenly divides the distribution in half. It is also the 50th percentile of a distribution. The median can be obtained for ordinal and interval-ratio level variables. If all data for a particular variable were arranged from highest to lowest, the median would be the value that falls directly in the center. If two values are at the center of a distribution (this often occurs in data sets with an even number of cases), simply take the average of the two values in the center.

To calculate the median of a variable in Excel, select the cell directly below the column containing the data you wish to analyze.

For example, let’s find the median for job satisfaction using the GSS 2008 subsample. Select the cell directly below the data column for the variable you are analyzing. In this case, select cell F22. Click on the drop-down menu arrow next to the “AutoSum” (∑) button on the “home” tab and select “more functions.” In the menu that appears change the category to “Statistical” and scroll to locate and select “median.” Press okay.

You will now be taken to a new window called “function arguments.” Excel has assumed by default that we wish to calculate the median for the values in the column directly about (cells F2 to F21). Once determining that range in “Number 1” is correct, click “OK.” If Excel does not make the correct assumption, you will have to enter the rage of cells manually before pressing “OK.” The median will now appear in the selected cell.

The median job satisfaction in the GSS 2008 subsample corresponds to the numerical value 3. Looking back to our coding scheme we can see that 1=very dissatisfied, 2= a little dissatisfied, 3= moderately satisfied, and 4=very satisfied. The median job satisfaction is therefore “moderately satisfied” (note: for categorical data that has been coded, the mean is always corresponds to category represented by a given numerical code, never to the numerical code itself).

  • The median can also be obtained from an existing frequency distribution including cumulative percentages by locating the value that falls at the 50th percentile.

Finding the Mode: The Most Common Value in the Distribution

The mode is the most frequently occurring value for a particular variable. The mode can be calculated for nominal, ordinal, or interval-ratio level data. One method of finding the mode is to count up the frequencies of each value for a particular variable and determining which value occurs most often. If we have already created a frequency distribution for a given variable, finding the mode is fairly simple. Let’s take another look at the frequency distribution we created for “Highest Rank (Symbols)” using the College Rating Survey data (located on the right). This frequency distribution was created in this previous hub.

Looking at the frequency distribution on the left, we can see that there are two most frequently occurring values for “Highest Rank (Symbols). Both Harvard and Yale have a frequency of 3. “Highest Rank (Symbols)” is therefore bimodal. Harvard and Yale are the modes. (Note: The mode always corresponds to the category/categories with the highest frequency/frequencies, but never to the frequency itself.)

  • The mode can also be obtained using built-in Excel functions. To do this, follow the same steps provided to calculate the median, but choose “mode” from the AutoSum drop-down selection “More Functions…” (make sure you have chosen the category “statistical.”

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)