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.
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.
-
Create a blank workbook or sheet.
-
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
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
-
Press
+ C . -
In the sheet, select cell A1, and then press
+ V .
Calculate the simple average
-
Select cells A2 through A7 (the values in the Unit Quality column).
-
On the Formulas tab, click the arrow next to AutoSum
, and then click Average.The result is 8.166666667.
Calculate the average for nonadjacent cells
-
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.
-
On the Formulas tab, click the arrow next to AutoSum
, click Average, and then press RETURN. -
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.
-
In the Formula Bar, select the content between the parentheses, which is A2:A7 if you're using the sample data.
-
Hold down the
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.
-
Select cell A9, which is the cell to the left of the cell that contains the text "Average price per unit."
-
On the Formulas tab, click Insert Function to open the Formula Builder pane.
-
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.
-
Click the box next to array1, and then on the sheet, select cells B2 through B7 (the values in the Price Per Unit column).
-
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).
-
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.
-
In the Formula Builder list, double-click SUM.
-
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).
-
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.
-
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."
-
On the Formulas tab, click Insert Function to open the Formula Builder pane.
-
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.
-
Click the box next to range, and then on the sheet, select cells A2 through A7 (the values in the Price Per Unit column).
-
Click the box next to criteria, and then type ">5".
-
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.
-
Create a blank workbook or sheet.
-
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
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
-
Press
+ C . -
In the sheet, select cell A1, and then press
+ 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
-
Select cells A2 through A7 (the values in the Unit Quality column).
-
On the status bar, click the arrow for the pop-up menu
(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.
Calculate an average so that it appears on a sheet
-
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.
-
On the Standard toolbar, just below the workbook title, click the arrow next to AutoSum
, 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 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
-
Select the cells whose contents you want to average. For example, select A2, A4, and A7.
Tip: To select nonadjacent cells, hold down the
key and click the cells that you want to average. -
On the status bar, click the arrow for the pop-up menu
, 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
-
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.
-
On the Standard toolbar, just below the workbook title, click the arrow next to AutoSum
, click Average, and then press RETURN. -
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.
-
In the Formula Bar, select the content between the parentheses, which is A2:A7 if you're using the sample data.
-
Hold down the
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.
-
Select cell A9, which is the cell to the left of the cell that contains the text "Average price per unit."
-
On the Formulas tab, under Function, click Formula Builder.
-
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.
-
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).
-
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).
-
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.
-
In the Formula Builder list, double-click SUM.
-
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).
-
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.
-
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."
-
On the Formulas tab, under Function, click Formula Builder.
-
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.
-
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).
-
Under Arguments, click the box next to criteria, and then type ">5".
-
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.
I am very impressed with your post because this post is very beneficial for me and provides a piece of new knowledge to me.
ReplyDeletevisit site
I am very impressed with your post because this post is very beneficial for me and provides a piece of new knowledge to me.
ReplyDeleteyoutube bellen