Understanding Statistical Functions in Google Sheets

September 11th, 2023

Data analysis is an invaluable component of many professions. Google Sheets equips users with a variety of statistical functions ranging from basic variance calculations to advanced distributions and tests. This guide not only introduces these functions but also delves into when and why you might use them.

Basic Statistical Measures

VARA and VAR.P

Calculate the variance of a dataset. While VARA considers both numbers and text, VAR.P focuses solely on numbers for the entire population.

When to use: To assess the spread or variability in a dataset, especially when determining risk or volatility in financial scenarios.

=VARA(B2:B10)
=VAR.P(C2:C10)

STDEV.P and STDEVPA

Determine the amount of variation or dispersion in your data set.

When to use: When assessing the consistency and reliability of data sets, such as quality control in manufacturing.

=STDEV.P(D2:D10)
=STDEVPA(E2:E10)

Distributions

GAMMAINV and GAMMA.INV

Inverse functions of the gamma distribution.

When to use: When modeling events that happen at particular intervals, like the number of calls received at a call center in an hour.

=GAMMAINV(0.5,2,3)
=GAMMA.INV(0.7,5,1)

NORM.INV and NORMINV

Determine the inverse of the normal cumulative distribution for specific mean and standard deviation.

When to use: In stock market analyses or when predicting future values based on historical data.

=NORM.INV(0.9,0,1)
=NORMINV(0.95,3,2)

NORM.S.DIST and WEIBULL.DIST

When to use: NORM.S.DIST for understanding probabilities under the standard bell curve. WEIBULL.DIST in reliability engineering to measure the likelihood of failure over time.

=NORM.S.DIST(1.5,TRUE)
=WEIBULL.DIST(2.5,6,7,TRUE)

T.INV and FDIST

When to use: In hypothesis testing. T.INV for smaller sample sizes and FDIST to compare variances of two datasets.

=T.INV(0.1,7)
=FDIST(0.8,6,7)

HYPGEOMDIST and NEGBINOM.DIST

When to use: In market research to understand how likely a specific outcome is, given a particular sample size.

=HYPGEOMDIST(4,10,5,20)
=NEGBINOM.DIST(4,6,0.7,TRUE)

Advanced Functions and Tests

KURT

Measures data "tailedness".

When to use: To identify the presence of outliers or extreme values in a dataset.

=KURT(A2:A20)

ZTEST and T.TEST

When to use: To determine if differences between groups are statistically significant, like in A/B testing.

=ZTEST(B2:B30, 50)
=T.TEST(C2:C30, D2:D30,2,1)

ERFC.PRECISE and GAMMALN.PRECISE

When to use: In advanced statistical analyses where high precision is necessary, such as in scientific research.

=ERFC.PRECISE(0.7)
=GAMMALN.PRECISE(5)

Conclusion

With a solid understanding of Google Sheets' statistical functions and insights into their applications, you can elevate your data analysis skills. As you explore these functions and their use-cases, you'll uncover the potential of Google Sheets as a powerful statistical tool.