Excel Advanced Statistical Analysis

Description

Use some of the hidden statistical analysis tools within Excel to build complex data models.

77df68e-28e4-057f-41d6-ca1d3577146_Course_Overview_Icon.png

Course overview

Duration: 1 day (6.5 hours)

This advanced Excel course looks at some of the statistical analysis tools available and gives examples of when they might be used.

This course is aimed at advanced users of Excel who work with statistical data often and have a good knowledge of Maths.

8ba0826-4fb5-333c-0ea4-28c13064336b_Objectives_Icon.png

Objectives

By the end of the course you will be able to:

Use Goal Seek

Create and view scenarios

Use Forecast Sheet

Create single and double input data tables

Create models using the solver

Install and use the Analysis Toolpak

Create and use array formulas

Use a range of advanced Financial and Statistical Excel functions

Content

Using what if analysis options

Using Goal Seek

Creating, saving and viewing scenarios

Generating a Forecast sheet

Solver

Creating models

Projecting scenarios with Solver

Using data tables

Creating single input data tables

Creating double input data tables

Projecting with data tables

Financial functions

PMT

FV

NPV

Analysing data with Analysis ToolPak

Installing the Analysis Toolpak

Generating statistical analysis

Visualising data using Histograms

Array formulas

Using embedded Excel Array formulas

Create an Array formula

Multi and single cell Array formula

Using TRANSPOSE to flip rows or columns

Use the FREQUENCY Function

Use an array to count unique entries in a range

Dynamic arrays

Spilling data

Using the new dynamic array functions

Advance functions in formulas

Statistical functions:MEAN, MEDIAN and MODERANKLARGE and SMALLMODPERCENTILE

Use the AGGREGATE function to sum data in ranges with errors