Excel provides a variety of statistical functions, but our goal today is to cover the top 5 of them. Knowing these 5 statistical functions will make your Excel work easier and more enjoyable.
Calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
TREND calculates the linear trend line through a given set of Y-values and (optionally) a given set of X-values. FORECAST function would do the same for you.
Correlation measures the degree to which two securities move in relation to each other.
- 1 – perfect positive correlation;
- 0 – no correlations;
- -1 – negative correlation.
The correlation we got in the example (0.239046) means that both of the stocks have a weak correlation with each other.
RSQ (R-squared) is a statistical measurement of the correlation between two sets of values.
RSQ=0.057 in our example means that 5.71% of the variance in Stock A is explained by the variance of Stock B.
Excel Statistical Functions List
A list of Excel Statistical Functions is provided below for your reference:
- Basic statistical functions: AVERAGE, MEDIAN, MODE, GEOMEAN, HARMEAN, AVEDEV, DEVSQ, STDEV, STDEVP, VAR, VARP, KURT, SKEW, LARGE, MAX, MIN, PERCENTRANK, PERCENTILE, QUARTILE, RANK, SMALL, AVERAGEIF, AVERAGEIFS, COUNT, STANDARDIZE, TRIMMEAN.
- Correlation and covariance functions: CORREL, COVAR, PEARSON, RSQ, FISHER, FISHERINV.
- Regression functions: FORECAST, INTERCEPT, SLOPE, TREND, LINEST, STEYX, GROWTH, LOGEST.
- Other statistical functions: CONFIDENCE, FREQUENCY, PROB
There are dozens of statistical functions in Excel, but knowing the 5 we’ve covered, would already help you dramatically to save your precious time in Excel.