RACON Functions:

COUNTIFS, SUMIFS, AVERAGEIFS,

MAXIFS, MINIFS

20 slides

Click the right arrow to view the next slide.

RACONs Ending in IF

COUNTIF takes two arguments:

  1. the range to be both tested and aggregated* and
  2. the criteria (operator and comparison value) for the test.

=COUNTIF(A2:A10, ">=15")

1st argument   2nd argument

* In the case of COUNTIF, the aggregation operation is counting.

   Other aggregations are summing, averaging, and finding minimums and maximums.

RACONs Ending in IF

=SUMIF(A2:A10, "=United States", D2:D10)

1st argument           2nd argument      3rd argument

Because it's possible that the range to be tested is not the range to be aggregated, SUMIF and AVERAGEIF have an optional third argument:

  1. the range to be tested,
  2. the criteria for the test, and 
  3. the sum_range or average_range.

RACONs Ending in IF

COUNTIF, SUMIF, and AVERAGEIF are RACONs because they can test an entire range of cells.

But they can only apply one test to those cells. Hence the singular IF at the end.

The IFS RACONs

The five IFS RACONS can apply multiple tests to multiple ranges (up to 127!) in one function statement.

  1. COUNTIFS
  2. SUMIFS
  3. AVERAGEIFS
  4. MINIFS
  5. MAXIFS

But, like the three IF RACONs, they will only perform one aggregation operation at the end.

IFS Argument Structure

Here is how the arguments are arranged in a RACON IFS function:

  1. The range to be aggregated is the first argument.*
  2. Then each of the multiple tests is presented as a pair of arguments:
  • the range to be tested, and
  • the criteria (comparison operator and comparison value).

(* Click the down arrow if you want to know why it's been moved from last to first.)

IFS Argument Structure

In SUMIF and AVERAGEIF, the argument for the range to be aggregated comes last, because it's optional.

But the range to be aggregated is NOT optional in the IFS RACONs. This is because, if you have multiple test ranges, Excel won't know which one you want aggregated. It may not be any of them.

Plus, from a programming standpoint, the range to be aggregated is the only range out of a possible 128 that's not followed by a logical test (criteria). So if you put it first and get it out of the way, the routine knows that everything after that is a criterion pair (a range and a test), resulting in more efficient processing.

(Click the up arrow to go back or the right arrow to go to the next slide.)

Example:

Average Cost of Bigville Oranges

Find the average Cost of Oranges in Bigville. 

Example:

Average Cost of Bigville Oranges

First, determine the range to aggregate (average). It's the Cost column, D2:D10.

 

That's your first argument, average_range.

Example:

Average Cost of Bigville Oranges

Next, determine the range to test for the presence of the city Bigville. It's the City column, B2:B10.

 

That's your second argument, criteria_range1.

Example:

Average Cost of Bigville Oranges

Now add that test's criteria as a text string in quotes. That's "=Bigville".

 

That's your third argument, criteria1.

first test: a lone IF

(NOTE: If we stop here, with one test, it works like an AVERAGEIF statement.)

Example:

Average Cost of Bigville Oranges

Now add the next test: searching the Product column for Orange. The column is C2:C10 and the criteria is "=Orange".

They're the fourth and fifth arguments, criteria_range2 and criteria2.

first test

second test

IFS

Example:

Average Cost of Bigville Oranges

So now you've told Excel to average any value in D2:D10 that is in a row that has:

  • the value Bigville in B2:B10, and
  • the value Orange in C2:C10.

 

Let's apply these two tests as column filters to demonstrate how this works invisibly within the AVERAGEIFS function.

Example:

Average Cost of Bigville Oranges

Find the average Cost of Oranges in Bigville. 

Example:

Average Cost of Bigville Oranges

The test B2:B10, "=Bigville" eliminates all the rows with Smallville in the City column, leaving only Bigville.

Example:

Average Cost of Bigville Oranges

The IFS function keeps moving through the tests from left to right until either (a) one of the tests fails to find anything that satisfies its criteria or (b) the function runs out of tests. In case of (b), it performs the aggregation.

Here, C2:C10,"=Orange" leaves us two rows to average.

Example:

Average Cost of Bigville Oranges

So, as you can see, each test in an IFS RACON works like a column filter in a table (or table-shaped range).

  • The test B2:B10, "=Bigville" filters out all rows that don't have Bigville as their City.
  • The test C2:C10, "=Orange" filters out all rows that don't have Orange as their Product.

The rows that are left get their Cost values (D2:D10) aggregated (into an average).

Ands and Ors

It's important to note that the IFS RACONs combine tests using AND. They don't work as OR combinations. For example:

  • you can ask for City = Bigville AND Product = Orange,
  • but you can't ask for City = Bigville OR Product = Orange.

There are ways to get OR results using wildcards, the addition operator, the OR function, and SUMPRODUCT function. See me for more on that, if you're interested.

Recap

  • Use RACONs if you want to test entire ranges (usually columns) of cell values.
  • There are 3 IF RACONs:
    • COUNTIF, SUMIF, AVERAGEIF
    • Use an IF RACON if you need only one test.
  • There are 5 IFS RACONs:
    • COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS, MINIFS
    • Use an IFS RACON if you need multiple tests.
  • In tables or table-shaped ranges, RACONs will perform their aggregate calculation on the rows that meet ALL the test criteria.

Why Bother With IF RACONs?

You probably noticed that anything you can do with an IF RACON function you can do with an IFS RACON function. In fact, you have to use IFS for doing maximums and minimums. So why not just do everything with IFS?

 

Because the certification test requires that you know the IF RACONs. When you get back to the real world, you can just do IFS.

Fin

Made with Slides.com