Calculate an average - Microsoft Support (2023)

Excel provides a variety of ways to find the average of a set of numbers. For example, you can use function to calculate a simple average, a weighted average, or an average that excludes specific values.

Newer versionsOffice 2011

Use the provided sample data and the following procedures to learn how to calculate averages.

Copy the sample data

To better understand the steps, copy the following sample data to cell A1 on a blank sheet.

  1. Create a blank workbook or sheet.

  2. Select the following sample data.

    Note:Do not select the row or column headings (1,2,3...A,B,C...) when you copy the sample data to a blank sheet.

    Selecting sample data in Help

    Calculate an average - Microsoft Support (1)

    Unit Quality

    Price Per Unit

    Number of Units Ordered

    10

    35

    500

    7

    25

    750

    9

    30

    200

    10

    40

    300

    8

    27

    400

    5

    20

    200

    Average unit quality

    Average price per unit

    Average quality for all units rated above 5

  3. Press Calculate an average - Microsoft Support (2)+ C .

  4. In the sheet, select cell A1, and then press Calculate an average - Microsoft Support (3)+ V .

Calculate the simple average

  1. Select cells A2 through A7 (the values in the Unit Quality column).

  2. On the Formulas tab, click the arrow next to AutoSum Calculate an average - Microsoft Support (4), and then click Average.

    The result is 8.166666667.

Calculate the average for nonadjacent cells

  1. Select the cell where you want the average to appear, such as cell A8, which is the cell to the left of the cell that contains the text "Average unit quality" in the sample data.

  2. On the Formulas tab, click the arrow next to AutoSum Calculate an average - Microsoft Support (5), click Average, and then press RETURN.

  3. Click the cell that contains the average that you just created, cell A8 in this example.

    (Video) How To: Calculate Sums, Averages, Hourly Rates & Other Functions in Microsoft Excel

    The formula is displayed in the formula bar, =AVERAGE(A2:A7) if you're using the sample data.

  4. In the Formula Bar, select the content between the parentheses, which is A2:A7 if you're using the sample data.

  5. Hold down the Calculate an average - Microsoft Support (6) key and click the cells that you want to average, and then press RETURN. For example, select A2, A4, and A7, and then press RETURN.

    Excel replaces the selected range reference in the AVERAGE function with cell references for the cells that you selected. In this example, the result is 8.

Calculate a weighted average

This example calculates the average price paid for a unit across all orders, where each order is for a different number of units at a different price per unit.

  1. Select cell A9, which is the cell to the left of the cell that contains the text "Average price per unit."

  2. On the Formulas tab, click Insert Function to open the Formula Builder pane.

  3. In the Formula Builder list, scroll down and double-click SUMPRODUCT.

    Tip:To quickly find a function, click the Search for a function box, and then start typing the function name. For example, start typing SUMPRODUCT.

  4. Click the box next to array1, and then on the sheet, select cells B2 through B7 (the values in the Price Per Unit column).

  5. Click the box next to array2, and then on the sheet, select cells C2 through C7 (the values in the Number of Units Ordered column).

  6. In the formula bar, click to the right of the closing parenthesis for the formula, and then type /

    If you don't see the formula bar, on the View menu, click Formula Bar.

  7. In the Formula Builder list, double-click SUM.

  8. Select the range in the number1 box, press DELETE, and then on the sheet, select cells C2 through C7 (the values in the Number of Units column).

    The formula bar should now contain the following formula: =SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7).

  9. Press RETURN.

    This formula divides the total cost of all the orders by the total number of units ordered, which results in a weighted price per unit average of 29.38297872.

Calculate an average that ignores specific values

