How Much Do You Know About IF Statements?
Today, keySkillset will take a closer look at the conditional formulas in Excel. Specifically, we will focus on IF Statements – IF function and its variations. You will see how easy and fast is to use Excel shortcuts. You will become more productive and save time on executing complex functions.
IF-formula is one of the most used formulas in Excel. Combining with all of its variations, it is probably the most used formula. IF-formula is used for obtaining different answers depending on a condition. For example, with IF statements, we can define a condition like this:
- In case the selected cell is greater than 5, return “important” as text.
- Alternatively, if the selected cell is not greater than 5, return “not important” as text.
IF function has 3 parameters: the condition, the value that will be shown if the condition is true, the value that will be shown if the condition is false. For example, we have a table with 4 different cities/towns and the population for each one of those cities/towns. In the cell range C2: C5, we categorize the areas in A2: A5 as city or town depending on the population.
Depending on an area has over 1 million population or not, we are trying to show different answers in the area C2: C5. We want to see the city if the selected area has over 1 million population. Otherwise, we want to see the town as a classification. Therefore, if we combine all these information, the formula in the cell C2 will look like IF(B2>1,”city”,”town”).
SumIf, AverageIf Functions?
The first two variations of IF-function that we will look at are SumIf and AverageIf. We use both of these formulas to execute a mathematical operation with a specific condition.
The first one we are going to talk about is SumIf function. We use SumIF to the summation of numbers in a selected cell which suits the condition. To use the SumIf function, we need to define 3 different parameters. The first one is the range that we will look for the condition. Then, the condition that we are looking for and, finally, the summation range that we will sum the numbers.
Looking at the example in the pictures, in cell B3, we are trying to find out the total sales number for the city Miami. First, we selected D2: D7 as the condition range, which is the column with the city names. Second, since we are looking for the total sales, we selected A3 as the criteria that we are looking for, which is Miami. And for the last step, we selected F2: F7 because we want to sum the sales number for Miami. After we enter all these parameters, we pressed enter to get the correct answer of 120.000 in the cell B3.
The AverageIf function is used for finding the average of the numbers, which suits the condition in a specific cell range. All the parameters and the usage of this formula is the same with SumIf formula. The only difference is the name – AverageIf (we have the result of the average of the number, not the sum).
CountIf is the formula used when we want to count the number of a specific condition occurred in a specific cell range. For example, if we want to find how many numbers in a specific range is greater than 10, we can use CountIf function to find the answer. We use the same function to find how many times a name is repeated in a specific name list.
If we look at the example below, we are trying to find out how many times the city New York is repeated in the city list in cells D2: D6. First, we need to input the first variable, which is the range that we are going to check the condition. Therefore, we selected D2: D6 as the range. The second and the last variable is the condition that we are going to count in the range. For this parameter, we need to type New York as the condition, or in this situation, we selected A2 as the second parameter. As you can see, the formula in B3 looked through the cell range D2: D6 and found 3 different cells that have the same condition which we were looking for.
IfError is a special variation of IF-function in Excel. We mostly use IfError to define some conditions where we get an error in Excel. For example, if you look at the pictures below, we are trying to calculate a division. In cell B4, we are trying to execute the calculation of B2/B3, which in this scenario is 100/0. In this situation, since we are trying to divide a number by 0, we are getting an error from Excel. It is a special condition in mathematics and Excel couldn’t calculate and found the answer.
By using IfError, we can define the outputs for these types of situations. For example, in the pictures, if the calculation of B2/B3 creates an error, instead of typing an error, show “NA” in the cell. By typing the formula =IFERROR(B2/B3,”NA”) in cell B4, we will have “NA” for every single time when the result of the calculation B2/B3 is something that Excel can’t define.
IsError is our last variation of conditional statements that we will look at in this blog. Similar to IfError, with IsError formula, we are trying to find out if the result of a situation in a specific cell that we selected will give an error or not.
For example, in the pictures below, we are trying to find out if the equation in cell B4 gives us an error or not. IsError has only one parameter so, all we need to do is selecting, in our situation, the B4 cell. Since the equation in cell B4 will give us an error because we are trying to divide 100 by 0, ISERROR(B4) will give the result of True in cell C4.
If you struggle on working with large amounts of data, try keySkillset game for free and save your time. You can become 10 times faster and more efficient.