You already know how to use the IF function to make simple decisions. But what happens when you need to check multiple conditions at once? This is where nesting functions comes in. By placing functions inside one another, you can build sophisticated logical tests that handle complex, real-world scenarios.

The key players here are IF, AND, and OR. The AND function returns TRUE only if all of its arguments are true. The OR function returns TRUE if any of its arguments are true. When you combine them with IF, you can create powerful decision-making models in your spreadsheets.

Let's look at a common business problem: calculating sales commissions. A sales representative earns a 10% commission if they exceed $50,000 in sales and have more than 10 new clients. If they only meet the sales target but not the client target, they get 5%. Otherwise, they get no commission. A single IF function can't handle this. A nested function can.

Let’s break that down:

1. The outer IF checks the most difficult condition first using AND(B2>50000, C2>10). If both sales (in cell B2) and new clients (in C2) are above their targets, it calculates a 10% commission and stops.

2. If the first condition is false, it moves to the second IF statement. This one checks if only the sales target was met (B2>50000). If so, it calculates a 5% commission.

3. If neither of the first two conditions is true, it returns 0.

Functions like SUMIF and COUNTIF are great for summarising data based on a single condition. But business data is rarely that simple. You often need to sum, count, or average values based on several criteria at once. That's what the 'S' functions are for: SUMIFS, COUNTIFS, and AVERAGEIFS.

A key difference to remember is the argument order. In SUMIF, the sum range is last. In SUMIFS a, the sum range comes first, followed by pairs of criteria ranges and their corresponding criteria. This structure allows you to add as many conditions as you need.

Imagine you have a sales log and want to find the total sales for 'Laptops' in the 'North' region during 'Q1'.

This formula tells Excel to:

1. Look at the Sales column (D2:D6) for the numbers to add up.
2. But only include rows where the Region column (A2:A6) is "North".
3. AND where the Quarter column (B2:B6) is "Q1".
4. AND where the Product column (C2:C6) is "Laptop".

COUNTIFS and AVERAGEIFS work the exact same way, just without the initial sum range argument. They are essential for building dynamic dashboards and reports.

Often, the data you get isn't ready for analysis. It might have extra spaces, inconsistent formatting, or useful information clumped together in a single cell. Text functions are your tools for tidying up this mess.

Let's explore the most common text manipulation tools.

Extracting Text

• LEFT(text, num_chars): Grabs characters from the start of a text string.
• RIGHT(text, num_chars): Grabs characters from the end.
• MID(text, start_num, num_chars): Extracts characters from the middle.

For example, if cell A1 contains an order ID like PROD-105-X, you could use LEFT(A1, 4) to get 'PROD' or MID(A1, 6, 3) to get '105'.

Finding and Replacing

• FIND(find_text, within_text): Returns the starting position of a text string within another. It's case-sensitive.
• SUBSTITUTE(text, old_text, new_text): Replaces existing text with new text.

You can combine these powerfully. For instance, to extract a first name from 'John Smith', you can use FIND to locate the space and then use LEFT to pull out everything before it: =LEFT(A1, FIND(" ", A1) - 1).

Cleaning and Joining

• TRIM(text): This is a lifesaver. It removes all extra spaces from text, leaving only single spaces between words. It's often the first function you use on imported data.
• CONCAT(text1, text2, ...): Joins multiple text strings into one. For example, CONCAT(A1, " ", B1) would combine a first name in A1 and a last name in B1 into a full name.

By mastering these intermediate functions, you move beyond simple calculations. You can now build logic, summarise complex datasets, and clean raw data—three essential skills for any serious Excel user.

Ready to test your knowledge? Let's see how well you've grasped these new functions.

These functions are the building blocks for more advanced analysis and automation, preparing you for the powerful tools you'll learn about next.