You can create a formula that excludes specific values. In this example, you'll create a formula to calculate the average unit quality for all units that have a rating greater than 5.

  1. Select cell A10, which is the cell to the left of the cell that contains the text "Average quality for all units rated above 5."

  2. On the Formulas tab, click Insert Function to open the Formula Builder pane.

  3. In the Formula Builder list, double-click AVERAGEIF.

    (Video) How to use the AVERAGEIF function in Excel

    Tip:To quickly find a function, click the Search for a function box, and then start typing the function name. For example, start typing AVERAGEIF.

  4. Click the box next to range, and then on the sheet, select cells A2 through A7 (the values in the Price Per Unit column).

  5. Click the box next to criteria, and then type ">5".

  6. Press RETURN.

    The formula excludes the value in cell A7 from the calculation, and results in an average unit quality of 8.8.

    Tip:To use the AVERAGEIF function to calculate an average that excludes zero values in a list of numbers, type "<>0" in the box next to criteria.

Use the provided sample data and the following procedures to learn how to calculate averages.

Copy the sample data

To better understand the steps, copy the following sample data to cell A1 on a blank sheet.

  1. Create a blank workbook or sheet.

  2. Select the following sample data.

    Note:Do not select the row or column headings (1,2,3...A,B,C...) when you copy the sample data to a blank sheet.

    Selecting sample data in Help

    Calculate an average - Microsoft Support (7)

    Unit Quality

    Price Per Unit

    Number of Units Ordered

    10

    35

    500

    7

    25

    750

    9

    30

    200

    10

    40

    300

    8

    27

    400

    5

    20

    200

    Average unit quality

    Average price per unit

    Average quality for all units rated above 5

  3. Press Calculate an average - Microsoft Support (8)+ C .

  4. In the sheet, select cell A1, and then press Calculate an average - Microsoft Support (9)+ V .

Calculate the simple average

Let's use two different ways to calculate the average unit quality. In the first way, you can quickly see the average without entering a formula. In the second way, you use the AutoSum function to calculate the average so that it appears on your sheet.

Get a quick average

  1. Select cells A2 through A7 (the values in the Unit Quality column).

  2. On the status bar, click the arrow for the pop-up menu Calculate an average - Microsoft Support (10)(if you are using the sample data, the area probably contains the text Sum=49), and then click Average.

    The result is 8.166666667.

    Note:If you don't see the status bar, on the View menu, click Status Bar.

    (Video) Formula for Calculating Length of Service in Microsoft Excel

Calculate an average so that it appears on a sheet

  1. Select the cell where you want the average to appear, such as cell A8, which is the cell to the left of the cell that contains the text "Average unit quality" in the sample data.

  2. On the Standard toolbar, just below the workbook title, click the arrow next to AutoSum Calculate an average - Microsoft Support (11), click Average, and then press RETURN.

    The result is 8.166666667, which is the average quality rating for all units.

    Tip:If you're working with data that lists numbers in a row, select the first blank cell at the end of the row, and then click the arrow next to AutoSum Calculate an average - Microsoft Support (12).

Calculate the average for nonadjacent cells

There are two ways to calculate the average of cells that are not next to each other. In the first way, you can quickly see the average without entering a formula. In the second way, you use the AVERAGE function to calculate the average so that it appears on your sheet.

Get a quick average

  1. Select the cells whose contents you want to average. For example, select A2, A4, and A7.

    Tip:To select nonadjacent cells, hold down the Calculate an average - Microsoft Support (13) key and click the cells that you want to average.

  2. On the status bar, click the arrow for the pop-up menu Calculate an average - Microsoft Support (14), and then click Average.

    The result for the example is 8.

    Note:If you don't see the status bar, on the View menu, click Status Bar.

Calculate an average so that it appears on a sheet

  1. Select the cell where you want the average to appear, such as cell A8, which is the cell to the left of the cell that contains the text "Average unit quality" in the sample data.

  2. On the Standard toolbar, just below the workbook title, click the arrow next to AutoSum Calculate an average - Microsoft Support (15), click Average, and then press RETURN.

  3. Click the cell that contains the average that you just created, cell A8 in this example.

    The formula is displayed in the formula bar, =AVERAGE(A2:A7) if you're using the sample data.

  4. In the Formula Bar, select the content between the parentheses, which is A2:A7 if you're using the sample data.

  5. Hold down the Calculate an average - Microsoft Support (16) key and click the cells that you want to average, and then press RETURN. For example, select A2, A4, and A7, and then press RETURN.

    Excel replaces the selected range reference in the AVERAGE function with cell references for the cells that you selected. In this example, the result is 8.

