Search Contextures Sites

 

30 Excel Functions in 30 Days

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Learn how to create Excel dashboards.

 

30 Excel Functions in 30 Days

 

 

 

Learn how to create Excel dashboards.

 

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Learn how to create Excel dashboards.

 

 

 

Excel Average Functions

Examples show how to average Excel cells with numbers, text, blanks, or cells based on specific criteria. Use the AVERAGE, AVERAGEA, AVERAGEIF or AVERAGEIFS functions.

Average Cells with Numbers -- AVERAGE Function   
Average Cells with Data -- AVERAGEA   
Average cells that match criteria -- AVERAGEIF  
    - Match criterion exactly  
    - Match criterion in a string  
    - Match criterion using operator  

Average cells for multiple criteria -- AVERAGEIFS  

Average Visible Items in a Filtered List  

Download the AVERAGE Sample File
More Function Tutorials

Average Cells with Numbers -- AVERAGE

The AVERAGE function will average cells that contain numbers. Its syntax is:
    =AVERAGE(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the AVERAGE formula.

The following AVERAGE function example uses one argument -- a reference to cells A1:A5.

  1. Enter the sample data on your worksheet
  2. In cell A7, enter an AVERAGE formula, to average the numbers in column A:   =AVERAGE(A1:A5)  
  3. Excel AVERAGE Function

  4. Press the Enter key, to complete the formula.
  5. The result will be 21.83, the average of the cells that contain numbers.
    Cell A1 isn't included in the average, because it contains text, and the empty cell C3, is ignored.

Note: Since dates are stored as numbers, the AVERAGE function will include any cells that contain dates.

Average Cells with Data -- AVERAGEA

The AVERAGEA function will average cells that are not empty. Its syntax is:
    =AVERAGEA(value1, value2,...value30).
The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument -- a reference to cells A1:A5.

  1. Enter the sample data on your worksheet
  2. In cell A7, enter a AVERAGEA formula, to average the numbers in column A:   =AVERAGEA(A1:A5)  
  3. Excel AVERAGEA Function

  4. Press the Enter key, to complete the formula.
  5. The result will be 16.38, the average of the cells that contain data. Cell A1 contains text, which is treated as a zero, and cell A3 is not included in the average, because it is blank.

Note: AVERAGEA will include cells with formulas — including those that look empty, because they evaluate to "", e.g. =IF(B2="","",B2).

Learn how to create Excel dashboards.

Average cells that match criteria -- AVERAGEIF

Match criterion exactly

In Excel, average cells that meet a specific criterion. In this example only the quantities for the Pen orders will be averaged.

  1. Select the cell in which you want to see the average (cell A12 in this example)
  2. Type an equal sign (=) to start the formula
  3. Type:   AVERAGEIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for text, so type the word in double quotes:   "Pen"
    Note: upper and lower case are treated equally
  7. Type a comma, to separate the arguments
  8. Select the cells that contain the values to average. In this example, cells B1:B10 contain the values
  9. Type a closing bracket
    The completed formula is: =AVERAGEIF(A1:A10,"Pen",B1:B10)
  10. Excel AVERAGEIF Function

  11. Press the Enter key to complete the entry
  12. The result will be 9.25, the average quantity for rows that contain "Pen"

Match criterion in a string

In Excel, average cells in rows that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be averaged, because they contain the string "pen".

  1. Select the cell in which you want to see the average (cell A12 in this example)
  2. Type an equal sign (=) to start the formula
  3. Type:   AVERAGEIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters:   "*Pen*"
    Note: upper and lower case are treated equally
  7. Type a comma, to separate the arguments
  8. Select the cells that contain the values to average. In this example, cells B1:B10 contain the values
  9. Type a closing bracket
    The completed formula is: =AVERAGEIF(A1:A10,"*Pen*",B1:B10)
  10. Excel AVERAGEIF wildcard

  11. Press the Enter key to complete the entry
  12. The result will be 8.83, the average of rows that contain the string, "Pen", in column A

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 7 above could be changed to:
    =AVERAGEIF(A1:A10,"*" & B12 & "*",B1:B10)
if cell B12 contained the text — pen.

Criterion and operator

You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be averaged.

  1. Select the cell in which you want to see the average (cell A12 in this example)
  2. Type an equal sign (=) to start the formula
  3. Type:   AVERAGEIF(
  4. Select the cells that contain the values to check for the criterion. In this example, cells B1:B10 will be checked
  5. Type a comma, to separate the arguments
  6. Type the criterion. In this example, you're checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: ">=10"
    Note: Even though this is a numerical criterion, it must enclosed in double quote marks.
  7. Type a closing bracket
  8. The completed formula is:
              =AVERAGEIF(B1:B10,">=10")

  9. Press the Enter key to complete the entry

    Excel AVERAGEIF operator

Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to:
    =AVERAGEIF(B1:B10,">=" & B12)
if cell B12 contained the number — 10

Average cells for multiple criteria -- AVERAGEIFS

Match multiple criteria

In Exce 2007 and later versions, you can use the AVERAGEIFS function to average rows that meet two or more criteria. In this example only the rows where the item is "Pen" and the quantity is greater than or equal to ten will be averaged.

The criteria have been entered in cell D3 and E3. We'll refer to those cells in the formula, instead of typing the values in the formula.

  1. Select the cell in which you want to see the total
  2. Type an equal sign (=) to start the formula
  3. Type:   AVERAGEIFS(
  4. Select the cells that contain the values to average. In this example, cells B2:B10 will be averaged
  5. To start the next argument, type a comma
  6. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
  7. Type a comma, and click on cell D3, which contains the first criterion, "Pen"
  8. To start the next set of criteria, type a comma
  9. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
  10. Type a comma, and the operator for the second criterion:  ">="
    Note: The operator is enclosed in double quote marks.
  11. Type an ampersand (&), and click on cell E3, which contains the minimum number for the averaged cells -- 10.
  12. To start the next argument, type a comma
  13. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
  14. Finish with a closing bracket: )
  15. The completed formula is shown below.
  16. Press the Enter key to complete the entry

The completed formula is:

=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">=" & E3)

Excel AVERAGEIF operator

Note: Instead of refering to cells for the criteria, you can type them into the formula. Here is the same formula, with typed criteria:

=AVERAGEIFS(B2:B10,A2:A10,"Pen",B2:B10,">=10")

Average Rows in a Filtered List -- SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function to average the visible rows.

  1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
  2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
  3. Select the cell immediately below the column you want to sum.
  4. Click the AutoSum button on the Ribbon's Home tab or Formulas tab.
    • If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.
  5. A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column
    • The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. The default is 9, which tells Excel to SUM the numbers.
    • Other function numbers can be used, such as 1 for AVERAGE, or 2 for COUNT.
  6. To Average all the non-empty cells in column D, select 1 - AVERAGE from the drop down list of functions, as the first argument:
    =SUBTOTAL(1,D2:D10)
  7. Excel AVERAGEIF operator

  8. Check the range of cells to be averaged, and correct the range address, if necessary.
  9. Press the Enter key to complete the formula entry.

    Note: In the Subtotal function, you can use 101, instead of 1:
        =SUBTOTAL(101,D2:D10)
    to subtotal rows which have been manually hidden, as well as filtered rows.

Download the AVERAGE Sample File

To see the formulas and test data used in this example, you can download the Average functions sample file. The file is in xlsx format (Excel 2007 and later versions.

Excel Function Tutorials

Sum Functions  
VLOOKUP Function  
INDEX / MATCH Functions   
Count Functions
Average Functions  
INDIRECT Function  

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Search Contextures Sites

Last updated: May 4, 2014 12:29 AM