Data Analysis with Spreadsheets

4.

Core Statistical and Analytical Functions

4.1.

Basic Statistical Functions

4.1.1.

4.1.1.1.

4.1.1.2.

SUMPRODUCT Function

4.1.1.3.

Summing Non-Contiguous Ranges

4.1.2.

Average Functions

4.1.2.1.

AVERAGE Function

4.1.2.2.

MEDIAN Function

4.1.2.3.

4.1.3.

Count Functions

4.1.3.1.

4.1.3.2.

COUNTA Function

4.1.3.3.

COUNTBLANK Function

4.1.4.

Extreme Value Functions

4.1.4.1.

4.1.4.2.

4.1.4.3.

4.1.4.4.

4.2.

Advanced Statistical Functions

4.2.1.

Distribution Functions

4.2.1.1.

STDEV Functions

4.2.1.2.

4.2.1.3.

4.2.1.4.

4.2.2.

Percentile Functions

4.2.2.1.

PERCENTILE Function

4.2.2.2.

QUARTILE Function

4.2.2.3.

4.2.3.

Correlation and Regression

4.2.3.1.

CORREL Function

4.2.3.2.

4.2.3.3.

INTERCEPT Function

4.2.3.4.

4.3.

Logical Functions

4.3.1.

Basic Logical Functions

4.3.1.1.

IF Function Structure

4.3.1.2.

Logical Operators

4.3.1.3.

Boolean Logic Principles

4.3.2.

Complex Logical Constructs

4.3.2.1.

Nested IF Statements

4.3.2.2.

4.3.2.3.

SWITCH Function

4.3.3.

Logical Operators

4.3.3.1.

4.3.3.2.

4.3.3.3.

4.3.3.4.

4.4.

Conditional Aggregate Functions

4.4.1.

Conditional Sum Functions

4.4.1.1.

4.4.1.2.

SUMIFS Function

4.4.1.3.

Multiple Criteria Logic

4.4.2.

Conditional Count Functions

4.4.2.1.

COUNTIF Function

4.4.2.2.

COUNTIFS Function

4.4.2.3.

Criteria Range Specifications

4.4.3.

Conditional Average Functions

4.4.3.1.

AVERAGEIF Function

4.4.3.2.

AVERAGEIFS Function

4.4.3.3.

Handling Text and Number Criteria

4.5.

Lookup and Reference Functions

4.5.1.

Vertical Lookup Functions

4.5.1.1.

VLOOKUP Function

4.5.1.2.

Exact vs Approximate Match

4.5.1.3.

Table Array Structure

4.5.1.4.

Column Index Numbers

4.5.2.

Horizontal Lookup Functions

4.5.2.1.

HLOOKUP Function

4.5.2.2.

Row-Based Lookups

4.5.3.

Advanced Lookup Functions

4.5.3.1.

4.5.3.2.

4.5.3.3.

INDEX-MATCH Combinations

4.5.3.4.

XLOOKUP Function

4.5.3.5.

FILTER Function

4.5.4.

Reference Functions

4.5.4.1.

INDIRECT Function

4.5.4.2.

OFFSET Function

4.5.4.3.

CHOOSE Function

4.5.4.4.

ADDRESS Function

4.6.

Date and Time Functions

4.6.1.

Current Date and Time

4.6.1.1.

4.6.1.2.

4.6.1.3.

Automatic vs Static Dates

4.6.2.

Date Component Functions

4.6.2.1.

4.6.2.2.

4.6.2.3.

4.6.2.4.

WEEKDAY Function

4.6.2.5.

WEEKNUM Function

4.6.3.

Date Calculation Functions

4.6.3.1.

DATEDIF Function

4.6.3.2.

NETWORKDAYS Function

4.6.3.3.

WORKDAY Function

4.6.3.4.

4.6.3.5.

EOMONTH Function

4.6.4.

4.6.4.1.

4.6.4.2.

MINUTE Function

4.6.4.3.

SECOND Function

4.6.4.4.

4.6.4.5.

TIMEVALUE Function

Previous

3. Data Import and Preparation

Go to top

Next

5. Data Organization and Analysis