Calculate a weighted average

This example calculates the average price paid for a unit across all orders, where each order is for a different number of units at a different price per unit.

  1. Select cell A9, which is the cell to the left of the cell that contains the text "Average price per unit."

    (Video) Microsoft Word 👉 How To Use Formula Option Average In Microsoft Word (Part - 2) | KB Tech

  2. On the Formulas tab, under Function, click Formula Builder.

  3. In the Formula Builder list, double-click SUMPRODUCT.

    Tip:To quickly find a function, click the Search for a function box, and then start typing the function name. For example, start typing SUMPRODUCT.

  4. Under Arguments, click the box next to array1, and then on the sheet, select cells B2 through B7 (the values in the Price Per Unit column).

  5. Under Arguments, click the box next to array2, and then on the sheet, select cells C2 through C7 (the values in the Number of Units Ordered column).

  6. In the formula bar, click to the right of the closing parenthesis for the formula, and then type /

    If you don't see the formula bar, on the View menu, click Formula Bar.

  7. In the Formula Builder list, double-click SUM.

  8. Under Arguments, select the range in the number1 box, press DELETE, and then on the sheet, select cells C2 through C7 (the values in the Number of Units column).

    The formula bar should now contain the following formula: =SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7).

  9. Press RETURN.

    This formula divides the total cost of all the orders by the total number of units ordered, which results in a weighted price per unit average of 29.38297872.

Calculate an average that ignores specific values

You can create a formula that excludes specific values. In this example, you'll create a formula to calculate the average unit quality for all units that have a rating greater than 5.

  1. Select cell A10, which is the cell to the left of the cell that contains the text "Average quality for all units rated above 5."

  2. On the Formulas tab, under Function, click Formula Builder.

  3. In the Formula Builder list, double-click AVERAGEIF.

    Tip:To quickly find a function, click the Search for a function box, and then start typing the function name. For example, start typing AVERAGEIF.

  4. Under Arguments, click the box next to range, and then on the sheet, select cells A2 through A7 (the values in the Price Per Unit column).

  5. Under Arguments, click the box next to criteria, and then type ">5".

  6. Press RETURN.

    The formula excludes the value in cell A7 from the calculation, and results in an average unit quality of 8.8.

    Tip:To use the AVERAGEIF function to calculate an average that excludes zero values in a list of numbers, type "<>0" in the box next to criteria.

    (Video) Using the Excel Average and AverageA functions

See also

Calculate a running total

Calculate percentages

AVERAGE function

FAQs

What are the 3 ways to calculate average? ›

There are three main types of average: mean, median and mode. Each of these techniques works slightly differently and often results in slightly different typical values. The mean is the most commonly used average. To get the mean value, you add up all the values and divide this total by the number of values.

How do you use the Averagea function? ›

To use it, you simply enter the range of cells you want to average into the function's parentheses, followed by the "=" symbol. For example, if you wanted to average the values in the cells A1 through A10, you would enter the following into the function bar: =AVERAGEA(A1:A10).

How do you calculate average condition? ›

Average which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers. For example, the average of 2, 3, 3, 5, 7, and 10 is 30 divided by 6, which is 5.

Which is the best average method? ›

The most widely used method of calculating an average is the 'mean'. When the term 'average' is used in a mathematical sense, it usually refers to the mean, especially when no other information is given. Add the numbers together and divide by the number of numbers. (The sum of values divided by the number of values).

Which is the simplest way to find the average? ›

How to Calculate Average? We can easily calculate the average for a given set of values. We just have to add all the values and divide the outcome by the number of given values.

What are the three ways for calculating an average in Excel? ›

The three main methods of finding the average within Excel are the AVERAGE (mean), MEDIAN (middle), and MODE (frequency) functions. They are all easy to use, so choose the one that's right for your type of data and the questions you want to answer.

What is the average function formula? ›

Description. Returns the average (arithmetic mean) of the arguments. For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.

