Using Logic and Manipulating Text

Making Decisions with the IF Function

One of Excel’s most powerful features is its ability to perform different actions based on whether a condition is met. The core of this is the IF function.

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

• logical_test: Any value or expression that can be evaluated to TRUE or FALSE. (e.g., A2 > 10, B2 = "Completed")
• value_if_true: The value that is returned if the logical test is TRUE.
• value_if_false: The value that is returned if the logical test is FALSE.

Example: Imagine you want to assign “Pass” or “Fail” based on a score in cell A2. If the passing score is 60, the formula would be:

=IF(A2>=60, "Pass", "Fail")

Handling Multiple Conditions

What if you have more than two outcomes?

1. Nested IF Statements (The Old Way) You can place an IF function inside another IF function. For example, to assign grades:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "D")))

While this works, it can become very confusing to read and edit.

2. The IFS Function (The Modern Way) A much cleaner solution for multiple criteria is the IFS function.

Syntax: =IFS([logical_test1, value_if_true1], [logical_test2, value_if_true2], ...)

=IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", A2<70, "D")

Excel checks each condition in order and stops at the first one that is TRUE.

Combining Criteria with AND and OR

Sometimes your decision depends on more than one condition at the same time.

• AND Function: Returns TRUE only if all of its arguments are TRUE.
• OR Function: Returns TRUE if any of its arguments are TRUE.

You typically use these inside the logical_test part of an IF function.

Example with AND: A bonus is given only if an employee is “Full Time” (cell C2) AND their years of service (cell G2) are 5 or more.

=IF(AND(C2="Full Time", G2>=5), "Eligible for Bonus", "Not Eligible")

Example with OR: A person gets a special status if they are a “Mother” (cell B2) OR have more than 5 children (cell C2).

=IF(OR(B2="Mother", C2>5), "Deserves", "Doesn't Deserve")

Working with Text Data

1. Combining Text (Concatenation) You can merge text from multiple cells using the & symbol or the CONCATENATE function. The & symbol is generally quicker.

Example: To combine a first name in A2 and a last name in B2 with a space in between:

=A2 & " " & B2

This would produce a result like “John Smith”.

2. Cleaning Up Text with TRIM Often, when you import data, you get extra, unwanted spaces. The TRIM function removes leading/trailing spaces and reduces multiple spaces between words to a single space.

Syntax: =TRIM(text)

=TRIM(A2)

3. Splitting Text with Text to Columns If you have a column with combined data (like “Firstname Lastname”), you can easily split it into separate columns.

1. Select the column of data you want to split.
2. Go to the Data tab and click Text to Columns.
3. Choose Delimited if your text is separated by a character like a comma, space, or tab.
4. Follow the steps to specify the delimiter and the destination for your new columns.

Enforcing Rules with Data Validation

Data Validation allows you to control what kind of data can be entered into a cell. This is critical for preventing errors.

You can find it under Data > Data Tools > Data Validation.

Common Uses:

• Restricting to Numbers: Allow only whole numbers or decimals.
• Creating a Dropdown List: This is one of the most popular uses. Instead of letting users type, you can give them a list of valid options to choose from.

To create a dropdown list:

1. Select the cell(s) where you want the list.
2. Go to Data Validation.
3. In the Settings tab, under Allow, choose List.
4. In the Source box, you can either type your list items separated by commas (e.g., Yes,No,Maybe) or select a range of cells that contains your list.
5. Click OK.