The SUM, AVERAGE, MAX, MIN functions are designed to work with sets of values contained in a certain range of cells. In the Function Wizard - Step 1 of 2 dialog box, these functions can be found under the Statistical category. The SUM function refers to mathematical and is designed to calculate the sum in a certain range of cells. Although these functions are related to different categories and calculate various parameters for a certain set of numeric values, they have a lot in common. They have similar syntax and are often used in calculations. Excel, as you can see from the above, is very powerful tool to solve problems using various functions.
SUM function
The SUM function is by far the most used Excel function. The SUM function has the following syntax:
SUM (number1; number2; ...; number255)
where number1, number2 - the number of arguments (from 1 to 255), the sum of which must be calculated.
The arguments of the SUM function must be numbers specified as specific values, references to cells or ranges of cells, arrays of constants. You can use cell names or cell ranges instead of references. An array of constants is an array of numbers enclosed in curly braces, such as (1; 2; 3) or (1: 2: 3). Numbers in curly braces must be separated by a semicolon or a colon.
Suppose cells A1, A2, and A3 contain the values 1, 2, and 3. Then the formulas = SUM (1; 2; 3), = SUM (A1; A2; A3), = SUM (A1: A3), = SUM (Data) and = SUM ((1; 2; 3)) return the same result - 6. Here the name Data is assigned to the range A1: A3. In the first formula, cell references are used as arguments to the SUM function, in the second formula, the argument to the SUM function is an array of values.
- § The sum of the numbers contained in one range of cells. In this case, one argument (number1) is sufficient. To refer to a contiguous range of cells, use a colon (:) as the separator between the first and last cells in the range. For example, in Fig. 6.25 The formula = SUM (C5: E5) in cell F5 returns the sum of the numbers in the range of cells C5: E5.
- · The sum of numbers contained in multiple ranges (both contiguous and non-contiguous). In this case, you can specify up to 255 arguments. To reference two non-contiguous ranges, use the range concatenation operator, denoted by a semicolon (;). For example, the formula = SUM (C5: C7; C9: C11; C13: C15; C17: C19) in cell C24 (Figure 6.25) returns the sum of the numbers that are in the ranges C5: C7, C9: C11, C13: C15, and C17: C19.
- § The sum of the numbers contained in the range that is the intersection of the ranges given as arguments. To reference such a range, use the intersecting range operator, space. For example, the formula in cell C21 = SUM (C $ 5: C $ 7 $ C5: $ E5; C $ 9: C $ 11 $ C9: $ E9; C $ 13: C $ 15 $ C13: $ E13; C $ 17: C $ 19 $ C17: $ E17) returns the sum of the numbers that are contained at the intersection of the ranges: C5: C7 and C5: E5 (cell C5), C9: C11 and C9: E9 (cell C9), C13: C15 and C13: E13 (cell C13), C17 : C19 and C17: E17 (cell C17), i.e. the function defined in this way calculates the sum of the numbers contained in cells C5, C9, C13 and C17 (see Fig. 6.25).
The last formula uses mixed range references. Usage mixed links allows you to reduce the time for entering rather cumbersome formulas in the C21: E23 range. It is enough to enter only one formula in cell C21 and then copy it to the rest of the cells in the range C21: E23.
If names are defined on the worksheet, then using names as arguments to the SUM function makes the formulas, if not less cumbersome, then, by at least more meaningful (fig. 6.26).
Features of using the SUM function
When summing values using the SUM function, only numerical values presented in one of the standard number formats Excel as well as numbers presented as text. Arguments that refer to blank cells, boolean or text values are ignored (fig. 6.27).
Note that the logical value TRUE can affect the calculation result of the SUM function. For example, the formula = SUM (1; 3; 4; TRUE), in which the logical value TRUE is specified explicitly, will return the value 9. The formula = SUM ((1; 3; 4; TRUE)), in which the SUM function is used as an argument array of constants, returns the same result as = SUM (E2: E6).
If boolean values are directly used as arguments to the SUM function, they are converted to numbers. The logical value TRUE is converted to 1, and the value FALSE is converted to 0. This is why the formula = SUM (1; 3; 4; TRUE) (see figure 6.27) returns a different result than the formula = SUM (E2: E6 ).
If at least one of the arguments to the SUM function is an error value, the SUM function returns the error value (Figure 6.28).
AVERAGE function
The AVERAGE function calculates the arithmetic mean of its arguments. If n real numbers a1, a2, ..., an, then the number 1 2 ... nna a aAn + + + = is called the arithmetic mean of the numbers a1, a2, ..., an.
In the formula for the arithmetic mean, the sum of n numbers is first calculated, then the result is divided by the number of terms. To calculate the arithmetic mean of n numbers in Excel, you can use one of the following formulas:
AVERAGE (Range1)
SUM (Range1) / COUNT (Range1)
An example of using these formulas is shown in Fig. 6.31. The names Range1 and Range2 are used as arguments to the AVERAGE, SUM and COUNT functions, which contain a reference to the ranges A2: C6 and E2: G6, respectively. In total, the AVERAGE function can have up to 255 arguments.
As seen from Fig. 6.31, when calculating the average arithmetic function AVERAGE ignores boolean and text values, but takes null values into account.
If you need to consider blank cells, Boolean values, and text when determining the arithmetic mean, use the AVERAGE function. When calculating the arithmetic mean using the AVERAGE function, the logical value TRUE is set equal to 1, the logical value FALSE and text (except for numbers presented as text) are set equal to 0 (zero). In fig. 6.31 you can clearly see that the AVERAGE and AVERAGE functions return different meanings for the same data range. Also, if the original range contains the logical value TRUE, the formulas = AVERAGE (Range2) and = SUM (Range2) / COUNT (Range2) return different values.
MAX and MIN functions
The MAX function returns greatest value from a set of values, the MIN function is the smallest value. The arguments to both functions can be numbers, references to cells or ranges of cells, names of cells or ranges, arrays of constants. There can be up to 255 arguments.
When determining the maximum and minimum values using the MAX and MIN functions, only those cells that contain numbers are taken into account; blank lines, text (except for numbers represented as text) and boolean values are ignored (Figure 6.32).
The MIN and MAX functions return an error value if at least one cell in the range contains an error value. If the range does not contain numeric cells, the MAX and MIN functions return 0 (zero).
If you need to consider blank cells, Boolean values, and text when determining the largest and smallest values in a given range, use the MAX and MINA functions, respectively. In fig. 6.33 it is clearly seen that the MIN and MINA, MAX and MAX functions return different values for the same range.
17.02.2017
Excel is table processor... It can be used to create a variety of reports. It is very convenient to perform various calculations in this program. Many people do not use even half of Excel's capabilities.
Finding the average value of the numbers may be useful at school, as well as at work. The classic way determining the arithmetic mean without using programs consists in adding all the numbers, and then the resulting sum must be divided by the number of terms. If the numbers are large enough or you need to perform an operation many times for reporting purposes, the calculations can take a long time. This is a waste of time and energy, it is much better to use the capabilities of Excel.
Finding the arithmetic mean
Many data are already initially recorded in Excel, but if this does not happen, it is necessary to transfer the data to a table. Each digit for the calculation must be in a separate cell.
Method 1: Calculate the average value using the "Function Wizard"
In this method, it is necessary to write a formula for calculating the arithmetic mean and apply it to the specified cells.
The main disadvantage of this method is that you have to manually enter cells for each term. In the presence of a large number numbers are not very convenient.
Method 2: Automatic calculation of the result in the selected cells
In this method, the arithmetic mean is calculated in just a couple of mouse clicks. Very handy for any number of numbers.
The disadvantage of this method is the calculation of the average value only for numbers located next to each other. If the necessary terms are scattered, then they cannot be selected for calculation. It is not even possible to select two columns, in which case the results will be presented separately for each of them.
Method 3: Using the Formula Bar
Another way to go to the function window:
Most quick way, in which you do not need to search for a long time in the menu, items are needed.
Method 4: Manual Entry
It is not necessary to use the tools in the Excel menu to calculate the average; you can manually write the required function.
Fast and convenient way for those who prefer to create formulas with their own hands, rather than looking for ready-made programs in the menu.
Thanks to these capabilities, it is very easy to calculate the average of any number, no matter how many, it is also possible to compile statistics without manual calculations. With tools Excel programs any calculations are much easier to do than in the head or using a calculator.
In mathematics (and statistics), an average is a number between the smallest and largest values from a set of numbers. There are three common averages: arithmetic mean, median, and mode. V Microsoft Excel you can calculate all three values and also find the weighted average that is used to calculate the average price.
Steps
Average
- Most of the time, numbers are entered in columns (as in our example), so enter numbers in cells A1 - A10.
- The numbers to enter are 2, 3, 5, 5, 7, 7, 7, 9, 16, and 19.
- If you want, find the sum of the numbers by entering the formula “= SUM (A1: A10)” in cell A11 (without the quotes).
-
You can find the arithmetic mean using the AVERAGE function.
- Click on an empty cell, for example A12, and then type "= AVERAGE (A1: 10)" (without the quotes).
- Click on an empty cell, and then click on the "f x" button (in the formula bar directly above the working Excel sheet). In the window that opens, in the "Select a function" list, find and highlight "AVERAGE", and then click OK. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
- Enter an equal sign (=) in the formula bar. To the left of the formula bar, in the Functions drop-down list, find and select AVERAGE. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
-
The cell where you entered the formula will display the arithmetic mean, which is equal to the ratio of the sum of numbers in the specified range of cells (80) to the total number of numbers in the range (10): 80/10 = 8.
- To check the correctness of the answer in any empty cell enter the formula "= A11 / 10".
- The arithmetic mean works well for centering a distribution when the individual numbers in a set of numbers are not very different from each other.
Median
-
Enter the numbers for which you want to find the median.
You can find the arithmetic mean using the MEDIAN function. You can enter the formula in one of three ways:
- Click on an empty cell, for example, A13, and then type "= MEDIAN (A1: 10)" (without the quotes).
- Click on an empty cell, and then click on the f x button (in the formula bar directly above the Excel worksheet). In the window that opens, in the "Select a function" list, find and highlight "MEDIAN", and then click OK. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
- Enter an equal sign (=) in the formula bar. To the left of the formula bar, in the Functions drop-down list, find and select MEDIAN. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
-
The cell where you entered the formula will display the median value, at which half of the numbers in some set of numbers have more than high values than the median, and the other half has more low values than the median (in our example, the median is 7). The median may or may not be equal to one of the numbers in some set of numbers.
Fashion
-
You can enter the formula in one of three ways:
- Click on an empty cell, for example, A14, and then enter "= FASHION (A1: 10)" or "= FASHION.ONE (A1: 10)" (without the quotes).
- Click on an empty cell, and then click on the f x button (in the formula bar directly above the Excel worksheet). In the window that opens, in the "Select a function" list, find and highlight "FASHION" or "FASHION. ONE", and then click OK. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
- Enter an equal sign (=) in the formula bar. To the left of the formula bar, in the Functions drop-down list, find and select MEDIAN or FASHION. ONE. Enter the range "A1: A10" in the line "Number 1" of the opened window and click OK.
-
The cell where you entered the formula will display the value of the mode equal to the number that occurs most often in a certain set of numbers. In our example, the mod is 7, since the number 7 appears three times in our set of numbers.
- If two numbers occur in a range of numbers the same number of times, MODE or FASHION. ONE will display the first number encountered. If in our example you change 3 to 5, then the mod will be 5, not 7, because 5 occurs first.
Enter the numbers you want to find the fashion for. For example, consider a set of ten numbers (2, 3, 5, 5, 7, 7, 7, 9, 16, and 19). Enter these numbers in cells A1 - A10.
V different versions Excel's fashion finding function is different.
Weighted average
-
Enter the numbers you want to find the weighted average of. Two columns of numbers are needed here. For example, consider several shipments of tonic.
- In our example, we will enter the column headings. Enter “Unit Price” in cell A1, and “Units Shipped” in cell B1.
- The first batch consisted of 10 units at $ 20 per unit. Enter "$ 20" in cell A2 and "10" in cell B2.
- The demand for tonic increased, so the second batch consisted of 40 units at $ 30 per unit. Enter "$ 30" in cell A3 and "40" in cell B3.
- As the price rose, demand for the tonic dropped and the third batch consisted of 20 units at $ 25 per unit. Enter “$ 25” in cell A4 and “20” in cell B4.
-
To calculate the weighted average in Excel, you need to use two functions:
- SUMPRODUCT. The SUMPRODUCT function multiplies the numbers on one line and adds the products of the numbers on all lines. You specify the range of each column; in our example: "SUMPRODUCT = (A2: A4, B2: B4)". As a result, you will receive the total cost of the entire tonic shipped.
- SUM. The SUM function adds numbers in a single row or column. Since you want to find the average price per unit of tonic shipped, you need to find the total quantity of tonic shipped. In our example: "= SUM (B2: B4)".
-
The value of the weighted average will be displayed in the cell where you entered the formula. In our example, this is the average price of a tonic unit, equal ratio the total cost of the shipped tonic to the total amount of the tonic.
- The total cost of the shipped tonic: 20 x 10 + 30 x 40 + 25 x 20 = 200 + 1200 + 500 = $ 1900.
- Total tonic shipped: 10 + 40 + 20 = 70.
- Average price: 1900/70 = $ 27.14.
- You do not need to enter all the numbers in a row (in a column or row), but you do need to make sure Excel understands which numbers you want to include and exclude. In our example, if you want to find the average of the first five numbers, enter the formula like this: "= AVERAGE (A1: A5, A10)".
Enter the numbers for which you want to find the arithmetic mean. For example, consider a set of ten numbers.
Category functions Statistical are intended primarily for analyzing ranges of cells in Excel. Using these functions, you can calculate the highest, lowest or average value, count the number of cells containing specified information, etc.
This category contains more than 100 of the most diverse Excel functions, most of which are intended exclusively for statistical calculations and to the ordinary ordinary user it will seem like a dark forest. As part of this lesson, we will consider the most useful and common functions of this category.
For the purposes of this article, we will not touch upon such popular Excel statistical functions as CHECK and COUNTIF, a separate lesson has been prepared for them.
AVERAGE ()
Statistical function AVERAGE returns the arithmetic mean of its arguments.
This function can take up to 255 arguments and find the average over several non-contiguous ranges and cells at once:
If the calculated range contains empty cells or cells containing text, they are ignored. In the example below, the average is searched for four cells, i.e. (4+15+11+22)/4 = 13
If you need to calculate the average, taking into account all the cells in the range, then you can use the statistical function AVERAGE... V following example the average is already searched for 6 cells, i.e. (4+15+11+22)/6 = 8,6(6) .
Statistical function AVERAGE can use mathematical operators as its arguments and various functions Excel:
AVERAGEIF ()
If you need to return the arithmetic mean of values that satisfy a certain condition, you can use the statistical function AVERAGEIF... The following formula calculates the average of numbers that are greater than zero:
V this example the same range is used to calculate the average and check the condition, which is not always convenient. In this case, the function AVERAGEIF there is a third optional argument over which the average can be calculated. Those. by the first argument, we check the condition, by the third, we find the average.
Let's say the table below contains statistics on the cost of drugs in a city. In one pharmacy the medicine is more expensive, in another it is cheaper. To calculate the average cost of analgin in the city, we will use the following formula:
If you need to meet several conditions, then you can always use the statistical function AVERAGEIFS, which allows you to calculate the arithmetic mean of cells that meet two or more criteria.
MAX()
Statistical function MAX returns the largest value in a range of cells:
MIN ()
Statistical function MIN returns the smallest value in a range of cells:
LARGE ()
Returns the nth largest value from an array of numeric data. For example, in the image below, we found the fifth largest value from the list.
To verify this, you can sort the numbers in ascending order:
LEAST()
Returns the n-th smallest value from an array of numeric data. For example, in the picture below, we found the fourth smallest value from the list.
If you sort the numbers in ascending order, then everything becomes much more obvious:
MEDIAN()
Statistical function MEDIAN returns the median from given array numerical data. The median is the number that is the middle of the number set. If the list contains an odd number of values, the function returns what is exactly in the middle. If the number of values is even, then the function returns the average of the two numbers.
For example, in the image below, the formula returns the median for a list of 14 numbers.
If you sort the values in ascending order, then everything becomes much clearer:
FASHION()
Returns the most common value in an array of numeric data.
If you sort the numbers in ascending order, then everything becomes much clearer:
Statistical function FASHION on the this moment outdated, more precisely, its form of recording is outdated. The function is now used instead FASHION. ONE... Record form FASHION also supported in Excel for compatibility.
As you know, the category Statistical Excel contains over 100 of the most versatile functions. But, as practice shows, the lion's share of these functions is practically not used, especially by novice users. In this lesson, we tried to introduce you to only the most popular statistical functions in Excel, which you will sooner or later be able to put into practice. I hope that this lesson was useful to you. Good luck and success in learning Excel.
In order to find the average value in Excel (it does not matter if it is a numerical, textual, percentage or other value) there are many functions. And each of them has its own characteristics and advantages. Indeed, in this task certain conditions can be set.
For example, the average values of a series of numbers in Excel are calculated using statistical functions. You can also manually enter your own formula. Let's consider various options.
How to find the arithmetic mean of numbers?
To find the arithmetic mean, add all the numbers in the set and divide the sum by the number. For example, a student's grades in computer science: 3, 4, 3, 5, 5. What goes beyond a quarter: 4. We found the arithmetic mean by the formula: = (3 + 4 + 3 + 5 + 5) / 5.
How to do it quickly with Excel functions? Take, for example, the series random numbers in line:
Or: let's do active cell and just manually enter the formula: = AVERAGE (A1: A8).
Now let's see what else the AVERAGE function can do.
Find the arithmetic mean of the first two and last three numbers. Formula: = AVERAGE (A1: B1; F1: H1). Result:
Average by condition
The condition for finding the arithmetic mean can be a numerical criterion or a text one. We will use the function: = AVERAGEIF ().
Find the mean arithmetic numbers that are greater than or equal to 10.
Function: = AVERAGEIF (A1: A8, "> = 10")
The result of using the AVERAGEIF function by the "> = 10" condition: The third argument - "Averaging range" - is omitted. First, it is optional. Secondly, the range analyzed by the program contains ONLY numeric values. The cells specified in the first argument will be searched by the condition specified in the second argument.
Attention! The search criterion can be specified in the cell. And in the formula make a link to it.
Let's find the average value of the numbers according to the text criterion. For example, the average sales of the "tables" product.
The function will look like this: = AVERAGEIF ($ A $ 2: $ A $ 12; A7; $ B $ 2: $ B $ 12). Range - a column with product names. The search criterion is a link to a cell with the word "tables" (you can insert the word "tables" itself instead of link A7). Averaging range - those cells from which data will be taken to calculate the average.
As a result of calculating the function, we get the following value:
Attention! For a text criterion (condition), the averaging range must be specified.
How to calculate weighted average price in Excel?
How did we know the weighted average price?
Formula: = SUMPRODUCT (C2: C12; B2: B12) / SUM (C2: C12).
Using the SUMPRODUCT formula, we find out the total revenue after the sale of the entire quantity of goods. And the SUM function sums up the quantity of goods. By dividing the total revenue from the sale of the product by the total number of units of the product, we found the weighted average price. This indicator takes into account the "weight" of each price. Its share in the total mass of values.
Standard deviation: formula in Excel
Distinguish the standard deviation by the general population and by sample. In the first case, it is the root of the general variance. In the second, from the sample variance.
To calculate this statistic, a variance formula is compiled. The root is extracted from it. But Excel has a ready-made function for finding the standard deviation.
The standard deviation is tied to the scale of the original data. This is not enough for a figurative representation of the variation of the analyzed range. The coefficient of variation is calculated to obtain the relative level of data variance:
standard deviation / arithmetic mean
The formula in Excel looks like this:
STDEVP (value range) / AVERAGE (value range).
The coefficient of variation is calculated as a percentage. Therefore, we set the percentage format in the cell.
