Marketing Analytics

Basic Formulas Part One Sample Lesson

Basic Formulas – Index, Match, and Lookups 43 Topics

Expand

Lesson Content

0% Complete 0/43 Steps

Basic Formulas Part One

Basic Formulas Part Two

Basic Formulas Part Three

Computing a Production Function

Modeling Supply and Demand

Cost Curves

Retirement

Modeling Fish in a Pond

Amortization Schedule

Product Prices Based On Date

Vertical Lookup From Multiple Worksheets

Vertical Lookup With Multiple Columns

Index Function

Index With Rows and Columns

Match Function

Combining Match and Index

Match, Index, and Large Functions

Finding Most Recent Date of Sale

Cost of Living

Currency Exchange

Predicting the Future of the United States Population

The CHOOSE Function

VLOOKUPS and Income Taxes

Math is Fun

Rule of 72

Bringing PDF Tables Into Excel

Multiplying 4 Consecutive Integers

Explaining Google’s Page Rank

The XLOOKUP Function

XLOOKUP and the TRUE Argument

XLOOKUP and the INDEX MATCH Combination

Using XLOOKUP to Reference a Cell Range

Filtering, Sorting, and Removing Duplicates 16 Topics

Expand

Lesson Content

0% Complete 0/16 Steps

Filtering a Column on Two Criteria

Basic Sorting

Sorting on Colors

Sorting A to Z

Case Sensitive Sorting

Sorting With a Custom List

Creating a Custom List

Sorting Across

Removing Duplicates

Filtering by Selection

Range Names 9 Topics

Expand

Lesson Content

0% Complete 0/9 Steps

Name Box

Create From Selection

F3 Trick

Name Entire Row or Column

Apply Names

Worksheet and Workbook Names

Range Name Tricks

Implicit Intersection

Lookup With Range Names

Text Functions 15 Topics

Expand

Lesson Content

0% Complete 0/15 Steps

Left, Right, Mid, Len and Concatenate Functions

Find Function and Text to Columns

Histograms Copy

Clean Function

Flash Fill

Control + E and Flash Fill

Find Last Word

Two Column Lookup

Social Security Numbers

Unicode

Creating an Email List With Text Functions

The TEXT Function

The ROUND, ROUNDUP, ROUNDDOWN, and INT Functions

Special Text Formats

Using Flash Fill to Clean Up Messy Data

Auditing Tools, Circular References, and IF Functions 24 Topics

Expand

Lesson Content

0% Complete 0/24 Steps

Valuing an Acquisition

Combining Addresses

If Statements and Sales Commissions Calculations

If Statements and Sales Commissions Calculations Part Two

IF Statements and Option Investing

IF Statements Continued

Basic Shift Code

Rail Fence Code

Longest Winning Streak

The Ducci Sequence

The Stern Sequence

Waist to Height Ratio and Longevity

Combining the IF and IFERROR Functions

Data Tables, Goal Seek, and Scenario Manager 18 Topics

Expand

Lesson Content

0% Complete 0/18 Steps

Finding Time to Breakeven

Scenario Manager

Down Payment

Project Management Copy

Hedging

Bill Belichick’s Bold Move

IF Statements and Data Tables

Data Table Chart

Hedge Funds

The Syracuse Problem

Relating Bond Price and Yield

Creating a Data Table Based on a Model in a Different Worksheet

Conditional Counts, Sums, Averages, and Subtotals 24 Topics

Expand

Lesson Content

0% Complete 0/24 Steps

COUNTIF and COUNTIFS Functions

COUNT, COUNTA, and COUNTBLANK Functions

SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS

SUMIFS With Duplicates

Subtotals

The SUBTOTAL Function

Using the SUBTOTAL Function to Count Cells With Colors

Sorting Subtotals

Data Consolidate

Summing Data in Different Worksheets

Showing Data in Different Worksheets Side by Side

Conditional Counts and Sums With Non-Blanks

Benford’s Law

Finding the N’th Match

Analyzing NFL Play Effectiveness

Using the SUMPRODUCT Function for Conditional Sums and Counts

How Many Winning Seasons?

Using OR Criteria with Conditional Counts, Sums, and Averages

Computing Sales

Ranked Choice Voting

Conditional Formatting and Data Validation 14 Topics

Expand

Lesson Content

0% Complete 0/14 Steps

Top and Bottom Rules

Highlight Cells Rules

Data Bars, Color Scales, and Icon Sets

Formula Option Part One

Formula Option Part Two

Stop if True

Data Validation

Custom Option

Using Data Validation to Prevent Entry of Duplicates

Circle Invalid Data

Highlighting New Hires

Conditional Formatting and Sports Part One

Conditional Formatting and Sports Part Two

