☰

Find out the average. How to calculate the arithmetic mean

In order to find the average value in Excel (it does not matter numerical, text, 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 \u200b\u200bof 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: \u003d (3 + 4 + 3 + 5 + 5) / 5.

How to do it quickly with Excel functions? Take, for example, a series of random numbers in a string:

Or: make a cell active and simply manually enter the formula: \u003d 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: \u003d AVERAGE (A1: B1; F1: H1). Result:



Average by condition

The condition for finding the arithmetic mean can be a numerical criterion or text. We will use the function: \u003d AVERAGEIF ().

Find the arithmetic mean of numbers greater than or equal to 10.

Function: \u003d AVERAGEIF (A1: A8, "\u003e \u003d 10")


The result of using the AVERAGEIF function by the "\u003e \u003d 10" condition:

The third argument - "Averaging range" - is omitted. First, it is not required. 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! Search criteria 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: \u003d 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: \u003d 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 between the standard deviation for the general population and for the 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.

Average salary ... Average life expectancy ... Almost every day we hear these phrases used to describe a set of one singular... But oddly enough, "average value" is a rather insidious concept, often misleading an ordinary person inexperienced in mathematical statistics.

What is the problem?

The average value most often means the arithmetic mean, which varies greatly under the influence of isolated facts or events. And you won't get a real idea of \u200b\u200bhow exactly the values \u200b\u200bthat you are studying are distributed.

Let's take a classic example with an average salary.

Some abstract company has ten employees. Nine of them receive a salary of about 50,000 rubles, and one of them is 1,500,000 rubles (by a strange coincidence, it is director general this company).

The average value in this case will be 195,150 rubles, which is wrong, you must admit.

What methods of calculating the average are there?

The first way is to calculate the already mentioned arithmetic mean, which is the sum of all values \u200b\u200bdivided by their number.

  • x - arithmetic mean;
  • x n - specific meaning;
  • n - number of values.
  • Works well with a normal distribution of sample values;
  • Easy to calculate;
  • Intuitive.
  • Doesn't give a real idea of \u200b\u200bthe distribution of values;
  • An unstable quantity that is easily emitted (as is the case with the CEO).

The second way is to calculate fashion, that is, the most common value.

  • M 0 - fashion;
  • x 0 - the lower boundary of the interval that contains the fashion;
  • n is the size of the interval;
  • f m - frequency (how many times in a row occurs this or that value);
  • f m-1 - frequency of the interval preceding the modal;
  • f m + 1 - frequency of the interval following the modal.
  • Great for getting an idea of \u200b\u200bpublic opinion;
  • Well suited for non-numeric data (season colors, bestsellers, ratings);
  • Easy to understand.
  • Fashion may simply not be (no repetitions);
  • There can be several mods (multimodal distribution).

The third way is to calculate medians, that is, the value that divides the ordered sample into two halves and is 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 - median;
  • x 0 - the lower boundary of the interval that contains the median;
  • h is the size of the interval;
  • f i - frequency (how many times in a row this or that value occurs);
  • S m-1 - the sum of the frequencies of the intervals preceding the median;
  • f m is the number of values \u200b\u200bin the median interval (its frequency).
  • Provides the most realistic and representative assessment;
  • Resistant to emissions.
  • It is more difficult to compute, since the sample must be ordered before computation.

We have covered the main methods for finding the average, called measures of the central trend(in fact, there are more of them, 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];…) - a function for determining the arithmetic mean;
  • FASHION.ONE (number1; [number2]; ...) - fashion function (in older versions of Excel, MODA (number1; [number2]; ...));
  • MEDIAN (number1; [number2]; ...) - a function for finding the median.

And here are the values \u200b\u200bwe got:

In this case, fashion and median characterize the average salary in a company much better.

But what to do when the sample contains not 10 values, as in the example, but millions? In Excel it is impossible to calculate, but in the database where your data is stored, no problem.

Calculating the arithmetic mean in SQL

Everything is quite simple here, since SQL provides a special AVG aggregate function.

And to use it, it is enough to write the following query:

Computing SQL fashion

There is no separate function in SQL for finding the mod, but it can be easily and quickly written by yourself. To do this, we need to find out which of the salaries is most often repeated and choose the most popular.

Let's write a request:

/ * WITH TIES must be added to TOP () if the set is multimodal, that is, the set has several modes * / SELECT TOP (1) WITH TIES salary AS "Salary mode" FROM employees GROUP BY salary ORDER BY COUNT (*) DESC

Calculating the median in SQL

As with mod, SQL does not have a built-in function for calculating the median, but there is a universal function for calculating PERCENTILE_CONT percentiles.

