Among the many resources traditionally known in Excel, such as charts, formulas and reports, there is still the possibility of working with statistics.
On the other hand, when you want to work with this science of probabilities in Excel, it is necessary to know the main functions that perform the calculations commonly used within this discipline. In this way, we separate the main statistical functions in Excel. Follow us!
This function basically counts the number of numbers in a selected set of cells.
So we want to know the number of numbers in the listing. In this way, simply enter the CONT.NUM function and select the desired cell range. At the end of the function just enter enter and check the result.
If we want to count all the data in the spreadsheet except the empty fields, we can use the COUNT function that counts all the cells within a stipulated range. Observe the example below:
NOTE: If you only need to count blank cells, simply use the COUNTER function with the same principle as COUNTER COUNTER and COUNTER COUNTER.
Maximum and minimum
These two functions as the names themselves refer to, bring the maximum and minimum values into a range of data. In this case, if we want to know for example the maximum value in a range of cells, just apply the function as shown below and check the result. The same procedure is used for the MINIMUM function.
This function counts elements according to a pre-set criteria.
Syntax: CONT.SE (cells, criterion)
Cells: represents the range of cells selected
Criterion: represents the criterion we wish to establish for counting.
In the example below we have a random numerical sequence. But we want to get the amount of values that are below 10. So, just use the function as shown below:
LARGER and LESS
These two functions are similar to the MAXIMUM AND MINIMUM functions. However in this case it is possible to choose the order of higher or lower value that is desired (example: second highest value, third lowest value and so on).
Syntax: Greater or Less (matrix; k).
Matrix: is a desired cell range.
K: is the position of the value that we wish to find.
In the example below we want to get the second lowest number from the list. In this way, we will apply the MINOR function as shown below:
This function takes the average of a range of data. That is, the sum between them is realized and later the total of the sum is divided by the quantity of summed values.
Below is a simple example where this function is used.
We know that in statistics the term Median represents the central value in a sample of data. Therefore, to calculate it, just use the MED function as shown below:
Note: To calculate Median, the sample values must be arranged in ascending order. In this case, we use the filter feature to sort the sample.
Fashion refers to the value that is repeated most frequently within a sample. In this way, the MODE function is used to find the fashion of a sequence. (For versions of Excel prior to 2007, the MODE function is used).
An example below illustrates the use of the MODE function.
The standard deviation is intended to demonstrate the degree of oscillation of a sample against its own mean. The calculation to find the standard deviation of a sample involves several steps, however in Excel it can be simplified only with the STDEV function (STDEV.A for versions from Excel 2010).
Below is an example of applying the standard deviation function in Excel.
In addition to the functions mentioned above, Excel also provides other statistics functions for data manipulation and analysis. Some of them:
CORREL: Shows the correlation coefficient between two data
COVARIAÇÃO.P: It presents the covariation of a population, the average of the deviations each data point in two dataset.
GROWTH: Shows the exponential growth trend corresponding to known data points.
CURT: Displays the kurtosis of a sample of data.
AVERAGE DEVICE: Displays the mean deviation of a sample;
Based on the statistical tips in Excel, it was possible to know the main functions regarding the manipulation and analysis of data through statistics.
In addition, it is notable that using Excel to work with statistics, this activity becomes easily performed by comparing it to the manual calculations of each of the formulas. At any rate, to delve into statistical applications with Excel, it is recommended to initially know each subject theoretically and then apply it in Excel.
Do you have questions or comments about statistics in Excel? Share with us in the comments!