Home > Store

Decision Analytics: Microsoft Excel

Register your product to gain access to bonus material or receive a coupon.

Decision Analytics: Microsoft Excel

eBook (Watermarked)

  • Your Price: $25.59
  • List Price: $31.99
  • Includes EPUB and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® Reader® software.

    This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.


  • Copyright 2014
  • Dimensions: 7" x 9-1/8"
  • Edition: 1st
  • eBook (Watermarked)
  • ISBN-10: 0-13-349059-9
  • ISBN-13: 978-0-13-349059-6

Crunch Big Data to optimize marketing and more!

Overwhelmed by all the Big Data now available to you? Not sure what questions to ask or how to ask them? Using Microsoft Excel and proven decision analytics techniques, you can distill all that data into manageable sets—and use them to optimize a wide variety of business and investment decisions. In Decision Analytics: Microsoft Excel, best selling statistics expert and consultant Conrad Carlberg will show you how—hands-on and step-by-step.

Carlberg guides you through using decision analytics to segment customers (or anything else) into sensible and actionable groups and clusters. Next, you’ll learn practical ways to optimize a wide spectrum of decisions in business and beyond—from pricing to cross-selling, hiring to investments—even facial recognition software uses the techniques discussed in this book!

Through realistic examples, Carlberg helps you understand the techniques and assumptions that underlie decision analytics and use simple Excel charts to intuitively grasp the results. With this foundation in place, you can perform your own analyses in Excel and work with results produced by advanced stats packages such as SAS and SPSS.

This book comes with an extensive collection of downloadable Excel workbooks you can easily adapt to your own unique requirements, plus VBA code to streamline several of its most complex techniques.

  • Classify data according to existing categories or naturally occurring clusters of predictor variables
  • Cut massive numbers of variables and records down to size, so you can get the answers you really need
  • Utilize cluster analysis to find patterns of similarity for market research and many other applications
  • Learn how multiple discriminant analysis helps you classify cases
  • Use MANOVA to decide whether groups differ on multivariate centroids
  • Use principal components to explore data, find patterns, and identify latent factors

Register your book for access to all sample workbooks, updates, and corrections as they become available at quepublishing.com/title/9780789751683.

Sample Content

Table of Contents

Introduction     1

What’s in the Book     1

Why Use Excel?     3

1  Components of Decision Analytics     5

Classifying According to Existing Categories     5

  Using a Two-Step Approach     6

  Multiple Regression and Decision Analytics     6

  Access to a Reference Sample     8

  Multivariate Analysis of Variance     9

  Discriminant Function Analysis     10

  Logistic Regression     12

Classifying According to Naturally Occurring Clusters      13

  Principal Components Analysis     13

  Cluster Analysis     14

Some Terminology Problems     16

  The Design Sets the Terms     17

  Causation Versus Prediction     18

  Why the Terms Matter     18

2  Logistic Regression     21

The Rationale for Logistic Regression     22

  The Scaling Problem     24

  About Underlying Assumptions     25

  Equal Spread     25

  Equal Variances with Dichotomies     27

  Equal Spread and the Range     28

The Distribution of the Residuals     29

  Calculating the Residuals     30

  The Residuals of a Dichotomy     30

Using Logistic Regression     31

  Using Odds Rather Than Probabilities     32

  Using Log Odds     33

  Using Maximum Likelihood Instead of Least Squares     34

Maximizing the Log Likelihood     35

  Setting Up the Data     35

  Setting Up the Logistic Regression Equation     36

  Getting the Odds     38

  Getting the Probabilities     39

  Calculating the Log Likelihood     40

  Finding and Installing Solver     41

  Running Solver     41

The Rationale for Log Likelihood     43

  The Probability of a Correct Classification     44

  Using the Log Likelihood     45

The Statistical Significance of the Log Likelihood     48

  Setting Up the Reduced Model     50

  Setting Up the Full Model     51

3  Univariate Analysis of Variance (ANOVA)     53

The Logic of ANOVA     54

  Using Variance     54

  Partitioning Variance     55

  Expected Values of Variances (Within Groups)     56

  Expected Values of Variances (Between Groups)     58

  The F-Ratio     61

  The Noncentral F Distribution     64

Single Factor ANOVA     66

  Adopting an Error Rate     66

  Computing the Statistics     67

  Deriving the Standard Error of the Mean     70

Using the Data Analysis Add-In     72

  Installing the Data Analysis Add-In     73

  Using the ANOVA: Single Factor Tool     73

Understanding the ANOVA Output     75

  Using the Descriptive Statistics      75

  Using the Inferential Statistics     76

The Regression Approach     79

  Using Effect Coding     80

  The LINEST() Formula     82

  The LINEST() Results     83

  LINEST() Inferential Statistics     85

