Making Small Amounts Equal 100%

Question

I have a dilemma; our largest client left and we have to figure out how the rest of the clients will get paid. It's regarding a number of portfolios of debt, and they have 3-5 members. This example is for the following ownership for 4 owners

47.30%

21.08%

21.08%

10.54%

100%

With the 47.30% member gone, how do I equally break up a $1,000 payment to make the other 3 equal to 100%?

Thanks in advance for any help.

Answer 1

I was able to duplicate your result. I am assuming that your workbook is set to "Set precision as displayed". That means that if you show the Adjusted Percent with no decimals and Excel rounds those to just the 24, 60, and 17, Excel will add exactly those rounded numbers instead of considering that they were not originally exact. There are two options to correct it.

1. Go to File>Options>Advanced and scroll down to When Calculating This Workbook. Uncheck the Set Precision as Displayed. Excel will then add the actual values including decimals even if not displayed, not the rounded values.

2. Change the format for the percentages in Column B to show at least 2 decimal places. Even with the precision as displayed this will correct the issue in this instance. Since we are dealing with the question of rounding, however, it is still possible that there would be a particular combination that might still come up a point higher or lower.

Unchecking the Set Precision as Displayed is probably the best option unless you need it that way for other operations in the workbook. Then just make sure that there are sufficient decimal places showing in the Adjusted Percent to prevent it from creating an inaccurate calculation.

Answer 2

Thank you so much Rich, this entire community is so helpful with my excel questions!!!

Answer 3

I think this is what you are looking for. Use this formula entered in B2 and filled down. Row 6 shows the new total percent.

=1/SUM($A$2:$A$4)*A2

Use the new Adjusted Percent to multiply times the $1,000 to break it up between the three clients that are left.

Answer 4

Oops, I did not pick that up when I created it, did the edit now, thanks for pointing that out.

Answer 5

I can't tell you how to do it but I can tell you that it can't be done in Microsoft Word for Mac. The program is a word processor which has limited calculation capabilities.

You'd have better luck in one of the Mac Excel Communities.