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-
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 the IF Function Via the 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: Make an Excel Spreadsheet Automatically Calculate Percentage
2. Using 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.
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 the 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 the IF function for calculating percentages with multiple criteria?
By using nested IF functions you can calculate percentages with multiple criteria.
3. Can I use the 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.
Download Practice Workbook
You can download the practice workbook from here and practice yourself.
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. 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
- Calculate Percentage in Excel VBA
- How to Calculate Percentage of Filled Cells in Excel
- How to Apply Percentage Formula for Multiple Cells in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Calculate Percentage for Multiple Rows in Excel
- How to Use Food Cost Percentage Formula in Excel
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- How to Calculate Variance Percentage in Excel