Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 33

Descriptive Statistics for Data Distribution

Recommended resources

Introduction

In our last lesson, you learned how to use pivot tables to summarize large datasets with functions like SUM and COUNT. This is a powerful way to get a high-level overview. However, to provide deeper insights for a client, you often need to understand not just the totals, but the nature of the data itself. Is the data clustered together, or is it widely spread out? Are there unusually high or low values (outliers) that might be skewing the picture?

This lesson will introduce you to descriptive statistics, a set of fundamental tools for answering these questions. We'll move beyond simple sums and learn how to calculate key metrics like the median, minimum, and maximum values. Mastering these concepts will allow you to describe the central tendency and distribution of your data, providing a much richer and more accurate analysis in your freelance projects.

1. Understanding the Core Concepts

Before we jump into the spreadsheet functions, it's important to understand what these statistical measures represent. The most common descriptive statistics are grouped into two categories: measures of central tendency (what's a "typical" value?) and measures of spread (how varied is the data?).

How Do I Use Mean, Median, Mode, and Standard ...

This article from Carleton College provides clear, concise definitions of the key statistical terms we'll be covering.

Please read the first two sections of the article: the introduction and the definitions. As you read, focus on grasping the difference between: Mean: The familiar "average." Median: The true middle value of a dataset. Mode: The most frequently occurring value. Range: The difference between the Maximum and Minimum values.

The distinction between mean and median is one of the most critical concepts in practical data analysis. The mean can be heavily influenced by a few extremely high or low values (outliers), while the median is not. For a freelance data researcher, knowing which one to report can be the difference between providing a misleading or an accurate insight.

Let's look at a classic example to illustrate this point.

How to read a histogram, min, max, median & mean

This article from Datawrapper has a great, simple explanation of why the median is often more useful than the mean.

Read the section titled "Mean and Median". Pay close attention to the salary example, which perfectly demonstrates how outliers can skew the mean and why the median can give a more truthful representation of the data.

2. Calculating Descriptive Statistics in a Spreadsheet

Now that you understand the concepts, let's see how to calculate these values in practice. Fortunately, spreadsheet applications like Google Sheets and Excel have simple, built-in functions for all of them. The function names are generally the same across both platforms.

The following video demonstrates how to calculate the most common descriptive statistics in Excel. The process and formulas are nearly identical in Google Sheets, so the skills are directly transferable.

Find Mean, Median, Mode, Standard Deviation, Variance, and Range in Excel Quickly and Easily

The video "Find Mean, Median, Mode, Standard Deviation, Variance, and Range in Excel Quickly and Easily" from the Stats4Everyone channel provides a great walkthrough.

Watch the following segments to learn the key functions. As you watch, notice the pattern: =[FUNCTION_NAME](data_range). Mean (Average): 01:00-01:51 (=AVERAGE) Median: 01:51-02:22 (=MEDIAN) Standard Deviation: 03:51-04:17 (=STDEV.S) Range (Min & Max): 04:45-05:23 (=MAX and =MIN) For Standard Deviation, the video mentions .S for sample and .P for population. In almost all data research tasks, you will be working with a sample of the total possible data, so you should use =STDEV.S().

Here is a summary of the key functions you've just seen:

Statistic Function Description
Mean =AVERAGE(range) Calculates the average of the numbers in a range.
Median =MEDIAN(range) Finds the middle number in a range of numbers.
Maximum =MAX(range) Returns the largest value in a range.
Minimum =MIN(range) Returns the smallest value in a range.
Standard Deviation =STDEV.S(range) Measures how dispersed the data is from the mean.
Mode =MODE(range) Returns the most frequently occurring value.
A typical summary of descriptive statistics in a spreadsheet. Each metric is calculated using a simple function that references the raw data column.

3. Hands-On Practice

It's time to apply what you've learned. We will use the same practice workbook from our previous lesson on pivot tables. This will help you see how you can perform different kinds of analysis on the same dataset.

If you no longer have the file, you can download it again from the "Pivot Table Practice Examples" resource in the previous lesson. Open the file in Google Sheets.

Practice Problem: Analyze Sales Amounts

  1. Navigate to the "Data" tab to see the raw data. We are interested in the Amount column.
  2. Create a new sheet by clicking the + icon at the bottom left. Name it "Statistics".
  3. In your "Statistics" sheet, set up labels for the calculations you will perform (e.g., in column A, type "Mean", "Median", "Max", "Min", "Std Dev").
  4. In the cell next to "Mean" (e.g., B1), type the formula to calculate the average of the "Amount" column. The formula will look something like this: =AVERAGE(Data!D2:D1001).
    • Data! tells the formula to look at the "Data" sheet.
    • D2:D1001 is the range of cells containing the sales amounts.
  5. Repeat this process for the Median, Max, Min, and Standard Deviation, using the appropriate functions.
  6. Once you are finished, check your answers below.
Click to see the correct answers
  • Mean: $474.33
  • Median: $370.00
  • Max: $1990.00
  • Min: $10.00
  • Standard Deviation: $428.18

Notice that the mean ($474.33) is significantly higher than the median ($370.00). What does this tell you? It suggests that there are some high-value sales that are pulling the average up. For a client report, presenting the median might give a better sense of a "typical" sale, while also noting the higher average indicates the presence of large, valuable transactions.

Conclusion

In this lesson, you've added a crucial set of tools to your data analysis toolkit. You moved beyond simple summarization and learned how to describe the fundamental characteristics of a dataset.

Here are the key takeaways:

  • Descriptive statistics reveal data distribution: Metrics like mean, median, min, and max tell you about the central point, spread, and range of your data.
  • Mean vs. Median is a critical choice: The mean is the mathematical average, but the median is often a more robust measure of central tendency when outliers are present.
  • Spreadsheet functions are your friends: Functions like =AVERAGE(), =MEDIAN(), =MAX(), =MIN(), and =STDEV.S() make these calculations quick and easy.

In our next lesson, we will build directly on this knowledge. Now that you understand the impact of outliers, you'll learn how to use sorting and conditional formatting to visually identify them in your dataset, making your data cleaning and analysis process even more efficient.