Software

Two ways to find a conditional average in Excel

There are two ways to calculate a conditional average in Excel, both involve some logic and some special functions.

Excel's SUMIF() and COUNTIF() are two of my favorite summarizing functions and we've talked about both frequently in this blog. What we haven't discussed is how to return a conditional average. For instance, using the simple sheet below, you could easily determine the average transaction using =AVERAGE() to evaluate the data in column C.

Now let's suppose you need to summarize that data by calculating the average number of units sold per region. Let's look at a couple of ways to return such a conditional average. Depending on your needs, you could use SUMIF() and COUNTIF() to return additional summary information and build on that.

First, you'd enter the criteria. In this case, that's a unique list of the regions (E3:E8). Next, you'd start summarizing:

F3: =SUMIF($B$2:$B$13,$E3,$C$2:$C$13)
G3: =COUNTIF($B$2:$B$13,$E3)
H3: =F3/G3

Then, you'd copy F3:H3 to create the summary range (F3:H8).

Both functions use the text in column E as the criteria. The SUMIF() function returns the total sold per region; COUNTIF() returns the total transactions per region. The simple expression, F3/G3, used in column H returns the average sold per region. In other words, there were 3 transactions made in the Frankfort region for a total of 244 units sold. The average Frankfort sale is 81.3 units.

A different function

The helper functions are a nice touch as they supply additional information that you might need. But, you could use the AVERAGEIF() to return the same information. Excel's AVERAGEIF() function uses the same logic and syntax as the SUMIF() and COUNTIF() functions. AVERAGEIF() returns the average of the values within a specific range that meet a specific condition.

This function uses the following syntax:

AVERAGEIF(range, criteria, [avgrange])

where range identifies the cells that must meet critieria, and avgrange identifies the cells you want to average. This function ignores Boolean values and empty cells.

Whether you use a simple expression or AVERAGEIF() doesn't matter. Neither solution shown is superior (or faster) than the other. In addition, don't forget about Excel's pivot table feature - if you don't mind losing the detail records, a pivot table is a quick way to summarize data.

Two example Excel demonstration files are also available.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
ms344
ms344

You could simply highlight the range $A$1:$C$13, then Insert a pivot table (Alt+N, V, T, Enter), drag "Region" to Row Labels, "Sold" to Values, click on "Value Field Settings", select "average", and - voila! Or, if you prefer, "Bob's your uncle".

celander
celander

For reporting purposes, the above method works great. If you are just looking for a quick conditional average, I prefer to use Sort or Filter and the highlight the column, look to the Status Bar and it gives you the average, count, and sum all in one place.

ssharkins
ssharkins

Thanks for the reminder -- sometimes the visual is all you need!

Editor's Picks