IF function and percentage formula are powerful tools in Excel that can help users manipulate data and perform calculations efficiently.
The IF function allows users to set up logical tests that return one value if the condition is true, and another value if it’s false. Meanwhile, the percentage formula can be used to calculate percentages, such as determining the percentage increase or decrease of a value.
By combining these two functions, users can create complex calculations that take into account conditional logic and percentages. In this article, we will discuss the IF percentage formula in Excel with 4 practical examples to streamline your data analysis and reporting.
The below image shows an overview of our task-
Download Practice Workbook
You can download the practice workbook from here and practice yourself.
IF Percentage Formula in Excel: 4 Easy Examples
To explore the examples, we’ll use the following dataset that represents the obtained marks out of 100 and the percentage change of a student for some subjects.
1. Applying IF Function Via Percentage Column
In this case, our goal is to learn how to use the IF and Percentage formulas. We can learn this by first creating a percentage column and then using it under a certain condition.
To use percentage form, we have to learn to convert a normal numeric value into a percentage. Technically Excel will convert any input data into a percentage by multiplying it by 100 and adding a percentage symbol (%) on the right if you opt to choose percentage formatting.
But you can also convert a number directly to a percentage value without letting it be multiplied by 100 in Excel. After that, you can easily insert the particular condition using the IF function.
Here, the IF function will return ‘Good’ if the change is greater than 0% otherwise return ‘Dissatisfactory’. The steps of this method are as follows-
- First, insert the following formula in Cell E5 to get the change in percentage-
=(D5-C5)/C5
- After that, use the Fill Handle option to use the formula for all the cells.
- If you don’t get the result in percentage format, select the whole column and go to the Home tab, and in the Number section select the Percentage format.
- Insert the following formula in Cell F5 to perform the logical test with the IF function-
=IF(E5>0,"Good","Dissatisfactory")
- After pressing the Enter button, you will get the result for this cell. Moreover, use the Fill Handle option to use the formula for all the cells.
- Finally, you will get the desired result.
Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
2. Use of IF and Percentage Altogether in One Column
Now, we want to use the IF and Percentage formulas together in a column to determine a certain value.
In Microsoft Excel, the IF function is used to check if a condition is met and then the defined statements will be shown based on the given condition. Our formula will return the evaluated percentage if it is greater than 0 otherwise return ‘Dissatisfactory’.
- First, arrange the dataset like the below image.
- Insert the following formula in Cell E5–
=(D5-C5)
- After pressing the Enter button, you will get the result for this cell. Then, use the Fill Handle option to use the formula for the whole column.
- Insert the following formula in Cell F5–
=IF(E5>0,(E5/C5),"Dissatisfactory")
- Press the Enter button, you will get the result for this cell. Moreover, use the Fill Handle option to use the formula for all the cells.
- Apply the Percentage format if you don’t get it by default.
Read More: How to Calculate Percentage of a Number in Excel (5 Easy Ways)
3. Applying Multiple IF Functions with Percentage Formula
Now we’ll see a different scenario, we want to return three statuses for the percentage of change. Change for 0 percent, equal to or less than 10%, and greater than 10%, it will return ‘Dissatisfactory’, ‘Good’, and ‘Excellent’ consecutively. For this purpose, we’ll have to use multiple nested IF functions.
- In Cell F5, type the following formula and then use the Fill Handle tool to get output for the whole column-
=IF(E5=0,"Dissatisfactory",IF(E5>10%,"Excellent","Good"))
- The first IF function will check the value of Cell F5 whether it is equal to 0% or not, if yes then it will return “Dissatisfactory”, if not then it will call the second IF function to perform another logical test.
- The second IF function will check the value whether is greater than 10% or not, if yes it will return ‘Excellent’, if not and greater than 0% then will return ‘Good’.
4. Using Percentage Formula and IF Function with AND Criteria
In our very last example, we’ll combine the AND function with the IF function and percentage formula for a criteria reason. The below dataset shows the percentage decreases for some laptops, we’ll filter the models whose decreases are between 10 to 30%.
- Insert the following formula in Cell F5–
=IF(AND(E5>=10%,E5<=30%),"Yes","No")
- Then apply the Fill Handle tool to copy the formula for the whole column.
Here, the AND function will join the two conditions- E5>=10%, and E5<=30%. Then the IF function will perform the logical test, if it meets both criteria then it will return ‘Yes’ otherwise ‘No’.
Things to Remember
- In the case of using the functions, we must focus on the conditions properly. If the condition isn’t written correctly then the desired result won’t be found.
- Initially, after using a formula, you will get the result in numeric form. So, you have to convert the numeric values into percentage form manually.
- We suggest you download the Excel file and see it while using the formulas for better understanding.
Frequently Asked Questions
1. How do you use IF formula for percentages in Excel?
First, calculate the percentage then use it for criteria within the IF function, although you can directly insert the percentage formula within the IF function.
2. Can I use IF function for calculating percentages with multiple criteria?
By using nested IF functions you can calculate percentages with multiple criteria.
3. Can I use IF function with a percentage formula in Conditional Formatting?
Of course, you can insert the percentage formula with the IF function in the New Rule section of Conditional Formatting.
Conclusion
To sum up, the IF percentage formula in Excel is a useful feature for analyzing and interpreting data accurately. This technique enables users to determine specific conditions and compute percentages based on the results. Whether you are dealing with financial statements, sales data, or any other information that requires percentage calculations, mastering this approach can help enhance your productivity and save time.
We will be glad to know if you can execute the task in any other way. Follow the ExcelDemy website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you need clarification or need any help. We will try our best to solve the problem or work with your suggestions.
Related Articles
- Calculate Percentage Using Absolute Cell Reference in Excel (4 Methods)
- How to Calculate Average Percentage in Excel (3 Easy Methods)
- Use Excel Formula to Calculate Percentage of Grand Total
- How to Add 10 Percent to a Number in Excel (2 Easy Methods)
- Calculate Cumulative Percentage in Excel (6 Easy Methods)
- How to Calculate Total Percentage in Excel (5 Ways)
- Calculate Percentage in Excel VBA (Involving Macro, UDF, and UserForm)