It all looks like this:

/ * In this case, the percentile is 0.5 and 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 how the PERCENTILE_CONT function works in the Microsoft and Google BigQuery help.

What method should you use?

From the above, it follows that the median is the best way to calculate the average.

But this is not always the case. If you are working with an average, then beware of multimodal distribution:

The graph shows a bimodal distribution with two peaks. This situation may arise, for example, when voting in elections.

In this case, the arithmetic mean and median are values \u200b\u200bthat are somewhere in between 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 statistics 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 \u200b\u200bin the sample.

The final choice of the measure of the central trend always lies with the analyst.

Remember!

To find 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 by condition.

As a result, we get arithmetic mean formula:


What is the arithmetic mean for?

In addition to the fact that it is constantly suggested to be found in the classroom, finding the arithmetic mean is very useful in life.

For example, let's say you decide to sell soccer balls. But since you are new to this business, it is completely incomprehensible at what price to sell balls to you.

Then you decide to find out at what price competitors are already selling soccer balls in your area. Let's find out the prices in stores and draw up a table.

The prices for balls in stores were completely different. What price should we choose for selling a soccer ball?

If you choose the lowest one (290 rubles), then we will sell the goods at a loss. If you choose the highest one (360 rubles), then customers will not buy soccer balls from us.

We need an average price. Here comes to the rescue average.

Let's calculate the arithmetic mean of the prices for soccer balls:

average price =

290 + 360 + 310
3
=
960
3
= 320 rub.

Thus, we got an average price (320 rubles), at which we can sell a soccer ball not too cheaply and not too expensive.

Average travel speed

Closely related to the arithmetic mean is the concept average speed.

Observing the movement of transport in the city, you can see that the cars are accelerating and going at high speed, then slowing down and going at low speed.

There are many such sections along the route of vehicles. Therefore, for the convenience of calculations, the concept of the average speed of movement is used.

Remember!

The average speed of movement is the entire distance traveled divided by the entire time of movement.

Consider a medium speed problem.

Problem number 1503 from the textbook "Vilenkin Grade 5"

The car moved for 3.2 hours on the 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 vehicle along the entire path.

To calculate the average speed, you need to know the entire distance traveled by the car, and all the time that the car was moving.

S 1 \u003d V 1 t 1

S 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 \u003d S 1 + S 2 + S 3

S \u003d 288 + 67.5 + 9 \u003d 364.5 (km) - all the way covered 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 av \u003d 364.5: 5 \u003d 72.9 (km / h) - average speed vehicle movement.

Answer: V avg \u003d 72.9 (km / h) - the average speed of the vehicle.

In mathematics, the arithmetic mean of numbers (or just the average) is the sum of all numbers in a given set, divided by their number. This is the most generalized and widespread concept of average. As you already understood, 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 arithmetic mean?

Let's take an example.

Example 1... Given numbers: 6, 7, 11. We need to find their average value.

Decision.

First, let's find the sum of all these numbers.

Now let's divide the resulting sum by the number of terms. Since we have three terms, respectively, we will divide by three.

Therefore, the average of 6, 7 and 11 is 8. Why 8? Because the sum of 6, 7 and 11 will be the same as three eights. This is clearly seen in the illustration.

The average is somewhat similar to the "alignment" of a series of numbers. As you can see, the piles of pencils have become one level.

Let's consider another example to consolidate the knowledge gained.

Example 2. Given numbers: 3, 7, 5, 13, 20, 23, 39, 23, 40, 23, 14, 12, 56, 23, 29. You need to find their arithmetic mean.

Decision.

We find the amount.

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 of this series of numbers is 22.

Now let's look at negative numbers. Let's remember how to summarize them. For example, you have two numbers 1 and -4. Let's find their sum.

1 + (-4) = 1 – 4 = -3

With this in mind, consider another example.

Example 3. Find the average value of a series of numbers: 3, -7, 5, 13, -2.

Decision.

Find the sum of the 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 Microsoft Office software package. Let's look at a short 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:
\u003d 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 try the knowledge gained.

  1. Enter the numbers 11, 12, 13, 14, 15, 16 in cells C1 - C6.
  2. Select cell C7 by clicking on it. In this cell, we will display the average value.
  3. Click on the Formulas tab.
  4. Choose More Functions\u003e Statistical to open the drop-down list.
  5. Select AVERAGE. After that, a dialog box should open.
  6. Select and drag cells C1 – C6 there to set the range in the dialog box.
  7. Confirm your actions with the "OK" key.
  8. If you did everything correctly, in cell C7 you should have the answer - 13.7. When you click on cell C7, the function (\u003d Average (C1: C6)) will be displayed in the formula bar.

