Most people use the term "average" to mean "central tendency", which refers to the center of a range of numbers.The mean, median, and mode are measures of central tendency.Microsoft excel has functions for all three measures, as well as the ability to determine a weighted average, which is useful for finding an average price when dealing with different quantities of items
Step 1: You can find the average by entering numbers.
We will use a series of ten small numbers to show how the central tendency functions work.When you use functions outside of these examples, you won't likely use actual numbers.You should enter the numbers in cells A1 through A10 in these examples because most of the time you will enter them in columns.There are 19 numbers to enter.The sum of the numbers can be found by entering the formula in cell A11.The quotation marks are used to set off the formula from the rest of the text.
Step 2: The average of the numbers you entered.
The AVERAGE function is used to do this.If you click on an empty cell, you can type the function directly in the cell without quotation marks.Click on an empty cell, then click on the symbol in the function bar.Click OK if you want to select "AVERAGE" from the "Select a function:" list.Click OK if you enter the range "A1:A10" in the Number 1 field of the Function Arguments dialog.You can enter an equals sign in the function bar.From the Name box, select the AVERAGE function to the left of the function symbol.Click OK if you enter the range "A1:A10" in the Number 1 field of the Function Arguments dialog.
Step 3: Take a look at the result in the cell you entered the formula in.
The average is determined by finding the sum of the numbers in the cell range and then dividing it by how many numbers make up the range.You can verify the sum by entering it in an empty cell.When the individual values in the sample range are close together, the mean value is a good indicator of central tendency.It is not a good indicator when there are a few values that are different from the rest.
Step 4: You can enter the numbers you want to find the median for.
We will use the same range of ten numbers as we did in the method for finding the mean value.If you haven't already done so, enter them in the cells from A1 to A10.
Step 5: The numbers you entered have a median value.
The MEDIAN function is used to do this.If you click on an empty cell, you can type "MEDIAN(A1:10)" directly in the cell.Click on an empty cell and then click on the symbol in the function bar.Click OK if you want to selectMEDIAN from the "Select a function:" list.Click OK if you enter the range "A1:A10" in the Number 1 field of the Function Arguments dialog.You can enter an equals sign in the function bar.From the Name box, select the MEDIAN function to the left of the function symbol.Click OK if you enter the range A1:A10 in the Number 1 field.
Step 6: Take a look at the result in the cell you entered the function in.
Half of the numbers in the sample have values that are higher than the median, and the other half are lower.The median value is 7 in the sample range.The median may be different from one of the values in the sample range.
Step 7: You can enter the numbers you want to find the mode for.
We will use the same range of numbers from A1 to A10 again.
Step 8: The mode value is what you entered.
Depending on the version of excel you have, there are different mode functions.There is a single mode function for excel.The function will find a single mode in a sample range of numbers.You can use either the MODE function, which works the same as in earlier versions of excel, or theMODE.SNGL function which uses a more accurate method to find the mode.MODE.MULT returns multiple values if it finds multiple modes in a sample, but it is intended for use with array of numbers instead of a single list of values.
Step 9: You can enter the mode function.
Click on an empty cell and type "MODE(A1:10)" directly in the cell, without the quotation marks, as with the AVERAGE and MEDIAN functions.If you want to use the MODE.SNGL function, you have to type it in place of the equation.Click on an empty cell, then click on the symbol in the function bar.Click OK if you want to selectMODE orMODE.SNGL from the "Select a function:" list.Click OK if you enter the range A1:A10 in the Number 1 field.You can enter an equals sign in the function bar.The function symbol can be found at the left of the name box.Click OK if you enter the range "A1:A10" in the Number 1 field of the Function Arguments dialog.
Step 10: Take a look at the result in the cell you entered the function in.
In the sample range, the mode is the value that occurs most often.In our sample range, the mode is 7, as 7 occurs three times in the list.If two numbers appear in the same number of times, the MODE function will report the value that it encounters first.The mode will change from 7 to 5 if you change the sample list to a 5.The mode will be 7 again if you change the list to have three 7s before three 5s.
Step 11: The data you enter will be used to calculate the weighted average.
We need two sets of numbers to find a weighted average.For the purpose of this example, we assume the items are shipments of tonic, dealing with a number of cases and the price per case.Column labels will be included for this example.The number of cases and the price per case can be entered.The price for the first shipment was $20 per case.In cell A2 and cell B2 enter the amount.The second shipment had 40 cases.The price of tonic went up due to demand.In cell A3 and B3 you can enter $30.The demand for tonic went down because the price went up.The price per case went down to $25 with the lower demand.In cell A4 and cell B4 you can enter the amount.
Step 12: You need to enter a formula to calculate the weighted average.
It is not possible to figure a weighted average with a single function.SUMPRODUCT is one of the two functions you will use.The SUMPRODUCT function takes the numbers in each row and adds them to the product of the other rows.You specify the range of each column, since the values are in cells A2 to A4.The total dollar value is the result of three shipments.There is a SUM.The numbers are added in a single row or column.We want to find an average price for a case of tonic, so we will sum up the number of cases that were sold in all three shipments.If you wrote this part of the formula separately, it would read.
Step 13: Since an average is determined by dividing a sum of all numbers by the number of numbers, we can combine the two functions into a single formula.
Step 14: Take a look at the result in the cell that you entered the formula in.
The total value of the shipment is divided by the total number of cases sold to arrive at the average per-case price.The total value is 20 x 10, 30 x 40, 25 x 20, or 200 x 1200, or $1900.10, 40, 20, or 70 are the total number of cases sold.The average price is $27.14 per case.