Conditional Formatting and Fraud Detection

Tables and 3D Formulas 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Worksheet Templates

Three Dimensional Formula

Three Dimensional Formulas With Wildcards

Aggregate Function with Slicers

Conditional Formatting Tables

Copying Formulas Across a Table

The Vignere Code

Filtering Charts With Table Slicers

Pivot Tables 31 Topics

Expand

Lesson Content

0% Complete 0/31 Steps

Introduction to Pivot Tables

Analyzing Pivot Table Data

Text Filters

Grouping and Pivot Charts

Value Field Settings and Show Values As

Calculated Fields

Slicers and Filters

GETPIVOTDATA Function

Calculated Items

Using Solve Order to Change Order Calculations

Timelines

Analyzing Surveys and More Pivot Charts

Creating Pivot Tables From Multiple Ranges

Multiple Pivot Tables

Data Model

Pivot Tables and the NBA Part One

Pivot Tables and the NBA Part Two

Birth Rate

Republican Congress

Pivot Tables and Dating

Simpsons Paradox

Putting it all Together

Analyzing Election Data

Editing the Pivot Table Default Settings

Using Multiple Slicers

Conditional Formatting and PivotTables

Pivot Tables and Unrecognized Dates

Summarizing Bitcoin Data

More on Calculated Fields

Using Custom Groups to Compute Net Promoter Score

Formatting a Single Pivot Table Cell and Moving a Pivot Table

Descriptive Statistics 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Histogram Creation

Histogram Shapes

Descriptive Statistics

Rule of Thumb

More Statistical Functions

Taking a Random Sample

Congressional Apportionment

Matchmaker

Multiple Objective Decision Making

Analyzing Changes in US Family Income

Descriptive Statistics for Grouped Data

Chinese Accidental Deaths and Histograms

Describing Baseball Salaries

Trend Curve, Correlation, and Regression to the Mean 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Linear Regression Part One

Linear Regression Part Two

Exponential Growth

Power and Demand Curves

Polynomial Demand Curves

Learning Curves

Correlation

Estimating the Beta of a Stock

Regression to the Mean

Moving Average

Good R Squared

Finding the Best Curve to Fit Data

Converting ACT scores to SAT scores

Multiple Regression 19 Topics

Expand

Lesson Content

0% Complete 0/19 Steps

Multiple Regression Part One

Multiple Regression Part Two

Dummy Variables

Introduction to Conjoint Analysis

Conjoint Analysis and Regression

Value Based Pricing

Nonlinearities and Interactions

What Makes NBA Teams Win

Quarterback Rating

Maximum Likelihood Estimation

Logistic Regression

Logistic Regression for Grouped Data

The Windchill Index

Predicting Wine Prices

One Way ANOVA

Randomized Blocks

Two Way ANOVA Without Interaction

Two Way ANOVA With Interaction

ANOVA With More Than Two Factors

Offset and Indirect Functions 21 Topics

Expand

Lesson Content

0% Complete 0/21 Steps

Graphing the Last 6 Months of Sales

Indirect Function

Indirect Consolidate

Using The INDIRECT Function With Spaces

Combining the INDIRECT and ADDRESS Functions

Indirect Range

Worksheet Names

Nested Drop Down

The INDIRECT Function and Stock Return Data

Income Distribution Part One

Income Distribution Part Two

One Man One Vote?

Average Points Scored

Technical Analysis

Using the INDEX Function to Return a Cell Address

Optimization and the Excel Solver 4 Topics

Expand

Lesson Content

0% Complete 0/4 Steps

Introduction to Solver Part One

Introduction to Solver Part Two

Pricing Model

Demand Sensitivity to Price Change

Negotiation 9 Topics

Expand

Lesson Content

0% Complete 0/9 Steps

Two Person Negotiation Part One

Two Person Negotiation Part Two

Why Maximize POP (Proportion of Potential)

Store Contractor Negotiation Part One

Store Contractor Negotiation Part Two

Store Contractor Negotiation Part Three

Three Player Negotiation

Six Player Negotiation

How to Determine Reservation Levels

Array Formulas 17 Topics

Expand

Lesson Content

0% Complete 0/17 Steps

Introduction to Array Formulas and Functions

Simple Examples of Array Formulas

Array Formulas and Text Functions

Finding Smallest Numbers in a Range

Emulating SUMIF or DSUM

Emulating SUMIFS

Emulating the OR Operator With Arrays

Creating the MEDIANIF Function

Creating the STDEVIF Function

Conditional Products

Finding Unique Entries in an Array

Transpose Function

Frequency Function

Matrix Array Functions

Trend Function

Linest Function

Fermat’s Little Theorem

Forecasting 17 Topics

Expand

Lesson Content

0% Complete 0/17 Steps

