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.
How to Calculate Win-Loss Percentage in Excel: 8 Easy Steps
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.
- 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 the AutoFill Handle Tool.
Step 2: Enter logical_test Argument of IF Function
- To find the win-loss situation, apply the IF function with the following formula.
- Enter the logical_test argument as the value of cell E5 has to be positive.
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.
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.
- 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.
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.
- As we want to count the wins, our criteria argument is “W”.
- Insert the criteria argument with the following formula.
- Press Enter to see the wins. It will result in 4 as the number of wins is 4.
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.
- Then, press Enter and see the ratio result in 0.4.
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.
- As a result, it will show as 0.6 for the ratio of loss.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Stay with us & keep learning.
- How to Calculate Cumulative Percentage in Excel
- How to Calculate SLA Percentage in Excel
- How to Calculate Error Percentage in Excel
- How to Calculate Mean Percentage Error in Excel
- How to Calculate Remaining Shelf Life Percentage in Excel
- How to Calculate Percentage of Completion in Excel
- How to Calculate Percentage of Budget Spent in Excel
- How to Calculate Utilization Percentage in Excel
- How to Calculate Absenteeism Percentage in Excel
- How to Calculate Savings Percentage in Excel
- How to Calculate Productivity Percentage in Excel
- How to Calculate Variance Percentage in Excel
- How to Calculate Accuracy Percentage in Excel
- How to Calculate Grade Percentage in Excel