What is difference between average and Averagea? ›

AVERAGE: Returns the average (arithmetic mean) of all the numbers in a column. AVERAGEA: Returns the average (arithmetic mean) of the values in a column. AVERAGEX: Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.

What's the difference between average and Averagea? ›

The AVERAGEA Function[1] in Excel is a Statistical function. The function calculates the average of a group of supplied values. It differs from the AVERAGE function, as it evaluates the logical values TRUE and FALSE, and numbers represented as text, whereas AVERAGE just skips these values during calculation.

What are the 4 types of averages? ›

We consider there to be four types of average: mean, mode, median and range. Actually, range is a measure of spread or distribution but the others are our most common “measures of central tendency”.

What is averaging factor? ›

Average Factor means, with respect to each Management Fee Calculation Period, the sum of the Daily Factors determined for each day in such Management Fee Calculation Period, divided by the number of days in such Management Fee Calculation Period (determined on an Actual/360 day-count basis), subject to a minimum ...

How do you average with multiple criteria? ›

The Excel AVERAGEIFS function calculates the arithmetic mean of all cells in a range that meet the specified criteria. The syntax is as follows: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

What are the three most commonly used averages? ›

Arithmetic mean, median and mode are the three most commonly used measures of central tendency.

What is the most accurate average? ›

Mean is generally considered the best measure of central tendency and the most frequently used one.

Which average is most commonly used? ›

An average can be described as a summary of a group of numbers as a single number. There are different types of averages; the most common used in official statistics are mean and median.

How do you calculate average number of employees? ›

Divide the number of weeks worked of each employee by 52 weeks to determine the portion of the year they were employed by you. 3. Add the portions for all employees to obtain the "average number of employees" that were employed last year.

How do you program an average? ›

average = sum of all values / number of values

Here we shall learn how to programmatically calculate average.

What methods can be used to calculate average cost? ›

What Is the Average Cost Method? The average cost method assigns a cost to inventory items based on the total cost of goods purchased or produced in a period divided by the total number of items purchased or produced. The average cost method is also known as the weighted-average method.

Which is the popular measure of average? ›

There are three often-used measures of average: Mean – what in everyday language would think of as the average of a set of figures. Median – the 'middle' value of a dataset. Mode – the most common value.

Which average is considered as the best average and why? ›

Arithmetic mean is considered to best average.

The average (arithmetic mean) of a, b, c, and d is 3 times the median.

What is the most common average computed? ›

The arithmetic mean is the most common measure of central tendency. It is computed by summing all the scores (sigma or Σ) and dividing by the number of scores (N):

Videos

1. How to Calculate Basic Statistics in Microsoft Excel (Sum, Average, Standard Deviation, etc.)
(Cody Baldwin)
2. Tips to Ensure H-4 & H-4 EAD Are Approved along with H-1B
(Reddy & Neumann, P.C.)
3. Calculations in Microsoft Access Report Footer Act5No1
(Study Opportunities CAT Gr12)
4. HOW TO CALCULATE WEIGHTED AVERAGE COST IN MICROSOFT ACCESS
(𝐊𝐚𝐦𝐚𝐲𝐨 𝐀𝐤𝐨🌈™)
5. Weighted Average with the SUMPRODUCT Function in Excel - Weighted Mean
(Computergaga)
6. How to Calculate Total Daily Averages with Pivot Tables
(Excel Campus - Jon)
Top Articles
Latest Posts
Article information

Author: Carlyn Walter

Last Updated: 10/12/2022

Views: 6632

Rating: 5 / 5 (50 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Carlyn Walter

Birthday: 1996-01-03

Address: Suite 452 40815 Denyse Extensions, Sengermouth, OR 42374

Phone: +8501809515404

Job: Manufacturing Technician

Hobby: Table tennis, Archery, Vacation, Metal detecting, Yo-yoing, Crocheting, Creative writing

Introduction: My name is Carlyn Walter, I am a lively, glamorous, healthy, clean, powerful, calm, combative person who loves writing and wants to share my knowledge and understanding with you.