Find averages with the SUBTOTAL function in Excel

The Subtotal function in Excel inserts the SUBTOTAL function into a database or list of related data and extracts specific information. The function is not limited to finding the sum or total for selected rows of data. It also finds the averages for each column or data field in your database.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

  1. Enter data in Excel
  2. Sort Excel data
  3. Find averages by region

Enter data in Excel

The first step to using the SUBTOTAL function in Excel is to enter your data into a table. Consider the following:

  • Fill in the details correctly. Errors caused by incorrect data entry are the cause of many data management problems.

  • Do not leave blank rows or columns when entering data. This means that no empty line is left between the column headings and the first row of data.

To follow this guide, enter the data shown in the image below into the cells of A1 before D12

Sort Excel data

Before you apply subtotals, your data must be grouped by the column of data you want to extract. This grouping is done using the Excel sort function.

This tutorial will show you the average number of orders per sales region. Here’s how to sort data by the Region column header.

  1. Drag selected cells A2 on the D12 to mark them.

  2. Select Facts

  3. Select To sort to open the Sort dialog box.

  4. Check the box next to My data has headers

  5. From the drop-down list, select Sort by Region

  6. Select Okay

The data in cells A3 through D12 is sorted alphabetically by the second column, Region. The data for the three sellers from the East region is listed first, then the north, then the south, then the west.

Find averages by region

After you’ve sorted the data correctly, use the SUBTOTAL function to find the average sales for each region. Syntax and its arguments to the function:

 = ИТОГО ( Function_Num ,  Ref1 ) 
  • Feature number : The SUBTOTAL function performs a number of different functions on numerical data. This field indicates the function to be performed. For the AVERAGE function, use number 1.
  • Ref1 : This argument marks the range of numbers used in the calculations of the SUBTOTAL function.

To use the AVERAGE SUBTOTAL function:

  1. Create a group of cells that display regional sales data. For this lesson, enter the data shown in cells F2 through F5.

  2. Select a cell to average sales data in the east G2 † This is where the results will appear.

  3. Select formulas

  4. Select Mathematics & Trig SUBTOTAL to open the Function Arguments dialog box. Excel for Mac opens the Formula Builder.

  5. Place the cursor in the text field Function_num and enter a number a

  6. Place the cursor in the text field Ref1 and select cells from D3 before D5 † These cells are combined to calculate the average sales volume for the East region.

  7. Select Okay † Except for Excel for Mac where you choose Finished † The average sales data is displayed in cell G2.

  8. Repeat the above steps for selling in the north, selling in the south, and selling in the west. Select the appropriate Ref1 range for each scenario.

Leave a Reply

Your email address will not be published.