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 mean arithmetic 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.
Average salary… Average life expectancy… Almost every day we hear these phrases used to describe many with one singular. But oddly enough, "average value" is a rather insidious concept, often misleading an ordinary person who is inexperienced in mathematical statistics.
What is the problem?
The average value most often means the arithmetic mean, which varies greatly under the influence of single facts or events. And you won't get a real idea of how exactly the values you're learning are distributed.
Let's take a classic example of the average salary.
An abstract company has ten employees. Nine of them receive a salary of about 50,000 rubles, and one 1,500,000 rubles (by a strange coincidence, he is also the general director of this company).
The average value in this case will be 195,150 rubles, which, you see, is wrong.
What are the ways to calculate the average?
The first way is to calculate the already mentioned arithmetic mean, which is the sum of all values divided by their number.
- x – arithmetic mean;
- x n - specific value;
- n - number of values.
- Works well with normal distribution values in the sample;
- Easy to calculate;
- Intuitive.
- Doesn't give a real idea of the distribution of values;
- An unstable quantity that is easily thrown out (as in the case of the CEO).
The second way is to calculate fashion, which is the most frequently occurring value.
- M 0 - mode;
- x0 is the lower bound of the interval that contains the mode;
- n is the value of the interval;
- f m - frequency (how many times a particular value occurs in a series);
- f m-1 - the frequency of the interval preceding the modal;
- f m+1 is the frequency of the interval following the modal.
- Great for getting a sense of public opinion;
- Good for non-numeric data (colors of the season, bestsellers, ratings);
- Easy to understand.
- Fashion may simply not exist (no repetitions);
- There can be several modes (multi-modal distribution).
The third way is to calculate medians, that is, the value that divides the ordered sample into two halves and lies between them. And if there is no such value, then the arithmetic mean between the boundaries of the halves of the sample is taken as the median.
- M e is the median;
- x0 is the lower bound of the interval that contains the median;
- h is the value of the interval;
- f i - frequency (how many times a particular value occurs in a series);
- S m-1 - the sum of the frequencies of the intervals preceding the median;
- f m is the number of values in the median interval (its frequency).
- Provides the most realistic and representative estimate;
- Emission resistant.
- It is more difficult to calculate, since the sample must be ordered before calculation.
We have considered the basic methods for finding the average value, called measures of central tendency(actually there are more, but these are the most popular).
Now let's go back to our example and calculate all three variants of the average using special Excel functions:
- AVERAGE(number1;[number2];…) — function for determining the arithmetic mean;
- FASHION.ONE(number1,[number2],...) - fashion function (older versions of Excel used FASHION(number1,[number2],...));
- MEDIAN(number1;[number2];...) is a function for finding the median.
And here are the values we got:
In this case, the mode and median characterize the average salary in the company much better.
But what to do when there are not 10 values in the sample, as in the example, but millions? In Excel, this cannot be calculated, but in the database where your data is stored, no problem.
Calculate the arithmetic mean in SQL
Everything is quite simple here, since SQL provides a special aggregate function AVG .
And to use it, it is enough to write the following query:
Computing the mode in SQL
SQL does not have a separate function for finding the mode, but you can easily and quickly write it yourself. To do this, we need to find out which of the salaries is most often repeated and choose the most popular one.
Let's write a query:
/* WITH TIES must be added to TOP() if the set is multimodal, meaning the set has multiple modes */ SELECT TOP(1) WITH TIES salary AS "Salary Mode" FROM employees GROUP BY salary ORDER BY COUNT(*) DESC
Calculate the median in SQL
As with fashion, SQL does not have a built-in function for calculating the median, but it does have a generic function for calculating percentiles PERCENTILE_CONT .
It all looks like this:
/* In this case, the 0.5 percentile will be the median */ SELECT TOP(1) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER() AS "Median salary" FROM employees
It is better to read more about the work of the PERCENTILE_CONT function in the help of Microsoft and Google BigQuery .
What way to use anyway?
From the above it follows that the median The best way to calculate the average value.
But it is not always the case. If you are working with the mean, then beware of multimodal distribution:
The graph shows a bimodal distribution with two peaks. Such a situation may arise, for example, when voting in elections.
In this case, the arithmetic mean and median are values somewhere in the middle and they will not say anything about what is really happening and it is better to immediately recognize that you are dealing with a bimodal distribution by reporting two modes.
Better yet, divide the sample into two groups and collect statistical data for each.
Conclusion:
When choosing a method for finding the mean, it is necessary to take into account the presence of outliers, as well as the normal distribution of values in the sample.
The final choice of the measure of the central trend always lies with the analyst.
Average - statistic, which shows the average value of the given data array. Such an indicator is calculated as a fraction, the numerator of which is the sum of all array values, and the denominator is their number. The arithmetic mean is an important coefficient that is used in household calculations.
The meaning of the coefficient
The arithmetic mean is an elementary indicator for comparing data and calculating an acceptable value. For example, a can of beer from a particular manufacturer is sold in different stores. But in one store it costs 67 rubles, in another - 70 rubles, in the third - 65 rubles, and in the last - 62 rubles. There is a rather large range of prices, so the buyer will be interested in the average cost of a can, so that when buying a product he can compare his costs. On average, a can of beer in the city has a price:
Average price = (67 + 70 + 65 + 62) / 4 = 66 rubles.
Knowing average price, it is easy to determine where it is profitable to buy goods, and where you have to overpay.
The arithmetic mean is constantly used in statistical calculations in cases where a homogeneous data set is analyzed. In the example above, this is the price of a can of beer of the same brand. However, we cannot compare the price of beer from different manufacturers or the prices of beer and lemonade, since in this case the spread of values will be greater, the average price will be blurred and unreliable, and the very meaning of the calculations will be distorted to the caricature "average temperature in the hospital." To calculate heterogeneous data arrays, the arithmetic weighted average is used, when each value receives its own weighting factor.
Calculating the arithmetic mean
The formula for calculations is extremely simple:
P = (a1 + a2 + … an) / n,
where an is the value of the quantity, n is the total number of values.
What can be used for this indicator? The first and obvious use of it is in statistics. Almost every statistical study uses the arithmetic mean. It could be average age marriage in Russia, the average grade in a subject for a student, or the average spending on groceries per day. As mentioned above, without taking into account the weights, the calculation of averages can give strange or absurd values.
For example, the president Russian Federation made a statement that, according to statistics, the average salary of a Russian is 27,000 rubles. For most people in Russia, this level of salary seemed absurd. No wonder, if the calculation takes into account the amount of income of oligarchs, leaders industrial enterprises, big bankers on the one hand and salaries of teachers, cleaners and salesmen on the other. Even average salaries in one specialty, for example, an accountant, will have serious differences in Moscow, Kostroma and Yekaterinburg.
How to calculate averages for heterogeneous data
In payroll situations, it is important to consider the weight of each value. This means that the salaries of oligarchs and bankers would be given a weight of, for example, 0.00001, and the salaries of salespeople would be 0.12. These are numbers from the ceiling, but they roughly illustrate the prevalence of oligarchs and salesmen in Russian society.
Thus, in order to calculate the average of averages or the average value in a heterogeneous data array, it is required to use the arithmetic weighted average. Otherwise, you will receive an average salary in Russia at the level of 27,000 rubles. If you want to know your average mark in mathematics or the average number of goals scored by a selected hockey player, then the arithmetic mean calculator will suit you.
Our program is a simple and convenient calculator for calculating the arithmetic mean. You only need to enter parameter values to perform calculations.
Let's look at a couple of examples
Average Grade Calculation
Many teachers use the arithmetic mean method to determine an annual grade in a subject. Let's imagine that a child gets the following quarter grades in math: 3, 3, 5, 4. What annual grade will the teacher give him? Let's use a calculator and calculate the arithmetic mean. First, select the appropriate number of fields and enter the grade values in the cells that appear:
(3 + 3 + 5 + 4) / 4 = 3,75
The teacher will round the value in favor of the student, and the student will receive a solid four for the year.
Calculation of eaten sweets
Let's illustrate some absurdity of the arithmetic mean. Imagine that Masha and Vova had 10 sweets. Masha ate 8 candies, and Vova only 2. How many candies did each child eat on average? Using a calculator, it is easy to calculate that on average, children ate 5 sweets each, which is completely untrue and common sense. This example shows that the arithmetic mean is important for meaningful datasets.
Conclusion
The calculation of the arithmetic mean is widely used in many scientific fields. This indicator is popular not only in statistical calculations, but also in physics, mechanics, economics, medicine or finance. Use our calculators as an assistant for solving arithmetic mean problems.
Remember!
To find the arithmetic mean, you need to add all the numbers and divide their sum by their number.
Find the arithmetic mean of 2, 3 and 4 .
Let's denote the arithmetic mean by the letter "m". By the definition above, we find the sum of all numbers.
Divide the resulting amount by the number of numbers taken. We have three numbers.
As a result, we get arithmetic mean formula:
What is the arithmetic mean for?
In addition to the fact that it is constantly offered to be found in the classroom, finding the arithmetic mean is very useful in life.
For example, you decide to sell soccer balls. But since you are new to this business, it is completely incomprehensible at what price you sell balls.
Then you decide to find out at what price your competitors are already selling soccer balls in your area. Find out the prices in stores and make a table.
Prices for balls in stores turned out to be quite different. What price should we choose to sell the soccer ball?
If we choose the lowest one (290 rubles), then we will sell the goods at a loss. If you choose the highest one (360 rubles), then buyers will not purchase soccer balls from us.
We need an average price. Here comes to the rescue average.
Calculate the arithmetic mean of the prices for soccer balls:
average price =
=
290 + 360 + 310
3
= 320
rub.960
3
Thus, we got the average price (320 rubles), at which we can sell a soccer ball not too cheap and not too expensive.
Average moving speed
Closely related to the arithmetic mean is the concept average speed.
Observing the movement of traffic in the city, you can see that the cars either accelerate and travel at high speed, then slow down and travel at low speed.
There are many such sections along the route of vehicles. Therefore, for the convenience of calculations, the concept of average speed is used.
Remember!
The average speed of movement is the total distance traveled divided by the total time of movement.
Consider the problem for the average speed.
Task number 1503 from the textbook "Vilenkin Grade 5"
The car traveled 3.2 hours on a highway at a speed of 90 km/h, then 1.5 hours on a dirt road at a speed of 45 km/h, and finally 0.3 hours on a country road at a speed of 30 km/h. Find the average speed of the car for the entire journey.
To calculate the average speed of movement, you need to know the entire distance traveled by the car, and the entire time that the car was moving.
S 1 \u003d V 1 t 1S 1 \u003d 90 3.2 \u003d 288 (km)
- highway.S 2 \u003d V 2 t 2
S 2 \u003d 45 1.5 \u003d 67.5 (km) - dirt road.
S 3 \u003d V 3 t 3
S 3 \u003d 30 0.3 \u003d 9 (km) - country road.
S = S 1 + S 2 + S 3
S \u003d 288 + 67.5 + 9 \u003d 364.5 (km) - the entire path traveled by the car.
T \u003d t 1 + t 2 + t 3
T \u003d 3.2 + 1.5 + 0.3 \u003d 5 (h) - all the time.
V cf \u003d S: t
V cf \u003d 364.5: 5 \u003d 72.9 (km / h) - average speed vehicle movement.
Answer: V av = 72.9 (km / h) - the average speed of the car.
In mathematics, the arithmetic mean of numbers (or simply the average) is the sum of all the numbers in a given set divided by their number. This is the most generalized and widespread concept of the average value. As you already understood, in order to find the average value, you need to sum up all the numbers given to you, and divide the result by the number of terms.
What is the arithmetic mean?
Let's look at an example.
Example 1. Numbers are given: 6, 7, 11. You need to find their average value.
Solution.
First, let's find the sum of all given numbers.
Now we divide the resulting sum by the number of terms. Since we have three terms, respectively, we will divide by three.
Therefore, the average of the numbers 6, 7 and 11 is 8. Why 8? Yes, because the sum of 6, 7 and 11 will be the same as three eights. This is clearly seen in the illustration.
The average value is somewhat reminiscent of the "alignment" of a series of numbers. As you can see, the piles of pencils have become one level.
Consider another example to consolidate the knowledge gained.
Example 2 Numbers are given: 3, 7, 5, 13, 20, 23, 39, 23, 40, 23, 14, 12, 56, 23, 29. You need to find their arithmetic mean.
Solution.
We find the sum.
3 + 7 + 5 + 13 + 20 + 23 + 39 + 23 + 40 + 23 + 14 + 12 + 56 + 23 + 29 = 330
Divide by the number of terms (in this case, 15).
Therefore, the average value of this series of numbers is 22.
Now consider negative numbers. Let's remember how to sum them up. For example, you have two numbers 1 and -4. Let's find their sum.
1 + (-4) = 1 – 4 = -3
Knowing this, consider another example.
Example 3 Find the average value of a series of numbers: 3, -7, 5, 13, -2.
Solution.
Finding the sum of numbers.
3 + (-7) + 5 + 13 + (-2) = 12
Since there are 5 terms, we divide the resulting sum by 5.
Therefore, the arithmetic mean of the numbers 3, -7, 5, 13, -2 is 2.4.
In our time of technological progress, it is much more convenient to use to find the average value computer programs. Microsoft Office Excel is one of them. Finding the average in Excel is quick and easy. Moreover, this program is included in the software package from Microsoft Office. Consider a brief instruction on how to find the arithmetic mean using this program.
In order to calculate the average value of a series of numbers, you must use the AVERAGE function. The syntax for this function is:
=Average(argument1, argument2, ... argument255)
where argument1, argument2, ... argument255 are either numbers or cell references (cells mean ranges and arrays).
To make it clearer, let's test the knowledge gained.
- Enter the numbers 11, 12, 13, 14, 15, 16 in cells C1 - C6.
- Select cell C7 by clicking on it. In this cell, we will display the average value.
- Click on the "Formulas" tab.
- Select More Functions > Statistical to open the drop down list.
- Select AVERAGE. After that, a dialog box should open.
- Select and drag cells C1-C6 there to set the range in the dialog box.
- Confirm your actions with the "OK" button.
- If you did everything correctly, in cell C7 you should have the answer - 13.7. When you click on cell C7, the function (=Average(C1:C6)) will be displayed in the formula bar.
It is very useful to use this function for accounting, invoices, or when you just need to find the average of a very long range of numbers. Therefore, it is often used in offices and large companies. This allows you to keep the records in order and makes it possible to quickly calculate something (for example, the average income per month). You can also use Excel to find the mean of a function.
Average
This term has other meanings, see the average meaning.Average(in mathematics and statistics) sets of numbers - the sum of all numbers divided by their number. It is one of the most common measures of central tendency.
It was proposed (along with the geometric mean and harmonic mean) by the Pythagoreans.
Special cases of the arithmetic mean are the mean (of the general population) and the sample mean (of samples).
Introduction
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 (x ¯ (\displaystyle (\bar (x))) , pronounced " x with a dash").
The Greek letter μ is used to denote the arithmetic mean of the entire population. For random variable, for which the mean value is defined, μ is probability mean or expected value 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 expectation of this sample.
In practice, the difference between μ and x ¯ (\displaystyle (\bar (x))) is that μ is a typical variable because you can see a selection rather than the whole general population. Therefore, if the sample is represented 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)).)
If X is a random variable, then the mathematical expectation X can be considered as the arithmetic mean of the values in repeated measurements of the quantity X. This is a manifestation of the law big numbers. Therefore, the sample mean is used to estimate the unknown mathematical expectation.
In elementary algebra, it is proved that the mean n+ 1 numbers above average n numbers if and only if the new number is greater than the old average, less if and only if the new number is less than the average, and does not change if and only if the new number is equal to the average. The more n, the smaller the difference between the new and old averages.
Note that there are several other "means" available, including power-law mean, Kolmogorov mean, harmonic mean, arithmetic-geometric mean, and various weighted means (e.g., arithmetic-weighted mean, geometric-weighted mean, harmonic-weighted mean).
Examples
- For three numbers Add them up 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
For a continuously distributed value f (x) (\displaystyle f(x)) the arithmetic mean on the interval [ a ; b ] (\displaystyle ) is defined via a definite integral:
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
Main article: Robustness in statisticsAlthough 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 subject to strong influence"large deviations". It is noteworthy that for distributions with a large skewness, the arithmetic mean may not correspond to the concept of “average”, 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 a 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 one can incorrectly conclude 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 high 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
Main article: ROIIf numbers multiply, but not fold, you need to use the geometric mean, not the arithmetic mean. Most often, this incident happens when calculating the return on investment 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 these 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% = 117% , i.e. a total increase of 17%, and the average annual compound interest is 117% ≈ 108.2% (\displaystyle (\sqrt (117\%))\approx 108.2\%) , that is, an average annual increase of 8.2%.
Directions
Main article: Destination statisticsWhen calculating the arithmetic mean of some variable that changes cyclically (for example, phase or angle), special care should be taken. For example, the average of 1° and 359° would be 1 ∘ + 359 ∘ 2 = (\displaystyle (\frac (1^(\circ )+359^(\circ ))(2))=) 180°. This number is incorrect for two reasons.
- First, angular measures are only defined for the range from 0° to 360° (or from 0 to 2π when measured in radians). Thus, the same pair of numbers could be written as (1° and −1°) or as (1° and 719°). The averages of each pair will be different: 1 ∘ + (− 1 ∘) 2 = 0 ∘ (\displaystyle (\frac (1^(\circ )+(-1^(\circ )))(2))=0 ^(\circ )) , 1 ∘ + 719 ∘ 2 = 360 ∘ (\displaystyle (\frac (1^(\circ )+719^(\circ ))(2))=360^(\circ )) .
- Second, in this case, a value of 0° (equivalent to 360°) would be the geometrically best mean, since the numbers deviate less from 0° than from any other value (value 0° has the smallest variance). Compare:
- the number 1° deviates from 0° by only 1°;
- the number 1° deviates from the calculated average of 180° by 179°.
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 °).
Weighted average - what is it and how to calculate it?
In the process of studying mathematics, students get acquainted with the concept of the arithmetic mean. In the future, in statistics and some other sciences, students are also faced with the calculation of other averages. What can they be and how do they differ from each other?
Averages: Meaning and Differences
Not always accurate indicators give an understanding of the situation. In order to assess this or that situation, it is sometimes necessary to analyze a huge number of figures. And then averages come to the rescue. They allow you to assess the situation in general.
Since school days, many adults remember the existence of the arithmetic mean. It is very easy to calculate - the sum of a sequence of n terms is divisible by n. That is, if you need to calculate the arithmetic mean in the sequence of values 27, 22, 34 and 37, then you need to solve the expression (27 + 22 + 34 + 37) / 4, since 4 values \u200b\u200bare used in the calculations. In this case, the desired value will be equal to 30.
Often within school course study the geometric mean. The calculation of this value is based on extracting the root of the nth degree from the product of n terms. If we take the same numbers: 27, 22, 34 and 37, then the result of the calculations will be 29.4.
harmonic mean in general education school usually not the subject of study. However, it is used quite often. This value is the reciprocal of the arithmetic mean and is calculated as a quotient of n - the number of values and the sum 1/a 1 +1/a 2 +...+1/a n . If we again take the same series of numbers for calculation, then the harmonic will be 29.6.
Weighted Average: Features
However, all of the above values may not be used everywhere. For example, in statistics, when calculating some average values, the "weight" of each number used in the calculation plays an important role. The results are more revealing and correct because they take into account more information. This group of values is collectively referred to as the "weighted average". They are not passed at school, so it is worth dwelling on them in more detail.
First of all, it is worth explaining what is meant by the "weight" of a particular value. The easiest way to explain this is to specific example. The body temperature of each patient is measured twice a day in the hospital. Of the 100 patients in different departments of the hospital, 44 will have a normal temperature - 36.6 degrees. Another 30 will have an increased value - 37.2, 14 - 38, 7 - 38.5, 3 - 39, and the remaining two - 40. And if we take the arithmetic mean, then this value in general for the hospital will be over 38 degrees! But almost half of the patients have a completely normal temperature. And here it would be more correct to use the weighted average, and the "weight" of each value will be the number of people. In this case, the result of the calculation will be 37.25 degrees. The difference is obvious.
In the case of weighted average calculations, the "weight" can be taken as the number of shipments, the number of people working on a given day, in general, anything that can be measured and affect the final result.
Varieties
The weighted average corresponds to the arithmetic average discussed at the beginning of the article. However, the first value, as already mentioned, also takes into account the weight of each number used in the calculations. In addition, there are also weighted geometric and harmonic values.
There is another interesting variety used in series of numbers. This is a weighted moving average. It is on its basis that trends are calculated. In addition to the values themselves and their weight, periodicity is also used there. And when calculating the average value at some point in time, values for previous time periods are also taken into account.
Calculating all these values is not that difficult, but in practice, only the usual weighted average is usually used.
Calculation methods
In the age of computerization, there is no need to manually calculate the weighted average. However, it would be useful to know the calculation formula so that you can check and, if necessary, correct the results obtained.
It will be easiest to consider the calculation on a specific example.
It is necessary to find out what is the average wage at this enterprise, taking into account the number of workers receiving a particular salary.
So, the calculation of the weighted average is carried out using the following formula:
x = (a 1 *w 1 +a 2 *w 2 +...+a n *w n)/(w 1 +w 2 +...+w n)
For example, the calculation would be:
x = (32*20+33*35+34*14+40*6)/(20+35+14+6) = (640+1155+476+240)/75 = 33.48
Obviously, there is no particular difficulty in manually calculating the weighted average. The formula for calculating this value in one of the most popular applications with formulas - Excel - looks like the SUMPRODUCT (series of numbers; series of weights) / SUM (series of weights) function.
How to find average value in excel?
how to find arithmetic mean in excel?
Vladimir09854
As easy as pie. In order to find the average value in excel, you only need 3 cells. In the first we write one number, in the second - another. And in the third cell, we will score a formula that will give us the average value between these two numbers from the first and second cells. If cell No. 1 is called A1, cell No. 2 is called B1, then in the cell with the formula you need to write like this:
This formula calculates the arithmetic mean of two numbers.
For the beauty of our calculations, we can highlight the cells with lines, in the form of a plate.
There is also a function in Excel itself to determine the average value, but I use the old-fashioned method and enter the formula I need. Thus, I am sure that Excel will calculate exactly as I need, and will not come up with some kind of rounding of its own.
M3sergey
This is very easy if the data is already entered into the cells. If you are just interested in a number, just select the desired range / ranges, and the value of the sum of these numbers, their arithmetic mean and their number will appear in the status bar at the bottom right.
You can select an empty cell, click on the triangle (drop-down list) "Autosum" and select "Average" there, after which you will agree with the proposed range for calculation, or choose your own.
Finally, you can use the formulas directly - click "Insert Function" next to the formula bar and cell address. The AVERAGE function is in the "Statistical" category, and takes as arguments both numbers and cell references, etc. There you can also choose more complex options, for example, AVERAGEIF - calculation of the average by condition.
Find average in excel is a fairly simple task. Here you need to understand whether you want to use this average value in some formulas or not.
If you need to get only the value, then it is enough to select the required range of numbers, after which excel will automatically calculate the average value - it will be displayed in the status bar, the heading "Average".
In the case when you want to use the result in formulas, you can do this:
1) Sum the cells using the SUM function and divide it all by the number of numbers.
2) A more correct option is to use a special function called AVERAGE. The arguments to this function can be numbers given sequentially, or a range of numbers.
Vladimir Tikhonov
circle the values that will be involved in the calculation, click the "Formulas" tab, there you will see "AutoSum" on the left and next to it a triangle pointing down. click on this triangle and choose "Average". Voila, done) at the bottom of the column you will see the average value :)
Ekaterina Mutalapova
Let's start at the beginning and in order. What does average mean?
The mean value is the value that is the arithmetic mean, i.e. is calculated by adding a set of numbers and then dividing the total sum of numbers by their number. For example, for the numbers 2, 3, 6, 7, 2 it will be 4 (the sum of the numbers 20 is divided by their number 5)
In an Excel spreadsheet, for me personally, the easiest way was to use the formula =AVERAGE. To calculate the average value, you need to enter data into the table, write the function =AVERAGE() under the data column, and in brackets indicate the range of numbers in the cells, highlighting the column with the data. After that, press ENTER, or simply left-click on any cell. The result will be displayed in the cell below the column. On the face of it, the description is incomprehensible, but in fact it is a matter of minutes.
Adventurer 2000
The Excel program is multi-faceted, so there are several options that will allow you to find the average:
First option. You simply sum all the cells and divide by their number;
Second option. Use a special command, write in the required cell the formula "=AVERAGE (and here specify the range of cells)";
Third option. If you select the required range, then note that on the page below, the average value in these cells is also displayed.
Thus, there are a lot of ways to find the average value, you just need to choose the best one for you and use it constantly.
In Excel, using the AVERAGE function, you can calculate the simple arithmetic mean. To do this, you need to enter a number of values. Press equals and select in the Statistical category, among which select the AVERAGE function
Also, using statistical formulas, you can calculate the arithmetic weighted average, which is considered more accurate. To calculate it, we need the values of the indicator and the frequency.
How to find the average in Excel?
The situation is this. There is the following table:
The columns shaded in red contain the numerical values of the grades for the subjects. In the column " Average score"It is required to calculate their average value.
The problem is this: there are 60-70 objects in total and some of them are on another sheet.
I looked in another document, the average has already been calculated, and in the cell there is a formula like
="sheet name"!|E12
but this was done by some programmer who got fired.
Tell me, please, who understands this.
Hector
In the line of functions, you insert "AVERAGE" from the proposed functions and choose from where they need to be calculated (B6: N6) for Ivanov, for example. I don’t know for sure about neighboring sheets, but for sure this is contained in the standard Windows help
Tell me how to calculate the average value in Word
Please tell me how to calculate the average value in Word. Namely, the average value of the ratings, and not the number of people who received ratings. 
Yulia pavlova
Word can do a lot with macros. Press ALT+F11 and write a macro program..
In addition, Insert-Object... will allow you to use other programs, even Excel, to create a sheet with a table inside a Word document.
But in this case, you need to write down your numbers in the table column, and put the average in the bottom cell of the same column, right?
To do this, insert a field into the bottom cell.
Insert-Field...-Formula
Field content
[=AVERAGE(ABOVE)]
returns the average of the sum of the cells above.
If the field is selected and the right mouse button is pressed, then it can be Updated if the numbers have changed,
view the code or field value, change the code directly in the field.
If something goes wrong, delete the entire field in the cell and re-create it.
AVERAGE means average, ABOVE - about, that is, a row of cells above.
I did not know all this myself, but I easily found it in HELP, of course, thinking a little.
