IF

For any The Office fans out there, fasten your seatbelts. It's time for the C.R.I.M.E.A.I.D. (Crime Reduces Innocence Makes Everyone Angry I Declare) fundraising event!

In this episode of The Office, Dunder Mifflin gets broken into and robbed, prompting Michael to plan an auction to raise money for the stolen items. His goal: $1,000,000.

If Michael had set up an Excel sheet with all these items, it might look something like this:

We can use an IF function in F2 to determine whether or not Michael meets his $1,000,000 goal!

If his goal is met, this cell will display "YES". If his goal has not been met yet, this cell will display "NO".

IF functions determine whether or not a condition is met, then assume a value depending on if the condition is true or false.

Coding an IF function

To code the C.R.I.M.E.A.I.D. IF function, let's first view the template for IF functions:

=IF(logical_test, [value_if_true], [value_if_false])

The logical_test is where we'll be determining whether or not we've reached the $1,000,000 goal. Keep in mind, this is the condition that we discussed earlier.

In our case, the condition is whether or not the sum of all donations (in column C) totals $1,000,000 or above. Therefore, we can write our logical_test like so:

=IF(SUM(D:D) >= 1000000, [value_if_true], [value_if_false])

If this sum reaches or exceeds $1,000,000, then the [value_if_true] must be displayed. What should this be? Remember what we said earlier...

If his goal is met, this cell will display 'YES'. If his goal has not been met yet, this cell will display 'NO'.

Therefore, we'll place the following in our IF function:

=IF(SUM(D:D) >= 1000000, "YES", [value_if_false])

Now for our [value_if_false] value...

If his goal is met, this cell will display 'YES'. If his goal has not been met yet, this cell will display 'NO'.

So, we'll place "NO" in our function for [value_if_false].

=IF(SUM(D:D) >= 1000000, "YES", "NO")

When we plug this into Excel, we get "NO" as a result.

Poor Michael hasn't met his fundraising goal yet! Let's bid up on Phyllis' hug to cheer him up.

Other comparison operators

We just used the ">=" comparison operator...

=IF(SUM(D:D) >= 1000000, "YES", "NO")

...but there are more that we can use in our logical_test. Here are the most popular ones:

Operator	Description	Example (num = 1)
=	__ equals __	"num = 1" TRUE. "1 equals 1" is a true statement.
<>	__ does not equal __	"num <> 1" FALSE. "1 does not equal 1" is a false statement.
>	__ is greater than __	"num > 0" TRUE. "1 is greater than 0" is a true statement.
<	__ is less than __	"num < 0" FALSE. "1 is less than 0" is a false statement.
>=	__ is greater than or equal to __	"num >= 2" FALSE. "1 is greater than or equal to 2" is a false statement.
<=	__ is less than or equal to __	"num <= 1" TRUE. "1 is less than or equal to 1" is a true statement.

Check out the practice problem below for some practice using different comparison operators!