We have values from different sources listed for certain periods. This listing indicates the contributions of each period of items over the period of time. It is always better to have those contributions presented in percentage format in contrast to plain data values. If you are curious to know how you can calculate the contribution percentages with a formula, this article may come in handy for you. In this article, we are going to demonstrate how you can calculate the contribution percentage with a formula in Excel with an elaborate explanation.
What Is the Contribution Percentage?
The Contribution Percentage is the proportion of the user’s contribution percentage amount to their compensation for the plan year, expressed as a percentage. Normally, the contribution is viewed as the value of each Henry. However, the overview of the whole process would be simpler if we presented the value of each cell with respect to its percentage of the total. This increases clarity a lot more.
How to Calculate Contribution Percentage with Formula in Excel: 3 Suitable Examples
Below, we have presented three separate examples to illustrate how you can estimate the contribution percentage with various formulas. To avoid any kind of compatibility issues, use Excel 365 edition.
1. Calculate the Contribution Percentage Based on Age Bands
In the dataset below, we will demonstrate how you can get the percentage contribution from each age range adult. We will use the SUM function for this purpose.
Steps
- In the dataset below, we got the people’s age band and their frequency.
- Then we need to evaluate the summation of the frequencies.
- To do this, select the cell C12 and enter the following formula in the cell:
=SUM(C5:C11)
- Then we need to calculate the percentages of each person in each range.
- To do this, select the cell D5 and enter the following formula:
=C5/$C$12
- Then drag the Fill Handle to cell D11.
- But the values are in number format and we want to convert them to Percentage.
- To do this, select the range of cells D5:D11 and then go to the Home tab > Number format.
- Then from the drop-down box, click on the Percentage.
- After selecting Percentage, the range of cells D5:D11 is something below.
2. Calculate Top 25% Contribution Percentage in Excel
In the dataset presented below, we have the product ID and their amount of contribution to the total sale. For the sake of better clarity, it’s better to present them in percentage-wise contribution compared to value-based contribution. In the below dataset, we will evaluate each of their contributions on a percentage basis, using the IF, PERCENTILE.INC and SUM function.
Steps
- We need to calculate the input data in the top 25 percent contributor of the total range.
- For this, select cell C19 and enter the following formula:
=SUM(C11:C18)
- Then to determine whether the sales values are in the top 25% or not, we enter the below formula in cell D4:
=IF(C5>=PERCENTILE.INC($C$5:$C$18,0.75),"In top 25%","")
- Then drag the Fill Handle to cell D18.
3. Calculate Monthly Contribution Percentage Based on Sales Data
Based on the monthly sales value we can calculate the contribution of the month to the total sales throughout the year. We are going to use the SUM function in order to get the contribution percentage
Steps
- To calculate the sales percentage of the total sales value
- We need to calculate the total sales value first, for this, enter the following formula in the cell C17:
=SUM(C5:C16)
- Then select cell D5 and then enter the following formula:
=C5/$C$17
- Then drag the Fill Handle to cell D16.
- But the values are in number format and we want to convert them to Percentage format.
- To do this, select the range of cells D5:D11 and then go to the Home tab > Number format.
- Then from the drop-down box, click on the Percentage.
- After selecting Percentage, the range of cells D5:D11 is something below.
How to Calculate Sales Contribution Percentage in Excel
In the below dataset, we have the dataset of the sales value of each region. The contribution here is actually presented with respect to the value amount. But for better clarity, it’s better to present them in percentage format instead of value format. We are going to use the SUM function in order to get the contribution percentage.
Steps
- We need to get the sales contributions of each region.
- For this, we need to get the total sales value of all the regions mentioned here.
- To do this, select cell C12 and enter the following formula:
=SUM(C5:C11)
Entering this formula will calculate the summation of region amount sales in the range of cells C5:C11.
- Now, we select cell D5 and enter the formula below:
=C5/$C$12
- Then drag the Fill Handle to cell D11.
- Doing this will fill the range of cell D5:D11 with the percentage contribution of each region.
- But the values are in number format and we want to convert them to Percentage format.
- To do this, select the range of cells D5:D11 and then go to the Home tab > Number format.
- Then from the drop-down box, click on the Percentage.
- After selecting Percentage, the range of cells D5:D11 is something below.
💬 Things to Remember
Always remember to switch the format of the cell values. The format can be in number format, so the value can seem bizarre. Switching to the percentage with two decimal places will be better.
Download Practice Workbook
Download this practice workbook below.
Conclusion
To sum it up, the issue of how we can calculate the contribution percentage with a formula in Excel with 3 examples. A workbook is available to download where you can practice these methods. Feel free to ask any questions or provide feedback through the comment section.
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
- IF Percentage 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
<< Go Back to Percentage Formula Examples | Calculating Percentages | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