It is very convenient to use this function for accounting, invoicing, or when you just need to find the average of a very long series 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). Also, using Excel, you can find the average value of the function.

Average

This term has other meanings, see mean.

Average (in mathematics and statistics) a set of numbers is the sum of all numbers divided by their number. It is one of the most common measures of the central trend.

It was proposed (along with the geometric mean and harmonic mean) by the Pythagoreans.

Particular cases of the arithmetic mean are the mean (of the general population) and the sample mean (samples).

Introduction

Let's denote the data set X = (x 1 , x 2 , …, x n), then the sample mean is usually indicated by a horizontal bar above the variable (x ¯ (\\ displaystyle (\\ bar (x))), pronounced “ x with a line ").

The Greek letter μ is used to denote the arithmetic mean of the entire population. For a random variable for which the mean value is determined, μ is probabilistic mean or the mathematical expectation of a random variable. If the set X is a collection of random numbers with a probabilistic mean μ, then for any sample x i from this collection μ \u003d E ( x i ) is the mathematical expectation of this sample.

In practice, the difference between μ and x ¯ (\\ displaystyle (\\ bar (x))) is that μ is a typical variable because you can see the sample rather than the entire population. Therefore, if the sample is presented at random (in terms of probability theory), then x ¯ (\\ displaystyle (\\ bar (x))) (but not μ) can be treated as a random variable having a probability distribution over the sample (probability distribution of the mean).

Both of these quantities are calculated in the same way:

X ¯ \u003d 1 n ∑ i \u003d 1 n x i \u003d 1 n (x 1 + ⋯ + x n). (\\ displaystyle (\\ bar (x)) \u003d (\\ frac (1) (n)) \\ sum _ (i \u003d 1) ^ (n) x_ (i) \u003d (\\ frac (1) (n)) (x_ (1) + \\ cdots + x_ (n)).)

If a X is a random variable, then the mathematical expectation X can be considered as the arithmetic mean of values \u200b\u200bin repeated measurements of a quantity X... This is a manifestation of the law of large 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 "mean" values, including power mean, Kolmogorov mean, harmonic mean, arithmetic-geometric mean, and various weighted averages (eg, weighted arithmetic mean, weighted geometric mean, harmonic weighted mean).

Examples of

  • For three numbers, add them and divide by 3:
x 1 + x 2 + x 3 3. (\\ displaystyle (\\ frac (x_ (1) + x_ (2) + x_ (3)) (3)).)
  • For four numbers, add them and divide by 4:
x 1 + x 2 + x 3 + x 4 4. (\\ displaystyle (\\ frac (x_ (1) + x_ (2) + x_ (3) + x_ (4)) (4)).)

Or more simply 5 + 5 \u003d 10, 10: 2. Because we added 2 numbers, which means how many numbers we add, we divide by so many.

Continuous random variable

For a continuously distributed quantity f (x) (\\ displaystyle f (x)), the arithmetic mean over the segment [a; b] (\\ displaystyle) is defined using the definite integral:

F (x) ¯ [a; b] \u003d 1 b - a ∫ abf (x) dx (\\ displaystyle (\\ overline (f (x))) _ () \u003d (\\ frac (1) (ba)) \\ int _ (a) ^ (b) f (x) dx)

Some problems of using the mean

Lack of robustness

Main article: Robustness in statistics

Although the arithmetic mean is often used as averages or central trends, it is not a robust statistic, which means that the arithmetic mean is strongly influenced by "large deviations." It is noteworthy that for distributions with a large skewness coefficient, the arithmetic mean may not correspond to the concept of “mean”, and the mean values \u200b\u200bfrom robust statistics (for example, the median) may better describe the central trend.

A classic example is calculating the average income. The arithmetic mean can be misinterpreted as the median, which can lead to the conclusion that there are more people with higher incomes than they actually are. “Average” income is interpreted to mean that the income of most people is 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 mean skews the arithmetic mean strongly (by contrast, the median income “resists” such a bias). However, this “average” income says nothing about the number of people near the median income (and does not say anything about the number of people near the modal income). Nevertheless, if you take lightly the concepts of "average" and "majority of the people", you can make the wrong conclusion that most people have incomes higher than they actually are. For example, a report on "average" net income in Medina, Washington, calculated as the arithmetic average of all residents' annual net incomes, would yield a surprisingly large number because of Bill Gates. Consider the sample (1, 2, 2, 2, 3, 9). The arithmetic mean is 3.17, but five out of six values \u200b\u200bare below this average.