Trend and Seasonality

Ratio to Moving Average Part One

Ratio to Moving Average Part Two

Winters Method of Forecasting Introduction

Winters Method – Equations

Winters Method – Initialization

Winters Method – Implementation

Credit Union Forecasting Part One

Credit Union Forecasting Part Two

Predicting Software Sales

Using The Brier Score To Evaluate Forecasters

Using the Excel 2016 Forecast Sheet Capability

Using the Excel 2016 Forecast Functions

Forecasting Candy Bar Sales

Downloading Trend Data from Google Trends and Forecasting Future Searches

Evaluating Forecast Bias

Forecast Sheet Statistics

Charting 32 Topics

Expand

Lesson Content

0% Complete 0/32 Steps

Combo and Secondary Axis Charts

Handling Missing Data

Handling Hidden Data

Adding Pictures to Column Graphs

Check Boxes and Chart Dashboards

List Boxes and Chart Dashboards

Spinners

Gantt Charts

Dynamic Histograms

Charts Based on Sorted Data

Adding Conditional Colors to a Chart

Waterfall Charts

Creating Charts With GETPIVOTDATA

Adding a Picture to a Column Graph

Stacking Pictures in a Column Graph

Creating a Chart With a Secondary X and Y Axis

Pie Charts With a Bar Chart Breakout

Deleting Points From a Chart

Highlighting Best and Worst Months of Sales

Advanced Solver 5 Topics

Expand

Lesson Content

0% Complete 0/5 Steps

Discriminant Analysis

Cluster Analysis

Estimating Elasticities

Introduction to Neural Networks

Fitting a Neural Network

Advanced Charts 11 Topics

Expand

Lesson Content

0% Complete 0/11 Steps

Histograms

Pareto Charts

Boxplots One Variable

Boxplots More Than One Variable

Waterfall Chart – Time Series Data

Waterfall Chart – Revenue to Profit

Treemap Chart – Bookstore

Sunburst Chart – Bookstore

Sunburst Chart – Monthly Breakdown

Treemap Chart – Restaurant

Funnel Chart

Office 365 Functions 4 Topics

Expand

Lesson Content

0% Complete 0/4 Steps

The TEXTJOIN Function

The MAXIFS and MINIFS Functions

The IFS Function

The SWITCH Function

Dynamic Arrays 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

The Unique Function

The UNIQUE Function and Excel Tables

The SORT and SORTBY Functions

The SORT and SORTBY Functions With Ranges and Tables

The FILTER Function

The SEQUENCE Function

The RANDARRAY Function

Referring to Dynamic Arrays in Other Formulas

Using Dynamic Arrays to Create Dynamic Dropdown Boxes

Using Dynamic Arrays to Emulate a Pivot Table

Power Pivot 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Introduction to PowerPivot

Creating PivotTables from a Database

DAX Functions

The DISTINCTCOUNT Function

Comparing Forecasted Sales to Actual Sales

The RELATED DAX Function

Pivot Tables Based on Power Pivot Calculated Columns

Calculated Measures

KPI (Key Performance Indicators)

Using Power Pivot to Draw a Random Sample

Using Power Pivot to Sample From a Subset of a Database

Median and Date Intelligence DAX functions

Intelligence Functions Part Two

Filled and 3D Maps 6 Topics

Expand

Lesson Content

0% Complete 0/6 Steps

World Filled Maps

US Filled Maps

Building a 3D Energy Map

3D Time Animation

Mapping the 2016 US Election

Mapping NYC Subway Lines

Data Types 5 Topics

Expand

Lesson Content

0% Complete 0/5 Steps

Introduction to Geography Data Types

Geography Types and Excel Tables

The FIELDVALUE Function

Resolving Ambiguity

Stock Data Types

Power Query 13 Topics

Expand

Lesson Content

0% Complete 0/13 Steps

Downloading Bitcoin Prices and Volume

Downloading the Population of US Cities

Flattening a Table

Turning a Flat File Into a Table

Using a Formula to Add a Column

How to Append Queries

Combining All Workbooks in a Folder

Filling Blanks in a Column

Transforming Data Using Delimiters

Grouping Data with Power Query

Using Power Query to Summarize Data Across Multiple Columns

Creating Custom Columns

Pivoting Columns with Power Query

Macros 10 Topics

Expand

Lesson Content

0% Complete 0/10 Steps

Inserting the Developer Tab

Inserting a List of Names

Relative Reference With Macros

Using Macros to Format Cells

Formatting a Range of Variable Size

Using a Macro to Freeze Values

Using Macros For Reporting

Creating a Border With a Macro

Personal Macro

Sorting Macro

Course Content

About Instructor

Wayne Winston

Course Includes

CONTACT INFO