Speaking in a mathematical context, "average" is used by most people for "central tendency", which means the middle number of a number. There are three kinds of measures of central tendency, namely: mean (arithmetic), median, and mode. Microsoft Excel has functions for all three of these measures of central tendency, including the ability to define a weighted average, which is useful in finding the average price when dealing with cases with different quantities of objects at different prices. also.
Step
Method 1 of 4: Finding the Arithmetic Mean Value (Average)
Step 1. Enter the number you want to average
To illustrate how each central tendency function works, we will use a series of ten small numbers. (You should never use numbers this small when using functions outside of the following examples.)
- Generally, numbers are entered into columns, so for the following examples enter the numbers into cells A1 through A10 on the worksheet.
- The numbers entered are 2, 3, 5, 5, 7, 7, 7, 9, 16, and 19.
- Although not required, you can calculate the sum of the numbers by entering the formula “=SUM(A1:A10)” into cell A11. (Do not include quotation marks; quotation marks are used to contain formulas in this one article.)
Step 2. Find the average of the entered numbers
You can do this using the AVERAGE function. You can use the function in one of three ways:
- Click an empty cell, for example A12, then type “=AVERAGE(A1:10)” (and again, without the quotes) directly into the cell.
- Click on an empty cell, then click on the "f" symbolx” in the function bar at the top of the worksheet. Select "AVERAGE" from the "Select a function:" list in the Insert Function dialog, then click OK. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
- Insert an equal sign (=) into the function bar to the right of the function symbol. Select the AVERAGE function from the child list box containing the function name next to the function symbol. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
Step 3. Observe the results in the cells that have been filled with the formula
The mean, or arithmetic mean, is determined by counting the number of numbers in the row of cells (80), then dividing by the number of data in the row (10), or 80/10 = 8.
- If you are calculating the amount as suggested above, you can verify it by entering the formula "=A11/10" in any blank cell.
- The mean is considered a good indicator of central tendency when the individual values in the data series are close to each other. The mean is not good for examples of data sequences that have some values that differ greatly from other values.
Method 2 of 4: Finding the Median Value
Step 1. Enter the numbers for which the median will be calculated
Step 2. Find the median of the entered numbers
Do this using the MEDIAN function. As with the AVERAGE function, you can enter the function in one of three ways:
- Click an empty cell, for example A13, then type “=MEDIAN(A1:10)” (and again, without the quotes) directly into the cell.
- Click on an empty cell, then click on the "f" symbolx” in the function bar at the top of the worksheet. Select "MEDIAN" from the "Select a function:" list in the Insert Function dialog, then click OK. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
- Insert an equal sign (=) into the function bar to the right of the function symbol. Select the MEDIAN function from the child list box containing the function name next to the function symbol. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
Step 3. Observe the results in the cells that have been filled with the formula
The median is the dividing point of the sample data series, with half of the data values above the median, and half of the values below the median. (In the case of the sample data series, the median value is 7.) The median may or may not have the same value as the value in the sample data.
Method 3 of 4: Finding the Mode Value
Step 1. Enter the numbers for which the mode value will be searched
Let's use the same data sequence again (2, 3, 5, 5, 7, 7, 7, 9, 16, and 19), and then enter it into cells A1 through A10.
Step 2. Find the mode value of the entered numbers
Excel has different mode functions, depending on the version of Excel you are using.
- For Excel 2007 and older, there is a single MODE function. This function will find the single mode in the sample data series.
- For Excel 2010 and newer, you can use the two MODE functions, which work exactly like older versions of Excel, or the MODE. SNGL function, which is built to use a more accurate algorithm for finding mode values. (Another mode function, MODE. MULT, will return some data if it finds some data in the sample data, but this function is intended to be used on arrays of data, not just a single row of data.)
Step 3. Enter the selected mode function
Just like the AVERAGE and MEDIAN functions, there are three ways to do this:
- Click an empty cell, for example A14, then type "=MODE(A1L10)" (again, without the quotes) directly into the cell. (If you want to use the MODE. SNGL function, type "MODE. SNGL" instead of "MODE" in the formula).
- Click on an empty cell, then click on the "f" symbolx” in the function bar at the top of the worksheet. Select "MODE" or "MODE. SNGL" from the "Select a function:" list in the Insert Function dialog, then click OK. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
- Insert an equal sign (=) into the function bar to the right of the function symbol. Select the "MODE" or "MODE. SNGL" function from the drop-down list box containing the function name next to the function symbol. Enter the data row "A1:A10" in the Number 1 box in the Function Arguments dialog box, then click OK.
Step 4. Observe the results in the cells that have been filled with functions
The mode value is the value that occurs most frequently in the sample data series. In the example data series used, the mode value is 7, because 7 appears three times in the data series.
If there are two different numbers with the same number of occurrences, the MODE or MODE. SNGL functions will return the value that was found first as a result. If you change the value "3" in the sample data series to "5," the mode result will change from 7 to 5, because 5 is found first. However, if you change the data sequence so that three 7s are found first instead of three 5s, the mode will return to 7
Method 4 of 4: Finding the Weighted Average
Step 1. Enter the data for which you want to find the weighted average
Unlike finding a single average using a single-column list of data, to find a weighted average, you need two sets of data. For the example in this case, we will make the assumption that the data used is tonic delivery data, with the number of tonic boxes and the price per box.
- For this example, we will use column labels. Enter the label "Price Per Box" into cell A1 and "Number of boxes" into cell B1.
- The first shipment was 10 boxes at a price of $20 per box. Enter "$20" into cell A2 and "10" into cell B2.
- The demand for tonics increases, so that on the second shipment, the number of boxes shipped is 40. On the other hand, due to increased demand, the price is increased to $30 per box. Enter "$30" into cell A3 and "40" into cell B3.
- As prices increased, the demand for tonics decreased, so that on the third shipment, only 20 boxes were delivered. Due to reduced demand, the price per box has decreased to $25. Enter "$25" into cell A4 and "20" into cell B4.
Step 2. Enter the necessary formulas to calculate the weighted average
Unlike calculating a single average, Excel does not have a function to calculate a weighted average. Instead, you should use two functions:
- SUMPRODUCT. The SUMPRODUCT function multiplies the numbers in the same row and sums them with the results of the other rows. Break down the data distance of each column; because the values are in cells A2 through A4 and B2 through B4, you should write “=SUMPRODUCT(A2:A4, B2:B4)”. The result obtained is the value of money in the currency generated from the three shipments.
- SUM. The SUM function adds up the numbers in a row or column. Since you want to find the average price for a box of tonics, you'll need to add up all the boxes sold in the three shipments. If you write this formula separately, it will be “=SUM(B2:B4)”.
Step 3. Since the average is determined by dividing the sum by one number, you can combine the two functions into one formula, written as “=SUMPRODUCT(A2:A4, B2:B4)/SUM(B2:B4)”
Step 4. Observe the results in the cells that have been filled with the formula
The average price per box is the total shipping value divided by the total number of boxes sold.
- The total value of the shipment is 20 x 10 + 30 x 40 + 25 x 20, or 200 + 1200 + 500, or $1900.
- The total number of boxes sold is 10 + 40 + 20, or 70.
- Average price per box is 1900/70 = $27.14.
