In order to find the average value in Excel (whether it is a numerical, textual, percentage or other value), there are many functions. And each of them has its own characteristics and advantages. After all, certain conditions can be set in this task.
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, you 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 for a quarter: 4. We found the arithmetic mean using the formula: \u003d (3 + 4 + 3 + 5 + 5) / 5.
How to do it quickly using Excel functions? Take for example a series of random numbers in a string:
Or: make the cell active and simply 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 arithmetic mean of numbers that are greater than or equal to 10.
Function: =AVERAGEIF(A1:A8,">=10")
The result of using the AVERAGEIF function on the condition ">=10": The third argument - "Averaging range" - is omitted. First, it is not required. Secondly, the range parsed by the program contains ONLY numeric values. In the cells specified in the first argument, the search will be performed according to the condition specified in the second argument.
Attention! The search criterion can be specified in a cell. And in the formula to make a reference to it.
Let's find the average value of the numbers by the text criterion. For example, the average sales of the product "tables".
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" instead of the link A7). Averaging range - those cells from which data will be taken to calculate the average value.
As a result of calculating the function, we obtain the following value:
Attention! For a text criterion (condition), the averaging range must be specified.
How to calculate the weighted average price in Excel?
How do 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 goods by the total number of units of goods, 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 between the standard deviation for the general population and for the sample. In the first case, this is the root of the general variance. In the second, from the sample variance.
To calculate this statistical indicator, a dispersion formula is compiled. The root is taken from it. But in Excel there is a ready-made function for finding the standard deviation.
The standard deviation is linked to the scale of the source data. This is not enough for a figurative representation of the variation of the analyzed range. To get the relative level of scatter in the data, the coefficient of variation is calculated:
standard deviation / arithmetic mean
The formula in Excel looks like this:
STDEV (range of values) / AVERAGE (range of values).
The coefficient of variation is calculated as a percentage. Therefore, we set the percentage format in the cell.
What is the arithmetic mean
The arithmetic mean of several values is the ratio of the sum of these values to their number.
The arithmetic mean of a certain series of numbers is called the sum of all these numbers, divided by the number of terms. Thus, the arithmetic mean is the average value of the number series.
What is the arithmetic mean of several numbers? And they are equal to the sum of these numbers, which is divided by the number of terms in this sum.
How to find the arithmetic mean
There is nothing difficult in calculating or finding the arithmetic mean of several numbers, it is enough to add all the numbers presented, and divide the resulting sum by the number of terms. The result obtained will be the arithmetic mean of these numbers.
Let's consider this process in more detail. What do we need to do to calculate the arithmetic mean and get the final result of this number.
First, to calculate it, you need to determine a set of numbers or their number. This set can include large and small numbers, and their number can be anything.
Secondly, all these numbers need to be added up and get their sum. Naturally, if the numbers are simple and their number is small, then the calculations can be done by writing by hand. And if the set of numbers is impressive, then it is better to use a calculator or spreadsheet.
And, fourthly, the amount obtained from addition must be divided by the number of numbers. As a result, we get the result, which will be the arithmetic mean of this series.
What is the arithmetic mean for?
The arithmetic mean can be useful not only for solving examples and problems in mathematics lessons, but for other purposes necessary in a person’s daily life. Such goals can be the calculation of the arithmetic mean to calculate the average expense of finances per month, or to calculate the time you spend on the road, also in order to find out attendance, productivity, speed, productivity and much more.
So, for example, let's try to calculate how much time you spend commuting to school. Going to school or returning home, you spend different time on the road each time, because when you are in a hurry, you go faster, and therefore the road takes less time. But, returning home, you can go slowly, talking with classmates, admiring nature, and therefore it will take more time for the road.
Therefore, you will not be able to accurately determine the time spent on the road, but thanks to the arithmetic mean, you can approximately find out the time you spend on the road.
Let's say that on the first day after the weekend, you spent fifteen minutes on the way from home to school, on the second day your journey took twenty minutes, on Wednesday you covered the distance in twenty-five minutes, in the same time you made your way on Thursday, and on Friday you were in no hurry and returned for half an hour.
Let's find the arithmetic mean, by adding the time, for all five days. So,
15 + 20 + 25 + 25 + 30 = 115
Now divide this amount by the number of days
Through this method, you have learned that the journey from home to school takes approximately twenty-three minutes of your time.
Homework
1. Using simple calculations, find the arithmetic average of the attendance of students in your class per week.
2. Find the arithmetic mean:
3. Solve the problem:
) and sample mean (samples).
Encyclopedic YouTube
-
1 / 5
Denote the set of data X = (x 1 , x 2 , …, x n), then the sample mean is usually denoted by a horizontal bar over the variable (, pronounced " x with a dash").
The Greek letter μ is used to denote the arithmetic mean of the entire population. For a random quantity , for which the mean value is determined, μ is probability mean or mathematical expectation of a random variable. If the set X is a collection of random numbers with a probability mean μ, then for any sample x i from this collection μ = E( x i) is the mathematical expectation of this sample.
In practice, the difference between μ and x ¯ (\displaystyle (\bar (x))) in that μ is a typical variable, because you can see the sample rather than the entire population. Therefore, if the sample is presented randomly (in terms of probability theory), then x ¯ (\displaystyle (\bar (x)))(but not μ) can be treated as a random variable having a probability distribution on the sample (probability distribution of the mean).
Both of these quantities are calculated in the same way:
x ¯ = 1 n ∑ i = 1 n x i = 1 n (x 1 + ⋯ + x n) . (\displaystyle (\bar (x))=(\frac (1)(n))\sum _(i=1)^(n)x_(i)=(\frac (1)(n))(x_ (1)+\cdots +x_(n)).)Examples
- For three numbers, you need to add them and divide by 3:
- For four numbers, you need to add them and divide by 4:
Or easier 5+5=10, 10:2. Because we added 2 numbers, which means that how many numbers we add, we divide by that much.
Continuous random variable
f (x) ¯ [ a ; b ] = 1 b − a ∫ a b f (x) d x (\displaystyle (\overline (f(x)))_()=(\frac (1)(b-a))\int _(a)^(b) f(x)dx)Some problems of using the average
Lack of robustness
Although the arithmetic mean is often used as means or central trends, this concept does not apply to robust statistics, which means that the arithmetic mean is strongly influenced by "large deviations". It is noteworthy that for distributions with a large coefficient of skewness, the arithmetic mean may not correspond to the concept of “mean”, and the values of the mean from robust statistics (for example, the median) may better describe the central trend.
The classic example is the calculation of the average income. The arithmetic mean can be misinterpreted as the median, which can lead to the conclusion that there are more people with more income than there really are. "Mean" income is interpreted in such a way that most people's incomes are close to this number. This "average" (in the sense of the arithmetic mean) income is higher than the income of most people, since a high income with a large deviation from the average makes the arithmetic mean strongly skewed (in contrast, the median income "resists" such a skew). However, this "average" income says nothing about the number of people near the median income (and says nothing about the number of people near the modal income). However, if the concepts of "average" and "majority" are taken lightly, then the wrong conclusion can be drawn that most people have incomes higher than they actually are. For example, a report on the "average" net income in Medina, Washington, calculated as the arithmetic average of all annual net incomes of residents, will give a surprisingly large number due to Bill Gates. Consider the sample (1, 2, 2, 2, 3, 9). The arithmetic mean is 3.17, but five of the six values are below this mean.
Compound interest
If numbers multiply, but not fold, you need to use the geometric mean, not the arithmetic mean. Most often, this incident happens when calculating the payback investments in finance.
For example, if stocks fell 10% in the first year and rose 30% in the second year, then it is incorrect to calculate the "average" increase over those two years as the arithmetic mean (−10% + 30%) / 2 = 10%; the correct average in this case is given by the compound annual growth rate, from which the annual growth is only about 8.16653826392% ≈ 8.2%.
The reason for this is that percentages have a new starting point each time: 30% is 30% from a number less than the price at the beginning of the first year: if the stock started at $30 and fell 10%, it is worth $27 at the start of the second year. If the stock is up 30%, it is worth $35.1 at the end of the second year. The arithmetic average of this growth is 10%, but since the stock has only grown by $5.1 in 2 years, an average increase of 8.2% gives a final result of $35.1:
[$30 (1 - 0.1) (1 + 0.3) = $30 (1 + 0.082) (1 + 0.082) = $35.1]. If we use the arithmetic mean of 10% in the same way, we will not get the actual value: [$30 (1 + 0.1) (1 + 0.1) = $36.3].
Compound interest at the end of year 2: 90% * 130% \u003d 117%, that is, a total increase of 17%, and the average annual compound interest 117 % ≈ 108.2 % (\displaystyle (\sqrt (117\%))\approx 108.2\%), that is, an average annual increase of 8.2%. This number is incorrect for two reasons.
The average value for a cyclic variable, calculated according to the above formula, will be artificially shifted relative to the real average to the middle of the numerical range. Because of this, the average is calculated in a different way, namely, the number with the smallest variance (center point) is chosen as the average value. Also, instead of subtracting, modulo distance (i.e., circumferential distance) is used. For example, the modular distance between 1° and 359° is 2°, not 358° (on a circle between 359° and 360°==0° - one degree, between 0° and 1° - also 1°, in total - 2 °).
Answer: everyone got a 4 pears.Example 2. 15 people attended English courses on Monday, 10 on Tuesday, 12 on Wednesday, 11 on Thursday, 7 on Friday, 14 on Saturday, and 8 on Sunday. Find the average course attendance for the week.
Solution: Let's find the arithmetic mean:
Answer: on average, English language courses came 11 person per day.15 + 10 + 12 + 11 + 7 + 14 + 8 = 77 = 11 7 7 Example 3. A driver drove for two hours at a speed of 120 km/h and an hour at a speed of 90 km/h. Find the average speed of the car during the race.
Solution: Let's find the arithmetic mean of car speeds for each hour of travel:
Answer: the average speed of the car during the race was 110 km/h120 + 120 + 90 = 330 = 110 3 3 Example 4. The arithmetic mean of 3 numbers is 6, and the arithmetic mean of 7 other numbers is 3. What is the arithmetic mean of these ten numbers?
Solution: Since the arithmetic mean of 3 numbers is 6, then their sum is 6 3 = 18, similarly, the sum of the remaining 7 numbers is 7 3 = 21.
So the sum of all 10 numbers will be 18 + 21 = 39, and the arithmetic mean is
Answer: the arithmetic mean of 10 numbers is 3.9 .39 = 3.9 10 The topic of arithmetic and geometric mean is included in the mathematics program for grades 6-7. Since the paragraph is quite simple to understand, it is quickly passed, and by the end of the school year, students forget it. But knowledge in basic statistics is needed to pass the exam, as well as for international SAT exams. And for everyday life, developed analytical thinking never hurts.
How to calculate the arithmetic and geometric mean of numbers
Suppose there is a series of numbers: 11, 4, and 3. The arithmetic mean is the sum of all numbers divided by the number of given numbers. That is, in the case of numbers 11, 4, 3, the answer will be 6. How is 6 obtained?
Solution: (11 + 4 + 3) / 3 = 6
The denominator must contain a number equal to the number of numbers whose average is to be found. The sum is divisible by 3, since there are three terms.
Now we need to deal with the geometric mean. Let's say there is a series of numbers: 4, 2 and 8.
The geometric mean is the product of all given numbers, which is under the root with a degree equal to the number of given numbers. That is, in the case of numbers 4, 2 and 8, the answer is 4. Here's how it happened:
Solution: ∛(4 × 2 × 8) = 4
In both options, whole answers were obtained, since special numbers were taken as an example. This is not always the case. In most cases, the answer has to be rounded or left at the root. For example, for the numbers 11, 7, and 20, the arithmetic mean is ≈ 12.67, and the geometric mean is ∛1540. And for the numbers 6 and 5, the answers, respectively, will be 5.5 and √30.
Can it happen that the arithmetic mean becomes equal to the geometric mean?
Of course it can. But only in two cases. If there is a series of numbers consisting only of either ones or zeros. It is also noteworthy that the answer does not depend on their number.
Proof with units: (1 + 1 + 1) / 3 = 3 / 3 = 1 (arithmetic mean).
∛(1 × 1 × 1) = ∛1 = 1 (geometric mean).
Proof with zeros: (0 + 0) / 2=0 (arithmetic mean).
√(0 × 0) = 0 (geometric mean).
There is no other option and there cannot be.
