Statistical functions are functions that perform mathematical and statistical operations and are some of the most common functions used in spreadsheets.
Statistical functions are functions that perform mathematical and statistical operations and are some of the most common functions used in spreadsheets. Statistical functions range from the simple, like identifying maximum (MAX) and minimum (MIN) values in datasets, to the complex, like calculating cumulative beta probability density functions (BETADIST).
How to Use Statistical Functions
In the examples below, we’ll take a look at some commonly used statistical functions. For documentation on every statistical function supported by Spreadsheet.com, reference our section on Statistical functions.
When using functions in your workbook, you can input and edit formulas either directly in the cell, or from the formula bar between the worksheet toolbar and the spreadsheet grid. Learn more in our article on Creating and Editing Formulas.
How to Calculate the Average Value of a Dataset (AVERAGE and AVERAGEIF)
The AVERAGE function takes a series of numbers and returns the average value of the series.
In the example above, we’ve used the AVERAGE function in Cell C6 to calculate the average of our series of values in Column A.
The AVERAGEIF function works similar to the AVERAGE function in that it calculates the average of a range of numbers, but instead returns only the average of numbers within a range that meet a given criteria.
In Cells F2 and F3, the AVERAGEIF functions are matching the period listed in the adjacent cell with our original table of values on the left and taking the average of all H1 and H2 sales, respectively.
In Cells F5, F6, and F7, the AVERAGEIF functions are performing a similar operation, this time matching the adjacent region labels with the values in our original table of values on the left and calculating the average of all Region 1, Region 2, and Region 3 sales, respectively.
AVERAGEIF supports only one condition. To find the average of values that meet multiple criteria, use the AVERAGEIFS function.
How to Identify the Maximum (MAX) and Minimum (MIN) Values in a Dataset
The MAX function takes a series of numbers and returns the maximum value of the series. Similarly, the MIN function takes a series of numbers and returns the minimum value of the series.
In the example above, we’ve used the MAX function in Cell C3 to calculate the maximum value of Column A, and the MIN function in Cell C6 to calculate the minimum value of Column A.
Like AVERAGE and AVERAGEIFS, both the MAX and MIN functions have their own “ifs” counterparts. MAXIFS and MINIFS returns the maximum and minimum values of a dataset depending on one or multiple criteria, respectively.
More Statistical Functions
The functions shown above are just some of the many statistical functions supported by Spreadsheet.com. Take a look at our full suite of articles on statistical functions to learn more.
Learn about visualizing the numbers behind your workbooks with Spreadsheet.com’s built-in charts. Or, read on to learn more about other types of functions supported by Spreadsheet.com, including financial functions, logical functions, math functions, and more