Career

Solving hard Excel problems: Part I

Open silver laptop

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:

  1. 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.
  2. 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
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
Figure 2: Home and away referee tendencies

 

Next month, we will determine how many games each referee called. This is a much harder problem and will require two uses of Office 365’s UNIQUE function as well as use of our old friend the COUNTIF function.

Want to improve your accounting Excel skillset? Enroll in Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate and learn essential Excel functions for accounting, from fundamentals all the way to in-depth applications of data analytics.

Try Becker's CPA Exam Review FREE for 14 days
FacebookLinkedinXEmail

About the author

Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won more than 40 teaching awards at Indiana University. He has written more than a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Office 365 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks. His Linkedin class on Forecasting has been completed by over 250,000 learners. He has also published 30 research articles.

Featured

Enrolled Agent vs CPA
Enrolled Agent vs CPA
Read More
The best side hustles for accountants
The best side hustles for accountants
Read More
Acing your accounting job interview
Acing your accounting job interview
Read More

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue