Skip to main content
O'Reilly home
    • Sign In
    • Try Now
    • Teams
      • For business
      • For government
      • For higher ed
    • Individuals
    • Features
      • All features
      • Certifications
      • Interactive learning
      • Live events
      • Answers
      • Insights reporting
    • Blog
    • Content sponsorship

Predictive Analytics: Microsoft® Excel by

Get full access to Predictive Analytics: Microsoft® Excel and 60K+ other titles, with free 10-day trial of O'Reilly.

There's also live online events, interactive content, certification prep materials, and more.

Start your free trial

Table of Contents

Introduction

1 Building a Collector

Planning an Approach

A Meaningful Variable

Identifying Sales

Planning the Workbook Structure

Query Sheets

Summary Sheets

Snapshot Formulas

More Complicated Breakdowns

The VBA Code

The DoItAgain Subroutine

The GetNewData Subroutine

The GetRank Function

The GetUnitsLeft Function

The RefreshSheets Subroutine

The Analysis Sheets

Defining a Dynamic Range Name

Using the Dynamic Range Name

2 Linear Regression

Correlation and Regression

Charting the Relationship

Calculating Pearson’s Correlation Coefficient

Correlation Is Not Causation

Simple Regression

Array-Entering Formulas

Array-Entering LINEST()

Multiple Regression

Creating the Composite Variable

Analyzing the Composite Variable

Assumptions Made ...

Get Predictive Analytics: Microsoft® Excel now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.

Start your free trial

About O’Reilly

  • Teach/write/train
  • Careers
  • Community partners
  • Affiliate program
  • Submit an RFP
  • Diversity
  • O’Reilly for marketers

Support

  • Contact us
  • Newsletters
  • Privacy policy
facebook-logo linkedin-logo youtube-logo

International

  • Australia & New Zealand
  • Hong Kong & Taiwan
  • India
  • Indonesia
  • Japan

Download the O’Reilly App

Take O’Reilly with you and learn anywhere, anytime on your phone and tablet.

Apple app store Google play store

Watch on your big screen

View all O’Reilly videos, Superstream events, and Meet the Expert sessions on your home TV.

Roku Payers and TVs Amazon appstore

Do not sell my personal information

O'Reilly home

© 2023, O’Reilly Media, Inc. All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.

Terms of service • Privacy policy • Editorial independence

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Get it now
Cover of Software Architecture Patterns

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

Start your free trial Become a member now