When working with these types of programs users have the opportunity to have a large number of functions that allow them to process data, information and numbers in a much easier and more practical way, without the need to have very advanced knowledge on the subject.
This is how functions allow you to process a large number of data and get answers quickly. But it is not a secret to anyone that there are many features available in the Microsoft programso it is very difficult to know them all.
In accordance with this and knowing the importance of each one of these elements, here we are going to explain a little more how they work and how you can apply them in your spreadsheet, for this, follow everything that we will teach you in the post in detail.
What is a statistical function in Excel and what is it used for in my documents?
Excel statistical functions allow you to calculate all the mathematical operations that are related to analysis of data stored in a spreadsheet. All this helps to get the data entry number or the average value of the data.
In this way it can be said that the statistics is a mathematical discipline which enables study the different ways of summarizing, collecting and drawing conclusions from a series of data that is studied. This is how this function Microsoft allows to make a statistical analysis of informationwhere it is necessary to have formulas to get the values of the mean, mean variance, standard deviationamong many others.
Some of the functions most used by the users of this program are the “Average, , , mode, frequency” among many others that are available there. In addition, it should be mentioned that these tools are available practically in all Excel versionsI already know so much for the free or paid versions.
List of all the statistical functions of Excel and how to apply them correctly in my documents
As mentioned above, there are a large number of statistics functions available in the programso it is practically impossible to know them all or at least know exactly how each of them works.
According to this, here we are going to explain the most important functions of excel Y How can you apply it correctly in your spreadsheet?
To do this, follow the list in detail that we will show you below:
AvgDev
Returns the average of the absolute deviations from the mean of the data points. Thus, this function takes care of measure the dispersion of values in a specific data set. The formula should be as follows: =AVDEV(number 1: number 2).
In this case number 1 is mandatorywhile the following numbers are optional, they can go between 1 and 255 argumentsthis will depend on the absolute deviations that you want to calculate. It is worth mentioning that the function arguments must be numbers, or in such a case matrix, references or names containing numbers.
In order to apply this formula you must have a numeric data string either in one column or row of your spreadsheetin this case we have in the column A between cells A1 and A8, so that formula is applied as follows: =AVDEV(A2:A8), This will generate the result you see in the image.
Average
In this case with this function returns the average of the argumentsthat is, if the interval is between cell A1:A20, the formula should be =AVERAGE(A1:A20), in this way you will get the average of all the data that is contained between said cells.
AverageA
This function is responsible for compute the average of the arithmetic mean of the values in the list of arguments. So the formula in this case should be as follows: =AVERAGEA(Value1: Value2). The value 1 is required, while subsequent values are optional. This will depend on the range you want to obtain the average.
on this occasion arguments can be numbers, references, arrays or names containing numbers, textual representations of numbers, values such as true or false.
For arguments containing true evaluate to 1, while those who contain false are taken as 0. For the arguments of references or matrices that contain text will be taken as 0, and the empty text will also evaluate to 0.
Those arguments that are error values or text they can’t be translate to text so it will throw an error in the result. And in the case that you do not want to include logical values, textual representations of numbers as part of the calculation, then it is recommended to use the “Average” function.
Average.yes
This function is responsible for return the average of all those in a specific range, lwhich ones should meet certain criteria.
The formula in this case is as follows:
- AVERAGE.IF(range; criteria; average range).
- The range is required. where one or more cells depending on the average that you want to obtain, these can include numbers, names, reference, or arrays containing numbers.
- The criterion is also mandatory, the criteria can be number Shape, cell or text reference, expression, among others. In this way it can be said that the criteria can be expressed as 32, “32”, “>32”, “Pears” or B4.
- Average range is optionalthis is the set royal cells whose average will be calculated, if omitted, range will be used.
Regarding the observations, it should be mentioned that in this case they are not taken into account. range cells containing true or false values. Now if the range is a blank or text value, the function will return the #DIV0! error value.
in each of the criteria can be used question mark and asterisk wildcard characters. It is also not necessary that the average range has the same size and shape of the range.
An example of all this is the following:
count function
With this function you will have the opportunity to count the number of cells containing numbers and count the numbers inside the argument list. In this way, it can be used when you need to get the number of entries in a number field of a range or array of numbers.
Thus, one can write the formula for count the numbers in the range A1:A20: =COUNT(A1:A20). The value 1 is required in the formulafirst element for reference range in which you want count the numbers. While for the value 2 is optional, can contain up to 255 elements, cells, or references in which you want to count numbers.
Among the considerations to be taken into account are count arguments that are numbers, dates, text representations of numbers. are also taken into account logical values and textual representations of numbers written directly into the argument list.
Now if the argument is a reference or an arrayit should only Consider the numbers in this matrix or reference. Which means that empty cells or logical values are not counted in these cases. To count logical values, text or error values you will need to use the function WILL COUNT.
An example of this COUNT function is as follows:
COUNTA function
With the COUNTA tool you will have the opportunity to keep track of the number of cells that are not empty in an interval. In this case the formula is written as follows: =COUNTA(value 1: value 2). The value 1 it is required, since it will be the first argument representing the values you want to count. While value 2 is optionalthese additional arguments represent the values you want to count.
Among the main arguments of this tool is taken into account that the COUNTA function counts cells that have any type of information, including the values of error and empty text. In the event that you don’t want to count the boolean values, text or error values, So it is recommended that you use the COUNT function.
An example of all this is the following:
COUNT YES
The COUNTIF function is one of the functions of excel statistics most used at the time of Count the number of cells that meet a certain criteria. The same can be used for count the number of times a product appears in a certain list.
This function indicates the following: By applying it you will have the opportunity to perform the search and what you want to search for. It can be written as follows: =COUNTIF(A2:A5, “plastic”)or also by selecting the cell where the word you want to search for is located =COUNTIF(A2:A5,A4).
So that all this can be much easier to understand, here we leave you the following example:
DEVIATION2 function
deviate2 is responsible for returning the sum of the squares of the deviations of the data points from the displayed average of the mean.
In this case, the following formula should be used: DEVIATION2(number 1, number 2,…) as in all previous cases number 1 is mandatoryMeanwhile he number 2.. they are optional. Among the arguments added to this function you can find numbers, references, names, or arrays containing numbers. are also taken into account logical values and textual representations of written numbers.
In the case of error values or text cannot be translated since they cause errors in the result.
So that you can understand this a little more, here is an example:
forecast function
In this function you will be able calculate or anticipate a future value using already existing valuesso a future value is a value y for a value X, existing values are known x-values and y-values. So the function forecast will be defined with linear regression.
The application of this formula it is mainly done for prdetermine future sales of a business, inventory requirements, consumer trendsamong other options.
In the case of this function it is necessary to consider that if the value of X is a non-numeric value, will be thrown the #VALUE or #VALUE!. Yes now the known i known x is empty, or one of the two has more points than the other. In this case it returns the #N/A error value. And finally, if the variance of known x is equal to zerothen it returns the #DIV/0! Or #VALUE!.
So that you can understand it better, here is an example:
Frequency
When this formula applies is done with the aim of calculate frequency with which they are produced the values within a specific range of data. In this case it returns a vertical array of numbers.
One of the most common reasons for using this frequency function is to count the number of results that fall within a specified range. In this way, the frequency function is specified as a matrix formula as frequency returns an array.
For…
Facebook Comments
Disqus