Chapter 10: Advanced Formulas

Introduction to Excel taught you most of the functions on MO-211. Lessons 2–5 covered IF, AND and OR, the conditional aggregate functions (SUMIF, COUNTIFS and the rest), XLOOKUP, VLOOKUP, INDEX and MATCH, NOW and TODAY, and PMT and NPER. This chapter adds the functions those lessons didn't cover, and it focuses on the skill MO-211 cares about most: nesting functions inside each other to answer a question in one formula.

10.1 IFS, SWITCH and nested logic

10.1.1 A quick review of nesting

A nested function is a function used as an argument to another function. You've already written formulas like these:

=IF(AND(B2>=90, C2="Yes"), "Honors", "Standard")
=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", "F")))

The second formula works, but each extra grade adds another IF and another closing parenthesis. IFS and SWITCH do the same job with flatter formulas.

10.1.2 IFS

IFS(test1, value1, [test2, value2], …) checks each test in order and returns the value paired with the first test that's TRUE.

=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F")

Two things to remember:

• Order matters. IFS stops at the first TRUE test. If you put B2>=60 first, a score of 95 would return "D".
• IFS has no "otherwise" argument. If no test is TRUE, it returns #N/A. To catch everything else, end with TRUE as the last test, as in the formula above.

10.1.3 SWITCH

SWITCH(expression, value1, result1, [value2, result2], …, [default]) compares one expression with a list of exact values and returns the result for the one that matches. A final unpaired argument is the default, returned when nothing matches.

=SWITCH(C2, "N", "North", "S", "South", "E", "East", "W", "West", "Unknown")
=SWITCH(WEEKDAY(A2), 1, "Weekend", 7, "Weekend", "Weekday")

Use SWITCH when you're matching one value against a list of exact options. Use IFS when each test is a different comparison, like ranges of scores. Without a default, SWITCH returns #N/A when nothing matches.

10.1.4 NOT

NOT(logical) reverses TRUE and FALSE. It's most useful inside AND or OR, to say "and this is not the case":

=IF(AND(D2>=3, NOT(E2="Probation")), "Eligible", "Not eligible")
=IF(NOT(ISBLANK(F2)), "Submitted", "Missing")

NOT(E2="Probation") means the same as E2<>"Probation". Either is fine unless a task asks for NOT.

10.1.5 Putting it together

MO-211 tasks often combine several functions. Read the task and break it into parts:

"In G2, enter a formula that returns Bonus if the employee's sales are over 50,000 and they are not in the Training group, Review if their sales are under 20,000, and None otherwise."

1. The first condition has two parts joined by and, one of them negative: AND(E2>50000, NOT(F2="Training")).
2. There are three outcomes, so use IFS (or nested IFs).
3. "Otherwise" means a final TRUE test.

=IFS(AND(E2>50000, NOT(F2="Training")), "Bonus", E2<20000, "Review", TRUE, "None")

10.2 MAXIFS and MINIFS

Lesson 4 of Introduction to Excel covered SUMIFS, COUNTIFS and AVERAGEIFS. MAXIFS and MINIFS work the same way, but they return the largest or smallest value that meets the criteria:

MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Like SUMIFS, the range being measured comes first, followed by pairs of criteria ranges and criteria. For a table Sales with the columns Region, Rep, Product and Amount:

=MAXIFS(Sales[Amount], Sales[Region], "West")
=MINIFS(Sales[Amount], Sales[Region], "West", Sales[Product], "Kayak")
=MAXIFS(Sales[Amount], Sales[Region], H2, Sales[Amount], "<"&H3)

Criteria work the same as in the other -IFS functions:

• Text or numbers to match exactly: "West", 500.
• Comparisons in quotation marks: ">=1000", "<>Closed".
• A comparison with a cell, joined with &: ">"&H3.
• Wildcards: "K*" (starts with K), "*kit*" (contains kit), "?" (any one character).

If no rows match, MAXIFS and MINIFS return 0, not an error.

10.3 LET

LET(name1, value1, [name2, value2], …, calculation) gives names to values or calculations inside a formula, then uses the names in a final calculation. It makes long formulas shorter and easier to read, and it calculates each named part only once.

Without LET, a formula might repeat the same calculation:

=IF(SUM(B2:D2)*1.08>500, SUM(B2:D2)*1.08*0.9, SUM(B2:D2)*1.08)

With LET, you name it once:

=LET(total, SUM(B2:D2)*1.08, IF(total>500, total*0.9, total))

Read it as: "Let total be SUM(B2:D2)×1.08. Then, if total is over 500, return total×0.9; otherwise return total."

You can define several names. Each name can use the ones before it:

=LET(subtotal, B2*C2, tax, subtotal*$H$1, subtotal + tax)

