RACON Functions:

COUNTIF, SUMIF, AVERAGEIF,

COUNTIFS, SUMIFS, AVERAGEIFS,

MAXIFS, MINIFS

15 slides

Click the right arrow to view the next slide.

RACON Functions:

The Logical Test

What's the deal with RACON Functions?

The Range-Based Conditional (RACON) functions are supercharged IF functions that perform aggregations.

  • The basic IF function takes the contents of a single cell and compares it to some value.
    • RACONs compare the contents of an entire range of cells to some value.
  • The IF function returns your custom results in place of TRUE and FALSE.
    • RACONs perform summary (aggregate) operations, like sum and average, on items that pass the test.

The IF function tests the value of one cell and compares it to:

  • some particular value (number, text) you type in or
  • some value from another cell (cell reference).

The logical test in an IF-function statement is a logical formula and can be broken down into 3 parts:

 

=IF(A1>15, "Hooray!", "Boo!")

         PART 1                             PART 2                                         PART 3

      Test Value                Comparison Operator               Comparison Value

The IF Function's Logical Test

your custom results

= value to be tested

= value the Test Value is compared to

The RACONs' Logical Test

RACON functions test multiple cells in a range. So, Part 1 of the logical test is no longer a reference to a single cell; it's a reference to a range of cells, like A1:A10.

 

Because of this, programmers found it more efficient to separate Part 1 into its own argument. Part 2 and Part 3 stayed together in a separate argument, but now have to be written as a text string.

(Click the down arrow to see a list of the comparison operators.)

These make up Part 2 of your logical test.

 

=     equal to           A1 = A2

>     greater than    A1 > A2

<     less than          A1 < A2

>=   greater than or equal to   A1 >= A2

<=   less than or equal to         A1 <= A2

<>   not equal to    A1 <> A2

FYI: Comparison Operators

(Click the up arrow to return or the right arrow for the next slide.)

The IF function's logical test

is one argument—a logical formula

Part 1

Part 2

Part 3

A1

>

15

=IF(

, TRUE, FALSE)

a single cell

1st argument

2nd argument

3rd argument

The RACON functions

split it into two arguments:

(1) a range and (2) the rest of the formula in a text string

Part 1

Part 2

Part 3

A1:A10, "

>

15")

now a range of cells

this argument is a text string

1st argument

2nd argument

=COUNTIF(

The RACONs' Logical Test

With RACON functions, logical tests are fed to the function in two arguments.

 

For example, the syntax for the simplest RACON, the COUNTIF function, is:

 

=COUNTIF( Part 1 , Part 2 & Part 3 together )

 

  • Part 1 is always a reference to a range of cells.
  • Part 2 and Part 3 are combined into a single string of text. 

1st argument

2nd argument

Making Parts 2 & 3 a String of Text

You do this using some basic rules for forming literal text strings. Specifically:

  • Part 2. Put comparison operators in quotes.
  • Part 3. Put numbers and text in quotes with the comparison operator.
    • =COUNTIF(A1:D100, ">=15")   Comparison value is a number
    • =COUNTIF(A1:A10, "=Bob")     Comparison value is text
  • Part 3. If the comparison value comes from another cell, you use a cell reference. That cell's contents are already considered a text string. So put the comparison operator in quotes, and then use the concatenate operator (&) to combine the two text strings into one.
    • =COUNTIF(A1:A10, ">="&B1)  Comparison value is in another cell

Making Parts 2 & 3 a String of Text

all in quotes

Part 3 not in quotes

Part 2 in quotes

=COUNTIF(A1:D100, ">=15")   numbers

 

=COUNTIF(A1:A10, "=Bob")     text

 

=COUNTIF(A1:A10, ">="&B1)  cell references

all in quotes

In quotes or not, Part 2 and Part 3 make up just one text string, one argument.

Understanding the RACON Logical Test

Compared to logical formulas and the two basic logical functions IF and IFS, the syntax for the RACON logical test is weird and difficult for newcomers to understand.

 

  • The first argument, Part 1, is easy. It's a range, like A1:A10.
  • The second argument is easier to understand if you remember to make it a single string of text. Just combine Part 2 and Part 3 using "" and &.

 

Understanding the RACON Logical Test

Note: 

  • The first argument in the RACON logical test is called (predictably enough) range.
  • The second argument is called criteria.*
    • * Yes, I know it's grammatically incorrect, but that's what it is.

If you leave out the comparison operator, Excel assumes it's equal to (=). So Part 2, the operator, can be omitted.

  • =COUNTIF(A1:A10, "15") is the same as =COUNTIF(A1:A10, "=15")    numbers

 

  • =COUNTIF(A1:A10, "Bob") is the same as =COUNTIF(A1:A10, "=Bob")  text

 

  • =COUNTIF(A1:A10, B1) is the same as =COUNTIF(A1:A10, "="&B1)  cell reference

An Important Exception

If the comparison operator is equal to (=),

     AND...

the comparison value is a number, you do not need to enclose that number in quotation marks.

=SUMIF( A1:A10, 5 )

gives the same result as 

=SUMIF( A1:A10, "=5" ) and

=SUMIF( A1:A10, "5" ).

An Extension of that Exception

for Numbers

Fin

UP NEXT: COUNTIF, SUMIF, & AVERAGEIF

Made with Slides.com