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 Function
- Sum If, Average If Functions
- Count If Function
- If Error Function
- Is Error Function
1. IF Function
IF-formula (IF Statement) 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 3parameters: 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 categories the areas in A2: A5as city or town depending on the population.
Depending on whether an area has over 1 million population or not, we are trying to show different answers in the areas 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 this information, the formula in cell C2 will look like IF(B2>1,” city”,” town”).
2. Sum If, Average If Functions
The first two variations of IF-function that we will look at are Sum If and Average If. We use both of these IF Statements to execute a mathematical operation with a specific condition.
The first one we are going to talk about is Sum If function. We use Sum IF to the summation of number sin a selected cell which suits the condition. To use the Sum If 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 Average If 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 Sum If formula. The only difference is the name – Average If (we have the result of the average of the number, not the sum).
3. Count IF Function
Count If 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 Count If 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.
4. If Error Function
If Error is a special variation of IF-function in Microsoft Excel. We mostly use If Error 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 If Error, 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 =IF ERROR(B2/B3,”NA”) in cell B4, we will have “NA” for every single time when the result of the calculation B2/B3is something that Excel can’t define.
5. Is Error Function
Is Error being our last variation of conditional statements that we will look at in this blog. Similar to If Error, with Is Error 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. Is Error 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, IS ERROR(B4) will give the result of True in cell C4.
Conclusion
You'll significantly speed up your financial analysis if you learn and master these functions. The IF function is commonly used to evaluate and analyze data by evaluating specific conditions. It can be used to evaluate text, values, and even errors. comparisons.
Meanwhile, what you just saw is an exact simulation of our platform keySkillset. You can explore our courses here www.keyskillset.com.
.jpg)
.jpg)
.jpg)
.jpg)
.png)
