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