Category functions Statistical designed primarily for analyzing cell ranges in Excel. Using these functions, you can calculate the largest, smallest, or average value, count the number of cells containing given information, and so on.
This category contains more than 100 different Excel functions, most of which are intended solely for statistical calculations and will seem like a dark forest to the average ordinary user. In this lesson, we will consider the most useful and common functions of this category.
Within the framework of this article, we will not touch on such popular Excel statistical functions as CHECK и COUNTIF, a separate lesson has been prepared for them.
CORE()
statistical function AVERAGE returns the arithmetic mean of its arguments.
This function can take up to 255 arguments and find the average in several non-adjacent ranges and cells at once:
If there are empty cells or cells containing text in the calculated range, they are ignored. In the example below, the average is searched for four cells, i.e. (4+15+11+22)/4 = 13
If you need to calculate the average, taking into account all the cells in the range, you can use the statistical function HEART. In the following example, the average is already searched for 6 cells, i.e. (4+15+11+22)/6 = 8,6(6).
statistical function AVERAGE can use mathematical operators and various Excel functions as its arguments:
CERTAIN()
If you need to return the arithmetic mean of values that satisfy a certain condition, you can use the statistical function HEARTLESS. The following formula calculates the average of numbers that are greater than zero:
In this example, the same range is used to calculate the average and test the condition, which is not always convenient. In this case, the function HEARTLESS there is a third optional argument over which to average. Those. on the first argument we check the condition, on the third – we find the average.
Let’s say the table below contains statistics on the cost of medicines in the city. In one pharmacy, the medicine is more expensive, in the other cheaper. To calculate the cost of analgin on average in the city, we use the following formula:
If you need to meet several conditions, then you can always apply the statistical function SRZNAČESLIMN, which allows you to calculate the arithmetic mean of cells that meet two or more criteria.
MAX()
statistical function MAX returns the largest value in a range of cells:
i()
statistical function MIN returns the smallest value in a range of cells:
GREAT()
Returns the nth largest value from an array of numeric data. For example, in the figure below, we found the fifth largest value from the list.
To see this, you can sort the numbers in ascending order:
LEAST()
Returns the nth smallest value from an array of numeric data. For example, in the figure below, we found the fourth smallest value from the list.
If you sort the numbers in ascending order, then everything becomes much clearer:
MEDIAN()
statistical function MEDIAN returns the median from the given array of numeric data. The median is the number that is the middle of a set of numbers. If the list has an odd number of values, then the function returns what is exactly in the middle. If the number of values is even, then the function returns the average of the two numbers.
For example, in the figure below, the formula returns the median for a list of 14 numbers.
If you sort the values in ascending order, then everything becomes much clearer:
FASHION()
Returns the most frequently occurring value in an array of numeric data.
If you sort the numbers in ascending order, then everything becomes much clearer:
statistical function FASHION at the moment, it is outdated, more precisely, its form of recording is outdated. Instead, the function is now used FASHION.ONE. Form records FASHION also supported in Excel for compatibility.
As you know, category Statistical in Excel contains more than 100 of the most diverse functions. But, as practice shows, the lion’s share of these functions is practically not used, and especially by novice users. In this lesson, we tried to introduce you only to the most popular Excel statistical functions that you will sooner or later be able to put into practice. I hope that this lesson was useful for you. Good luck and success in learning Excel.