4  Multivariate Analysis of Variance (MANOVA)     89

The Rationale for MANOVA     89

  Correlated Variables     90

  Correlated Variables in ANOVA     91

Visualizing Multivariate ANOVA     92

  Univariate ANOVA Results     93

  Multivariate ANOVA Results     93

  Means and Centroids     95

From ANOVA to MANOVA     96

  Using SSCP Instead of SS     98

  Getting the Among and the Within SSCP Matrices     102

  Sums of Squares and SSCP Matrices     104

Getting to a Multivariate F-Ratio     105

Wilks’ Lambda and the F-Ratio     107

  Converting Wilks’ Lambda to an F Value     108

Running a MANOVA in Excel     110

  Laying Out the Data     110

  Running the MANOVA Code     111

  Descriptive Statistics     112

  Equality of the Dispersion Matrices     113

  The Univariate and Multivariate F-Tests     115

After the Multivariate Test     116

5  Discriminant Function Analysis: The Basics     119

Treating a Category as a Number     120

The Rationale for Discriminant Analysis     122

  Multiple Regression and Discriminant Analysis     122

  Adjusting Your Viewpoint     123

Discriminant Analysis and Multiple Regression     125

  Regression, Discriminant Analysis, and Canonical Correlation     125

  Coding and Multiple Regression     127

The Discriminant Function and the Regression Equation     129

  From Discriminant Weights to Regression Coefficients     130

  Eigenstructures from Regression and Discriminant Analysis     133

  Structure Coefficients Can Mislead     136

Wrapping It Up     137

6  Discriminant Function Analysis: Further Issues     139

Using the Discriminant Workbook     139

  Opening the Discriminant Workbook     140

  Using the Discriminant Dialog Box     141

Why Run a Discriminant Analysis on Irises?     144

  Evaluating the Original Measures    144

  Discriminant Analysis and Investment     145

Benchmarking with R     147

  Downloading R     147

  Arranging the Data File     148

  Running the Analysis     149

The Results of the Discrim Add-In     152

  The Discriminant Results     153

  Interpreting the Structure Coefficients     155

  Eigenstructures and Coefficients     156

  Other Uses for the Coefficients     159

Classifying the Cases     162

  Distance from the Centroids     163

  Correcting for the Means     164

  Adjusting for the Variance-Covariance Matrix     167

  Assigning a Classification     169

  Creating the Classification Table     170

Training Samples: The Classification Is Known Beforehand     171

7  Principal Components Analysis     173

Establishing a Conceptual Framework for Principal Components Analysis     174

  Principal Components and Tests     174

  PCA’s Ground Rules     175

  Correlation and Oblique Factor Rotation     176

Using the Principal Components Add-In     177

  The Correlation Matrix     179

  The Inverse of the R Matrix     179

  The Sphericity Test     182

Counting Eigenvalues, Calculating Coefficients and Understanding Communalities     183

  How Many Components?     184

  Factor Score Coefficients     186

  Communalities     186

Relationships Between the Individual Results     187

  Using the Eigenvalues and Eigenvectors     187

  Eigenvalues, Eigenvectors, and Loadings     188

  Eigenvalues, Eigenvectors, and Factor Coefficients     190

  Getting the Eigenvalues Directly from the Factor Scores     191

Getting the Eigenvalues and Eigenvectors     192

  Iteration and Exhaustion     193

Rotating Factors to a Meaningful Solution     196

  Identifying the Factors     197

  The Varimax Rotation     200

Classification Examples     202

  State Crime Rates     202

  Physical Measurements of Aphids     206

8  Cluster Analysis: The Basics     209

Cluster Analysis, Discriminant Analysis, and Logistic Regression     209

Euclidean Distance     211

  Mahalanobis’ D2 and Cluster Analysis     214

Finding Clusters: The Single Linkage Method     215

The Self-Selecting Nature of Cluster Analysis     220

Finding Clusters: The Complete Linkage Method     223

  Complete Linkage: An Example     224

  Other Linkage Methods     227

Finding Clusters: The K-means Method     228

  Characteristics of K-means Analysis     228

  A K-means Example     229

Benchmarking K-means with R     233

9  Cluster Analysis: Further Issues     235

Using the K-means Workbook     235

  Deciding on the Number of Clusters     237

  The Cluster Members Worksheet     239

  The Cluster Centroids Worksheet     241

  The Cluster Variances Worksheet     242

  The F-Ratios Worksheet     244

  Reporting Process Statistics     247

Cluster Analysis Using Principal Components     248

  Principal Components Revisited     249

  Clustering Wines     253

  Cross-Validating the Results     256

Index     259


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020