Rubayed Razib Suprov
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.
Download Practice Workbook
Download this practice workbook below.
What Is 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. But the overview of the whole process would be simpler if we present the value of each cell with respect to its percentage of the total. This increases clarity a lot more.
3 Suitable Examples to Estimate Contribution Percentage with Formula in Excel
Below, we have presented three separate examples to illustrate how you can estimate the contribution percentage with various formulas. For avoiding any kind of compatibility issues, use Excel 365 edition.
1. Calculate 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 people of each range.
- To do this, select the cell D5 and enter the following formula:
=C5/$C$12
- And 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.
Read More: How to Apply Percentage Formula for Multiple Cells in Excel (5 Methods)
2. Calculate Top 25% Contribution Percentage in Excel
In the dataset presented below, we have the product Id and their amount of contribution in 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 the cell D4:
=IF(C5>=PERCENTILE.INC($C$5:$C$18,0.75),"In top 25%","")
- Then drag the Fill Handle to cell D18.
Read More: How to Calculate Percentage for Multiple Rows in Excel
Similar Readings
- How to Calculate Percentage of Percentage in Excel (2 Examples)
- Calculate Percentage in Excel Based on Cell Color (4 Methods)
- How to Calculate Attendance Percentage in Excel (with Steps)
- Calculate Monthly Growth Rate in Excel (2 Methods)
- How to Calculate Percentage of Budget Spent in Excel
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.
Read More: How to Calculate Percentage of Sales in Excel (4 Examples)
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.
Read More: How to Calculate Sales Growth Percentage in Excel (3 Easy Approaches)
💬 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.
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. Any suggestion for the betterment of the Exceldemy community will be highly appreciable
Related Articles
- How to Calculate Average Percentage in Excel (3 Easy Methods)
- Calculate Percentage of a Number in Excel (5 Easy Ways)
- How to Calculate Minus Percentage in Excel (2 Methods)
- Percentage Formula in Excel (6 Examples)
- Calculate Percentage Difference Between Two Numbers in Excel
- Show One Number as a Percentage of Another in Excel (2 Ways)
- How to Add 10 Percent to a Number in Excel (2 Easy Methods)