Rules for names:

• They must start with a letter and can't look like a cell reference. taxRate and total are fine, but A1 and tax1 are not, because TAX is a column.
• They work only inside that formula.
• The last argument is always the calculation that gives the result. LET needs an odd number of arguments.

10.4 HLOOKUP

HLOOKUP is the horizontal version of VLOOKUP. VLOOKUP searches down the first column of a range and returns a value from another column. HLOOKUP searches across the first row and returns a value from another row.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Argument	Meaning
lookup_value	The value to find in the first row.
table_array	The range to search. Its first row holds the values to search.
row_index_num	Which row of the range to return from: 1 is the first row, 2 the second, and so on.
range_lookup	FALSE for an exact match. TRUE (the default) for the closest match below, which needs the first row sorted in ascending order.

Suppose B1:M1 holds month names (Jan to Dec), row 2 holds sales, row 3 holds costs and row 4 holds staff counts:

=HLOOKUP("Apr", B1:M4, 3, FALSE)      April's costs
=HLOOKUP(P2, $B$1:$M$4, 2, FALSE)     sales for the month in P2

10.5 FILTER and SORTBY

FILTER and SORTBY are dynamic array functions, like SORT and UNIQUE (chapter 5). They return a whole range of results that spills from one cell.

10.5.1 FILTER

FILTER(array, include, [if_empty]) returns the rows (or columns) of array where include is TRUE.

Argument	Meaning
array	The data to return, such as a whole table or some of its columns.
include	A condition that's TRUE or FALSE for each row, the same height as array.
if_empty	What to return when no rows match, such as "None". Without it, FILTER returns #CALC! when nothing matches.

=FILTER(Sales, Sales[Region]="West")
=FILTER(Sales[[Rep]:[Amount]], Sales[Amount]>=1000, "No sales over 1,000")
=FILTER(Sales, Sales[Region]=H1, "None")

To combine conditions, put each one in parentheses:

• Multiply them for and: both must be true.
• Add them for or: either can be true.

=FILTER(Sales, (Sales[Region]="West")*(Sales[Amount]>1000))
=FILTER(Sales, (Sales[Product]="Kayak")+(Sales[Product]="Canoe"))

This works because TRUE counts as 1 and FALSE as 0. A product is 1 only when both conditions are 1, and a sum is 0 only when both conditions are 0.

10.5.2 SORTBY

SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …) sorts array by one or more other ranges.

=SORTBY(Staff[Name], Staff[Salary], -1)
=SORTBY(Staff, Staff[Department], 1, Staff[Salary], -1)

The first formula lists names from highest salary to lowest, without showing the salaries. The second sorts the whole table by department, A to Z, and by salary from highest to lowest within each department.

SORT (chapter 5) sorts by a column number inside the array. SORTBY sorts by ranges, which has two advantages:

• You can sort by a column that isn't in the result, as in the first example.
• You can sort by several columns, each with its own order, as in the second.

Each by_array must be the same height as array. Sort order is 1 for ascending (the default) or -1 for descending.

10.5.3 Combining them

Dynamic array functions nest well:

=SORTBY(FILTER(Sales, Sales[Region]="West"), FILTER(Sales[Amount], Sales[Region]="West"), -1)
=SORT(FILTER(Sales[[Rep]:[Amount]], Sales[Amount]>1000), 2, -1)

The second formula is shorter because SORT sorts by a column number of the filtered result. Use SORTBY when you need to sort by something that isn't in the result.

10.6 WEEKDAY and WORKDAY

Lesson 5 of Introduction to Excel covered dates, TODAY and NOW. MO-211 adds two functions for working with days of the week.

10.6.1 WEEKDAY

WEEKDAY(serial_number, [return_type]) returns a number for the day of the week of a date. return_type chooses the numbering:

return_type	Numbering
1 or omitted	Sunday = 1 through Saturday = 7
2	Monday = 1 through Sunday = 7
3	Monday = 0 through Sunday = 6

With return_type 2, weekends are 6 and 7, which makes weekend checks simple:

=IF(WEEKDAY(A2, 2)>5, "Weekend", "Weekday")

To show the day's name instead of its number, use a number format (dddd, chapter 9) or =TEXT(A2, "dddd").

10.6.2 WORKDAY

WORKDAY(start_date, days, [holidays]) returns the date that is a number of working days before or after a start date. It skips Saturdays, Sundays and any dates you list as holidays.

=WORKDAY(A2, 10)                  10 working days after A2
=WORKDAY(A2, 10, Holidays[Date])  the same, also skipping listed holidays
=WORKDAY(A2, -5)                  5 working days before A2

The start date itself isn't counted. WORKDAY returns a serial number, so format the cell as a date if it shows a number like 46213.

10.7 Practice