Compound interest

Main article: Return on investment

If the numbers multiply, but not fold, you need to use the geometric mean, not the arithmetic mean. Most often, this incident occurs when calculating the return on investment in finance.

For example, if stocks fell by 10% in the first year and increased by 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 \u003d 10%; the correct average in this case is given by the cumulative annual growth rate, at 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 was at $ 30 in the beginning and fell 10%, it is at $ 27 in the beginning 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 rose by only $ 5.1 in 2 years, the average growth of 8.2% gives final result $35.1:

[$ 30 (1 - 0.1) (1 + 0.3) \u003d $ 30 (1 + 0.082) (1 + 0.082) \u003d $ 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) \u003d $ 36.3].

Compound at the end of Year 2: 90% * 130% \u003d 117% for a total increase of 17%, and an average compound interest of 117% ≈ 108.2% (\\ displaystyle (\\ sqrt (117 \\%)) \\ approx 108.2 \\%) , that is, an average annual growth of 8.2%.

Directions

Main article: Destination statistics

When 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 \u003d (\\ displaystyle (\\ frac (1 ^ (\\ circ) +359 ^ (\\ circ)) (2)) \u003d) 180 °. This number is incorrect for two reasons.

  • First, angular standards are only defined for the range 0 ° to 360 ° (or 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 average of each pair will be different: 1 ∘ + (- 1 ∘) 2 \u003d 0 ∘ (\\ displaystyle (\\ frac (1 ^ (\\ circ) + (- 1 ^ (\\ circ))) (2)) \u003d 0 ^ (\\ circ)), 1 ∘ + 719 ∘ 2 \u003d 360 ∘ (\\ displaystyle (\\ frac (1 ^ (\\ circ) +719 ^ (\\ circ)) (2)) \u003d 360 ^ (\\ circ)).
  • Second, in this case, 0 ° (equivalent to 360 °) would be the geometrically better average, since the numbers deviate less from 0 ° than from any other value (0 ° has the least variance). Compare:
    • the number 1 ° deviates from 0 ° by only 1 °;
    • the number 1 ° deviates from the calculated mean of 180 ° by 179 °.

The average value for the cyclic variable, calculated using the above formula, will be artificially shifted from the real average towards the middle of the numeric range. Because of this, the mean is calculated in a different way, namely, the number with the least variance (center point) is chosen as the mean. Also, instead of subtracting, the modular distance (that is, the circumferential distance) is used. For example, the modular distance between 1 ° and 359 ° is 2 °, not 358 ° (on a circle between 359 ° and 360 ° \u003d\u003d 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. Later in statistics and some other sciences, students are faced with the calculation of other averages. What can they be and how do they differ from each other?

Average values: meaning and differences

Not always accurate indicators give an understanding of the situation. In order to assess a particular situation, it is sometimes necessary to analyze a huge number of figures. And then averages come to the rescue. They make it possible to assess the situation as a whole.


Since school times, many adults remember the existence of the arithmetic mean. It is very easy to calculate - the sum of a sequence of n members is divisible by n. That is, if you need to calculate the arithmetic mean in a sequence of values \u200b\u200b27, 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 30.

Often, within the framework of the school course, geometric mean is also studied. The calculation of this value is based on extracting the nth root of 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 comprehensive school usually not a subject of study. Nevertheless, 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 \u200b\u200band 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 \u200b\u200bmay not be used everywhere. For example, in statistics, when calculating some average values, the "weight" of each number used in the calculations plays an important role. The results are more indicative and correct because they take into account more information. This group of values \u200b\u200bis collectively referred to as "weighted average". They do not pass at school, so it is worth dwelling on them in more detail.

First of all, it is worth telling what is meant by "weight" of this or that value. The easiest way to explain this is with a specific example. Every patient's body temperature is measured twice a day in the hospital. Out of 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 more than 38 degrees! But almost half of the patients have a completely normal temperature. And here it will be more correct to use the weighted average value, and the "weight" of each value will be the number of people. In this case, the calculation result 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 particular day, in general, anything that can be measured and affect the final result.

Varieties

The weighted average corresponds to the arithmetic mean 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 geometric and harmonic weighted mean values.

There is another interesting variation used in the series of numbers. This is a weighted moving average. It is on its basis that trends are calculated. In addition to the values \u200b\u200bthemselves and their weights, periodicity is also used there. And when calculating the average value at some point in time, the values \u200b\u200bfor the previous time intervals are also taken into account.

Calculating all of these values \u200b\u200bis not that difficult, but in practice, only the usual weighted average is usually used.

Calculation methods

In an age of massive computing, there is no need to manually calculate the weighted average. However, it will be useful to know the calculation formula so that you can check and, if necessary, correct the results obtained.

The easiest way to consider the calculation is with 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 this or that earnings.

So, the calculation of the weighted average is done using the following formula:

x \u003d (a 1 * w 1 + a 2 * w 2 + ... + a n * w n) / (w 1 + w 2 + ... + w n)

For example, the calculation will be like this:

x \u003d (32 * 20 + 33 * 35 + 34 * 14 + 40 * 6) / (20 + 35 + 14 + 6) \u003d (640 + 1155 + 476 + 240) / 75 \u003d 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 do I find the average in excel?

how to find arithmetic mean in excel?

Vladimir09854

Easy peasy. It only takes 3 cells to find the average in excel. In the first we will write one number, in the second - another. And in the third cell, we will hammer in a formula that will give us the average value between these two numbers from the first and second cells. If cell number 1 is called A1, cell number 2 is called B1, then in the cell with the formula you need to write as follows:

This formula calculates the arithmetic mean of two numbers.

For the beauty of our calculations, you can select cells with lines, in the form of a plate.

There is also a function for determining the average value in the Excel itself, but I use the old-fashioned method and enter the formula I need. Thus, I am sure that the Excel will calculate exactly as I need it, and will not come up with some kind of rounding.

M3sergey

It's very easy if the data has already been entered into the cells. If you are just interested in a number, it is enough to select the required range / ranges, and the value of the sum of these numbers, their arithmetic mean and their number will appear in the bottom right of the status bar.

You can select an empty cell, click on the triangle (drop-down list) "AutoSum" and select "Average" there, and then agree with the proposed range for calculation, or choose your own.

Finally, you can use formulas directly by clicking "Insert Function" next to the formula bar and cell address. The AVERAGE function is located in the "Statistical" category, and takes as arguments both numbers and cell references, etc. You can also choose more complex options there, for example, AVERAGEIF - calculating 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, heading "Average".

In the case when you want to use the obtained 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 specified sequentially, or a range of numbers.

Vladimir tikhonov

circle the values \u200b\u200bthat will participate 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 bar you will see the average :)

Ekaterina mutalapova

Let's start at the beginning and in order. What does mean mean?

Average is a value that is the arithmetic mean, i.e. is calculated by adding a set of numbers and then dividing the entire sum of the numbers by their number. For example, for numbers 2, 3, 6, 7, 2 there will be 4 (the sum of numbers 20 is divided by their number 5)

In an Excel spreadsheet for me personally, the easiest way was to use the formula \u003d AVERAGE. To calculate the average value, you need to enter data into the table, write the function \u003d AVERAGE () under the data column, and in parentheses indicate the range of numbers in the cells, highlighting the data column. After that, press ENTER, or simply left-click on any cell. The result will be displayed in the cell below the column. It looks incomprehensible, but in fact - a matter of minutes.

Adventurer 2000

Ecxel's program is diverse, so there are several options that will allow you to find the average value:

First option. You simply add up all the cells and divide by their number;

Second option. Use a special command, write in the required cell the formula "\u003d AVERAGE (and then 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 arithmetic prime mean. To do this, you need to drive in 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 weighted arithmetic mean, which is considered more accurate. To calculate it, we need the indicator values \u200b\u200band frequency.

How to find the average in Excel?

The situation is as follows. There is the following table:

The bars shaded in red contain the numerical values \u200b\u200bof the grades for the subjects. In the column "Average score" you want to calculate their average.
The problem is this: there are 60-70 items in total, and some of them are on another sheet.
I looked in another document, the average was already calculated, and in the cell there is a formula like
\u003d "sheet name"! | E12
but it was done by some programmer who was fired.
Please tell me who understands this.

Hector

In the line of functions you insert from the offered functions "AVERAGE" and choose from where they need to be calculated (B6: N6) for Ivanov, for example. I don't know exactly about the neighboring sheets, but for sure it is contained in the standard Windows help

Tell me how to calculate the average value in a Word

Please tell me how to calculate the average value in the Word. Namely, the average of the ratings, not the number of people who received the ratings.


Julia 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 enter 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
[\u003d AVERAGE (ABOVE)]
gives the average of the sum of the above lying cells.
If the field is selected and the right mouse button is pressed, then it can be Refreshed if the numbers have changed,
view the code or value of the field, change the code directly in the field.
If something goes wrong, delete the entire field in the cell and recreate it.
AVERAGE stands for average, ABOVE stands for about, that is, the row of cells above.
I didn’t know all this myself, but I easily found it in HELP, of course, thinking a little.