Career

Solving hard Excel problems: Part II

March 11, 2022

7 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.

Read part one of this miniseries here.

One of the most common questions my consulting clients ask is, “How do I get good at Excel?” I believe the two keys to becoming proficient in Excel are:

Mastering Excel’s key functions. Excel’s built-in functions can help you save time by performing specific calculations. Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate and my new book, “Data Analysis and Business Modeling with Office 365,” can help you become familiar with Excel’s important functions.
Learning how to combine Excel functions. Once you’ve mastered key Excel functions, learn how to combine them to solve the problem at hand.

Problem of the month

This month’s problem is again from my former student Mark Cuban, who owns the Dallas Mavericks and is always interested in analyzing NBA referees’ tendencies. This month we will show you how to use Excel functions to determine 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.

Our worksheet contains information on over 9,000 calls by referee, as shown in the figures below.

Figure 1: Data tab: referee call data

Each row of data in Figure 1 contains the following information about each referee call:

Column A: Detailed call information
Column B: Call type
Column C: Which team committed the call
Column D: Which referee called the foul - a blank indicates that NBA.com did not list which referee made the call
Column E: Which team (Home or Away) committed the foul
Column F: Game ID

Let’s say we want to find out how many games each referee called. For example, if we want to determine how many unique game IDs are paired with the referee N Sago, we can’t just calculate the number of rows in which N Sago appears because each referee makes multiple calls during each game. To find the answer, we’ll need to use both the UNIQUE function (available only in the Office 365 version of Excel) and the COUNTIF function:

Use the UNIQUE function to create a list of unique referee-game ID combinations in columns H and I of the Delete Location tab.
Use the COUNTIF function to count the number of game IDS associated with each referee in columns L and M of the Delete Location tab.

The UNIQUE function can return unique combinations across multiple adjacent columns. Since we want each unique referee game ID combination, we deleted the Location column from the data worksheet and copied the result to the Delete Location tab. Our work is shown in Figure 2 below.

Entering the formula =UNIQUE(D1:E9476,FALSE,FALSE) in cell H2 amazingly returns each unique referee-game ID combination from the cell range D1:E9476. This is an example of a dynamic array formula, which can display results in multiple cells. The first false argument tells Excel to compare rows in the selected range in a search for unique combinations of ref and game ID. The second false argument tells Excel to return all distinct referee-game ID combinations, not just combinations that appear exactly once.

Figure 2: Delete Location tab: number of games officiated by each referee

To find out the number of games officiated by each referee, we again use the UNIQUE and COUNTIF functions to count how many times each referee occurs in column H.

Next, we create a list of referees in column L by entering the dynamic array formula =UNIQUE(D2:D9476) in cell L4. Copying from L4 to L5:L76, the formula =COUNTIF($H$3:$H$888,L4) returns the number of games officiated by each referee. For example, we find that N Sago officiated 11 games.

If we create an Excel table with our original data range A1:E9476 (using the keystroke combination Ctrl+T), then when we add new data, columns H and I will automatically update to incorporate the new data. If we use this approach, then we should enter the amazing dynamic array formula =COUNTIF(H3:H888,L4#) in cell N4. The # sign ensures that our COUNTIF formula is dynamically copied down for each referee listed in column L!

If we want to sort our final list of referees identically, we could replace the cell L4 formula with =SORT(UNIQUE(D2:D9476),1,1) to sort our list of referees in ascending alphabetical order. See Figure 3 or the Sorted tab in the worksheet.

Figure 3: Sorted tab: games by referee sorted alphabetically

Next month, we will show you how to use Microsoft Word and Power Query to import tables from PDF files into Excel.

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!