Excel Data Analysis For Dummies
Book image
Explore Book Buy On Amazon
When it’s time to get down to analyzing your data, a good place to start is with some basic statistics, such as counting items, calculating sums and averages, finding the largest and smallest values, working out the standard deviation, and so on. These measures fall under the general rubric of descriptive statistics, and Excel offers a fistful of functions that help you get the job done. Here’s a summary:
Function What It Does
COUNT(<em>value1</em>[, <em>value2</em>, ...]) Counts numbers
COUNTA(<em>value1</em>[, <em>value2</em>, ...]) Counts non-empty cells
COUNTBLANK(<em>value1</em>[, <em>value2</em>, ...]) Counts empty cells
COUNTIF(<em>range</em>, <em>criteria</em>) Counts the cells in a range that match the criteria
COUNTIFS(<em>range1</em>, <em>criteria1</em>[,<em> range2, criteria2</em>, ...]) Counts the cells in multiple ranges that match multiple criteria
PERMUT(<em>number</em>, <em>number_chosen</em>) Counts the permutations
COMBIN(<em>number</em>, <em>number_chosen</em>) Counts the combinations
SUM(<em>number1</em>[, <em>number2</em>, ...]) Calculates the sum
SUMIF(<em>range</em>, <em>criteria</em>[, <em>average_range</em>]) Calculates the sum of the cells that match the criteria
SUMIFS(<em>average_range</em>, <em>range1</em>, <em>criteria1</em>[, <em>range2</em>, <em>criteria2</em>...]) Calculates the sum of cells in multiple ranges that match multiple criteria
AVERAGE(<em>number1</em>[, <em>number2</em>, ...]) Calculates the arithmetic mean
AVERAGEIF(<em>range</em>, <em>criteria</em>[, <em>average_range</em>]) Calculates the mean of the cells that match the criteria
AVERAGEIFS(<em>average_range</em>, <em>range1</em>, <em>criteria1</em>[, <em>range2</em>, <em>criteria2</em>...]) Calculates the mean of cells in multiple ranges that match multiple criteria
MEDIAN(<em>number1</em>[, <em>number2</em>, ...]) Calculates the median (middle) value
MODE(<em>number1</em>[, <em>number2</em>, ...]) Calculates the mode (most common) value
RANK.EQ(<em>number</em>, <em>ref</em>[, <em>order</em>]) Returns an item’s rank relative to the other items in a data set
LARGE(<em>array</em>, <em>k</em>) Returns the kth largest item in a data set
SMALL(<em>array</em>, <em>k</em>) Returns the kth smallest item in a data set
FREQUENCY(<em>data_array</em>, <em>bins_array</em>) Creates a grouped frequency distribution
VAR.S(<em>number1</em>[, <em>number2</em>, ...]) Calculates the variance of a sample
VAR.P(<em>number1</em>[, <em>number2</em>, ...]) Calculates the variance of a population
STDEV.S(<em>number1</em>[, <em>number2</em>, ...]) Calculates the standard deviation of a sample
STDEV.P(<em>number1</em>[, <em>number2</em>, ...]) Calculates the standard deviation of a population
CORREL(<em>array1</em>, <em>array2</em>) Calculates the correlation between two data sets

About This Article

This article is from the book:

About the book author:

Paul McFedries is a technical writer who has been authoring computer books since 1991 and has over 100 books to his credit. These books include Alexa For Dummies, Amazon Fire TV For Dummies, and Cord Cutting For Dummies. You can visit Paul on the web at www.mcfedries.com.

This article can be found in the category: