Career

Solving hard Excel problems: Part I

January 4, 2022

5 min read

Welcome to another entry in our series of Dr. Wayne Winston’s best Excel tips! I’m Wayne Winston, Becker’s resident expert on all things Microsoft Excel. Let’s learn another helpful Excel skill that can make your accounting and finance work a bit easier.

In my consulting engagements, I am often asked: “How do I get good at Excel?” Simply put, I think the two keys to becoming good at Excel are as follows:

Master Excel’s key functions. The Becker Excel Certificate and my new book Data Analysis and Business Modeling with Office 365 can help you master Excel’s important functions.
Learn how to combine multiple Excel functions to solve the problem at hand.

In the next few months, our articles will focus on solving interesting problems whose solutions require the ability to combine one or more Excel functions.

This month’s problem was brought to me by my former student Mark Cuban (I think you know him!) Mark owns the Dallas Mavericks and is always interested in analyzing NBA referees’ tendencies. In this month’s article, we will show you how to determine the fraction of calls each referee made against the home team. Next month, we will calculate how many games were called by each referee. Copy and paste this link into a new browser tab to download the data and follow along. As shown in Figure 1, each row of data contains the following information about an official’s call:

Column A contains all information about the call.
Column B gives the type of call.
Column C gives the team that committed the foul.
Column D gives the official who made the call.
Column E indicates if the call was against the Home (H) or Away (A) team.
Column F gives the Game ID.

Figure 1: Referee data table

Of course, we want to our analysis to update automatically if we add new data. As the data in the file is already in an Excel table, we know that our formulas and charts will update automatically when we add new source data, as a simple “Refresh” will update a Pivot Table.

Which referees are homers?

Say we want to calculate the fraction of calls made against the home team for each referee. Select the data and insert a new Pivot Table (Insert à Pivot Table) with the row field as “ref” and column field as “location.” We also drag location to the value field and select “count of.” We right click on the Pivot Table and under “Show Values As,” we select “% of Row Total.” This yields the Pivot Table shown in Figure 2. We see that some referees like A Smith appear to be swayed by the home crowd. Overall, the bottom of the Pivot Table tells us (surprisingly) 51% of the calls were made against the home team. If desired, we could sort on the H or A column to place the referees in order of highest percentage (or lowest percentage) of calls against the home team.

If we add new rows of data to our original source data, right clicking on our Pivot Table and hitting refresh (PivotTable Analyze à Refresh) will result in the Pivot Table updating to include the new data.

Figure 2: Home and away referee tendencies

Learn more with Excel CPE

Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning!

Check out Becker's wide range of CPE courses that teach you to make the most of this powerful tool:

These and many more Excel-focused, CPE credit-earning courses are included in Becker's Prime CPE subscription. Sign up now for 12 months of access to over 1700 on-demand, webcast, and podcast CPE courses!