Bhubon Costa
In general, a rise in the percentage indicates a win, whereas a reduction in the percentage indicates a defeat. In financial analysis, we must determine if a project is in a win or a loss status. In this tutorial, we will show you how to calculate the win-loss percentage in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
8 Easy Steps to Calculate Win-Loss Percentage in Excel
We have supplied a data set reflecting the sales summary for 2 sequential periods in the figure below. We will utilize the data set to calculate the overall win-loss scenario of the transaction. To do this, we will use the IF, COUNTIF, and COUNTA functions.
Step 1: Calculate Percentage of Win-Loss for Each Entry in Excel
- To calculate the increase or decrease in percentage, firstly type the following formula.
=(D5-C5)/C5
- Press Enter to see the result.
- To convert into a percentage, click on the Percent Style from the Number tab.
- Therefore, the value in cell E5 will show in percentage.
- Apply the same formula in the following rows by using AutoFill Handle Tool.
Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
Step 2: Enter logical_test Argument of IF Function
- To find the win-loss situation, apply the IF function with the following formula.
=IF(E5>0- Enter the logical_test argument as the value of cell E5 has to be positive.
Read More: Percentage Formula in Excel (6 Examples)
Step 3: Insert Value_if_true Argument of IF Function
- For meeting the condition, enter the value_if_true
- Type “W” for the value_if_true argument with the following formula. It will show “W” for the positive percentages.
=IF(E5>0,"W",
Step 4: Type Value_if_false Argument of IF Function
- Type “L” for the value_if_false argument with the following formula. It will show “L” for the negative percentages.
=IF(E5>0,"W","L")
- Finally, press Enter and it will appear as “L” as the percentage in cell E5 is negative.
- Then, use the AutoFill Handle Tool to auto-fill the cells.
Similar Readings
- How to Calculate Reverse Percentage in Excel (4 Easy Examples)
- Apply Percentage Formula in Excel for Marksheet (7 Applications)
- How to Calculate Percentage in Excel Based on Cell Color (4 Methods)
- Add 20 Percent to a Price in Excel (2 Quick Methods)
- How to Calculate Monthly Growth Rate in Excel (2 Methods)
Step 5: Insert COUNTIF Function to Calculate the Number of Win in Win-Loss Percentage in Excel
- Firstly, to count the total wins in the data set, we will use the COUNTIF function.
- Select the range F5:F14 as the range argument of the COUNTIF function.
=(COUNTIF(F5:F14
- As we want to count the wins, our criteria argument is “W”.
- Insert the criteria argument with the following formula.
=(COUNTIF(F5:F14, “W”)
- Press Enter to see the wins. It will result in 4 as the number of wins is 4.
Read More: How to Calculate Weight Loss Percentage in Excel (5 Methods)
Step 6: Apply COUNTA Function to Calculate the Ratio of Wins
- Divide the number of wins by the total number by applying the following formula of the COUNTA function.
=(COUNTIF(F5:F14,"W"))/COUNTA(F5:F14)
- Then, press Enter and see the ratio result in 0.4.
Read More: How to Calculate Profit Percentage in Excel (3 Methods)
Step 7: Calculate Ratio of Loss
- Similar to the previous method, apply the same to count the ratio of loss by using the following formula.
=(COUNTIF(F5:F14,"L"))/COUNTA(F5:F14)- As a result, it will show as 0.6 for the ratio of loss.
Read More: How to Calculate Percentage Decrease in Excel (2 Methods)
Step 8: Calculate Final Win-Loss Percentage in Excel
- Finally, to convert the ratios into win-loss percentages, select the cells and click on the Percent Style.
- Therefore, you will obtain the final win-loss percentage as shown in the image below.
Read More: Excel formula to calculate percentage of grand total (4 Easy Ways)
Conclusion
To conclude, I hope this article has given you some useful information about how to calculate win-loss percentage in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us & keep learning.
Related Articles
- Calculate Excel percentage difference between two numbers (using formula)
- Excel Formula to Add Percentage Markup [with Calculator]
- How to calculate salary increase percentage in Excel [Free Template]
- How do you Calculate Percentage Increase or Decrease in Excel
- Formula to Calculate Discount Percentage in Excel
- How to Calculate Growth Percentage Formula in Excel
