New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to redistribute a remaining percentage

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

How to redistribute a remaining percentage

Postby draftdaddy » Tue Sep 01, 2015 3:18 pm

I'm trying to create a formula that will distribute X% proportionally to other %s in a table.

For example: I have 5 rows of %s that always add up to 100%.

1= 40%
2= 20%
3= 16%
4= 14%
5= 10%

If I remove 5 (10%) and want to distribute that 10% proportionally to 1-4, and still maintain 100% total; what does my formula look like? Initially I thought I could do:
1= (40%*10%) = 44%
2= (40%*10%) = 22%
3= (40%*10%) = 17.6%
4= (40%*10%) = 15.4%

Problem is this only adds up to 99%. What am I missing?

Thanks!

Kevin
  • 0

draftdaddy
Rookie
 
Posts: 14
Joined: Apr 20, 2015
Reputation: 0
Excel Version: 2010

Re: How to redistribute a remaining percentage

Postby ConneXionLost » Tue Sep 01, 2015 5:53 pm

Perhaps your formulas should be:

1= 40%/(100%-10%) = 44.4%
2= 20%/(100%-10%) = 22.2%
3= 16%/(100%-10%) = 17.8%
4= 14%/(100%-10%) = 15.6%

Put another way:

1= 40%/SUM(40%,20%,16%,14%,0%) = 44.4%
2= 20%/SUM(40%,20%,16%,14%,0%) = 22.2%
3= 16%/SUM(40%,20%,16%,14%,0%) = 17.8%
4= 14%/SUM(40%,20%,16%,14%,0%) = 15.6%

where the 0% is where the 10% was originally.

Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: How to redistribute a remaining percentage

Postby draftdaddy » Wed Sep 02, 2015 2:21 pm

Awesome, this works. Thank you!
  • 0

draftdaddy
Rookie
 
Posts: 14
Joined: Apr 20, 2015
Reputation: 0
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 33 guests

cron