Use IF functions (AVERAGEIF, SUMIF, COUNTIF) to perform quick data analysis. (And maybe run a soccer team.)

What is data analysis, anyway? It's the process of inspecting data, and discovering useful information about that data.

Its a way of asking questions of the data, and getting answers. In the video below, you'll see a few everyday examples.

Let's say, for example, that were helping develop strategy for a soccer team, and wanted to know what the average points were for a team that had a certain number of draws?

Perhaps, knowing the answer to that question would help you recommend a strategy of pressing for 3-point wins late in games (at the risk of losing more games), or playing more defensively and going for the 1-point draws.

In other words, are you more likely to get into the playoffs with more draws, or less? Or does it matter?

The mixture of sports and data was made the topic of watercolor conversations by books like Moneyball. Analytics is used extensively in baseball, basketball, football, and pretty much any other professional sport.

You can start to extract the answers to questions like these by using the IF functions in Microsoft Excel.

If you're considering a career in data analytics - maybe even in the front offices of your favorite sports team - then learning how to extract useful info from rows of spreadsheet data is a must.

How to use the IF fuction

As demo'd in the video, the IF function performs a logical test on a range of date. It returns one value for a TRUE result, and another for a FALSE result.

For example, you might use it to indicate (and later sort) a list of students who have received a "passing" score on a test. The function would look something like this:


And note that more than one condition can be tested by nesting IF functions. The IF function can also be combined with logical functions like AND and OR.

How to use the AVERAGEIF

The AVERAGEIF function help data analysts find a tendency within a range of data. It does this by averaging a specified range for entries that match a criteria.

The AVERAGEIF function uses the following arguments:

  1. Range (required argument) – It is one or more than one cells that we want to average. The argument includes numbers or names, arrays, or references that contain numbers.
  2. Criteria (required argument) – Criteria determines the cell that will be averaged. Criteria can be in the form of an expression, number, cell reference, or text that defines which cells are averaged. For example, 12, “<12”, “Baby” or C2.
  3. Average_range (optional argument) – It is the actual set of cells that we wish to average. If a user omits it, the function will use the range given.


You'll see how it works in the video below. You're about 6 minutes away from your first few steps in analyzing data in Microsoft Excel...

and maybe even helping  Sporting KC raise their next MLS Cup!

Of course, you can learn much more about how to save time with the apps you use every day by getting enrolled in the All Access subscription at