Use AVERAGEIF function to find the mean for certain criteria in Excel

Function AVERAGEIF combines the function IF and function AVERAGE in Excel; this combination allows you to find the mean or arithmetic mean of these values ​​in a selected range of data that meets certain criteria.

Part IF function determines which data meets the specified criteria, and shares AVERAGE calculates the mean or mean value. Often AVERAGEIF uses rows of data, called records, to which all the data in each row is associated.

AVERAGEIF Function Syntax

In Excel, function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.

Syntax for AVERAGEIF

 = СРЕБЛИ (диапазон, критерии, диапазон_усреднение) 

The function’s arguments tell which condition to test and the range of data to average when that condition is met.

  • Range (required) is a group of cells in which the function searches according to the specified criteria.
  • Criteria (mandatory) is the value compared to the data in reach † You can enter actual data or a cell reference for this argument.
  • Average_range (optional): the function averages the data in this range of cells when matches are found between the arguments Range and criteria † If you omit the argument Average_range the function instead averages the data that matches the argument Range

In this example, the function AVERAGEIF looking for the average annual turnover in the eastern sales region. The formula will include:

  • Range cells of C3 before C9 which contains the names of the regions.
  • criteria cell D12 (east).
  • By Average_range by cells E3 before E9, containing the average revenue for each employee.

So, if the data is in the range: C3:C12 Directly East then the total sales for that record is averaged by the function.

Opening the AVERAGEIF function

Although you can enter a function in a cell AVERAGEIF many people find it easier to use Dialog Function to add the function to the sheet.

Start entering the sample data in cells C1 v E11 blank excel spreadsheet as shown in the image above.

V cell D12 In chapter Sales Region enter East

These instructions do not include formatting steps for the worksheet. Your worksheet will be different from the worksheet in the example, but the function AVERAGE IF gives you the same results.

  1. Press cell E12 to make it the active cell where the function is placed AVERAGEIF

  2. Press Formulas Tab on the adhesive tape

  3. Select Additional Features Statistical on the ribbon to open the features drop-down list.

  4. Click AVERAGEIF in the list to open Dialog Function † Data that falls into three blank lines in dialog functions make up the function arguments AVERAGEIF

  5. Click on a line Range

  6. highlight cells C3 before C9 on the worksheet to enter those cell references as the range the function looks for.

  7. Click on a line criteria

  8. Click cell D12 to enter a reference to this cell – the function searches the range selected in the previous step for data that meets this criterion. Although you can enter actual data, for example the word East – for this argument it is usually more convenient to add data to a cell on the worksheet and then enter a reference to that cell in the dialog box.

  9. Click on a line Average_range

  10. highlight cells E3 on the E9 If the criteria specified in the previous step match any of the data in the first range (from C3 before C9 ), the function will average the data in the corresponding cells in the second range of the cell.

  11. Click Finished to complete the function AVERAGEIF

  12. Answer $59,641 should appear in cell E12

When you press cell E12 the full function appears in the formula bar above the worksheet.

 = AVERAGEIF (C3: C9, D12, E3: E9) 

Using a cell reference for the Criteria argument makes it easy to find changes to the criteria when needed. In this example you can change the contents of the cell D12 Of East on the north or West † The function is automatically updated and the new result is displayed.

Leave a Reply

Your email address will not be published.