Getting Started in Excel or Spreadsheets: First IF Function

14/07/26 •0•0

Let's get to know our first function in Excel! The IF function and what we can do with it

撰写者Leon

翻译者Editorial

修订者Editorial

What is the IF function in Excel or Spreadsheets

The IF function is one of the most useful and popular functions. It makes a logical comparison between two values and performs an action according to the result. In other words, if, depending on the condition, you can perform two different tasks, it is perfect for you.

The simplest way to know how to use the IF function is as follows:

> =IF(logical test;value if true;value if false)

Breaking Down a Function

Let's start with the basics: an Excel function has a name, and this name can vary from "IF" to "LARGE", among many other elements. For Excel to identify that the word to be executed is a "function" and not actually "a word", you must indicate this to Excel by placing an equal sign before the phrase.

Equal signs before any phrase in Excel indicate to the program that we will execute code from that point onward, so every word starts being identified as a function to be interpreted.

A function comes right after an equal sign, and immediately after that it opens with parentheses. Just like a mathematical function, whatever is inside the parentheses are the function's INPUTs, that is, the elements necessary for that function to work.

The INPUTs of functions are separated by semicolons (;)! The IF function therefore has 3 INPUTs:

- logical test is the condition that will be tested

- value if true is the result if the condition is true

- value if false is the result if the condition is false

There may be INPUTs that are not mandatory; these will be inside square brackets, indicating that if you do not choose a value for them, the system will ignore them. Some INPUTs may have default values pre-established by the program; we will see those later.

Testing the IF Function

=IF(2+2=4; "We got it right!"; "We got it wrong!")

Be careful with words after the equal sign; to tell the program that it is actually a word, put it inside quotation marks.

In this case, the program will perform the following logical test: 2+2=4?

If yes, it will write "We got it right!" on the screen. If not, it will write "We got it wrong!" on the screen.

Since it is true, it will write "We got it right!" in the cell.

Logical tests generally accept operators such as equal to (=), different from (<>), greater than (>) or less than (<). They also accept numbers: if it is 1, it is true. 0 is false.

=IF(A2="Italian"; "Mama mia"; "This is Brazil")

It is also possible to compare cells or words with a specific cell. In the example above, it compares what is in cell A2 and checks whether it is the word "Italian" in the cell. If it is, it will respond with "Mama mia"; if not, it will respond with "This is Brazil".

A Nest of IFs, Also Called Nested IFs

We call them Nested IFs when we place functions inside others. The name comes precisely from the mental image we get as soon as we see a jumble of functions calling one another.

Imagine the following situation: you check the traffic light; if it is red, your car stays stopped; if it is green, you can move. Now, in addition to that, if you move, you can choose to drive at 80 km/h if it is sunny or 60 km/h if it is raining. In this situation, we are seeing two IFs, one inside the other.

This is how we would solve it:

=IF(light=green; IF(weather=sun;"80km/h";"60km/h"); "Stopped at the light")

Notice that we have 2 IFs, one inside the other. The second IF only occurs when the light is actually green.

We do not need to stop there; we can add another IF if it is raining and the light is green:

=IF(light=green; IF(weather=sun;"80km/h";IF(road=dangerous;"40km/h";"60km/h"); "Stopped at the light")

Now we added an IF in case the weather is rainy and the light is red. When both of these occur, we will test whether the road is dangerous; if it actually is, we will drive at 40km/h instead of 60km/h.

We can keep going by adding more IFs, but notice how it becomes increasingly difficult to read, which is why we call these functions "jumbled" like a nest. It is not recommended to use Nested Functions, but sometimes we are forced to because the situation requires several conditions.

Exercises

1. Create the following conditions in Excel:

a) If 3+1 is greater than 4, say "TRUE"; otherwise, say "FALSE".

b) If 3 squared is greater than 8, say "TRUE"; otherwise, say "FALSE".

c) If 128 minus 62 times 2 is different from 4, say "TRUE"; otherwise, say "FALSE".

d) If 200 divided by 10 plus 40 is equal to 60, say "TRUE"; otherwise, say "FALSE".

e) If the letters F + O + O + T + B + A + L + L form the word FOOTBALL, say "TRUE"; otherwise, say "FALSE".

f) If the word ARISTOTLE is equal to the word GREEK, say "TRUE"; otherwise, say "FALSE".

2. Create a nest of IFs with the following true conditions:

a) If 2 + 2 is greater than or equal to 4

b) If 1 + 3 is equal to 4

c) If 5 + 5 is less than 20

d) If 10 * 2 is equal to 20

Ask the function to return "All true" if all are true.

3. Create a nest of IFs with the following true conditions:

a) If 2 + 3 is greater than or equal to 40

b) If 1 + 3 is equal to 3

c) If 5 + 5 is less than 10

d) If 10 * 2 is equal to 30

Ask the function to return "All false" if all are false.

4) Write the following values in 3 cells in your Excel:

- traffic light color ("yellow", "red" and "green")

- Whether it is far from the light ("far" and "near")

- Whether it is raining ("rain" and "sun")

Using what you wrote in these 3 cells, say whether the car should "stop", "accelerate" or "stay calm" using the following knowledge:

- If it is green and raining, stay calm

- If it is green and sunny, speed up

- If it is yellow and far away, stop the car

- If it is yellow and close, speed up

- If the light is red, stop

5) Given a grade in cell A1, write a formula using nested IFs to classify the grade as follows:

- If the grade is greater than or equal to 9, return "Excellent".

- If the grade is greater than or equal to 7, return "Good".

- If the grade is greater than or equal to 5, return "Average".

- Otherwise, return "Insufficient".

6) Given an age in cell A1, write a formula using nested IFs to classify the age as follows:

- If the age is less than 12, return "Child".

- If the age is less than 18, return "Teenager".

- If the age is less than 60, return "Adult".

- Otherwise, return "Elderly".

7) Given three sides of a triangle in cells A1, B1, and C1, write a formula using nested IFs to:

- Check whether the sides form a valid triangle.

- If valid, classify the triangle as "Equilateral", "Isosceles", or "Scalene".

- Otherwise, return "It is not a valid triangle".

8) Given the weight in cell A1 (in kg) and the height in cell B1 (in meters), write a formula using nested IFs to calculate the BMI (Weight / Height ^2) and classify it as follows:

- If the BMI is less than 18.5, return "Underweight".

- If the BMI is less than 24.9, return "Normal weight".

- If the BMI is less than 29.9, return "Overweight".

- If the BMI is less than 34.9, return "Obesity grade 1".

- If the BMI is less than 39.9, return "Obesity grade 2".

- Otherwise, return "Obesity grade 3".