The dreaded rounding problem regarding dollars and cents.

Hello, I am new to this Forum and am desperate. I have a billing spreadsheet where one invoice has the potential of being paid for by up to 6 clients. The amount of each client's invoice is calculated via formula for their assigned percentage responsibility. I find when adding each clients calculated invoice totals, sometimes they total a few cents more than the actual total of the original invoice (clear as mud). See my attached spreadsheet as an example. I populate the Invoice Total column with the total amount found on the invoice generated from my billing program and let the formulas in the spreadsheet calculate each client's assigned responsibility. I have highlighted in yellow an example of a variance. Any suggestion is appreciated.

That is where my spreadsheet comes into play. One supplier may be responsible for only 30% of the invoice and the other two suppliers may be responsible for 50% and 20%. So I have the spreadsheet where I enter the total amount of the invoice and let the spreadsheet do the percentage calculations.

Surely you should invoice them separately, no?


Sent from my iPad using Tapatalk

Unfortunately it is not as simple as it sounds. The billing software I use does not meet the requirements dictated by the suppliers. They all need to see the "Master Invoice" but want their specific percentage breakdown. I could bill separate invoices but then do not have the ability to have one master invoice. So I am working with what I have.

Are they suppliers or customers? I got a bit confused on that... If you are invoicing them then I guess they are customers, but when you talk about percentage responsibility it sounds like they are suppliers, in which case they would be invoicing you.
Either way, why don't you do it the other way round and have separate invoices on your system and make a summary (master) invoice in excel?


Sent from my iPad using Tapatalk

I have explored that option at great length and is not possible at this time. Thanks for taking an interest.

OK your Lordship... I'll take a look and get back to you...

If I understand it correctly, @shg solution will always distribute the error to the last participant.

Is the actual quote...

But anyway, the @shg solution is way more mathematically elegant and takes a bit of work to understand how it works.
It seems like your calculating a corrected total which is (the total) times (the actual remaining total) divided by (the remaining total calculated by the remaining percentages)
Where "the remaining" means the ones to the right of the value being calculated.
Which is quite brilliant.
Is that right?

Ok Guy! Let me digest what your saying and I will get back to you.

To answer coolblue, basically I am providing a service to a customer and billing for that service. And it is 6 separate entities/individuals that split the cost of paying for that service billed on a invoice.

Thanks again!

SHG and Coolblue,
Thank you for the support. I reviewed the spreadsheets provided and was able to replicate the formulas to suit my purposes on a much larger scale than the example provided. Thank you. I have to admit I do not fully understand portions of the formula but I will attempt to figure that out this weekend.

Another question. Is there a way to turn off the automatic reallocation once I am finished? I have placed filters and subtotals to my spreadsheet because there will be times when I will go back and filter for groups of specific cases and or dollar amounts etc. But it appears every time I make a change to a cell or use a filter, the random reallocation kicks in and changes the allocations ever so slightly.

If I change of filter the spreadsheet, the data in Column O will change and reallocate.

Firstly I would repeat that shg's solution is far more elegant than mine, although I don't fully understand it. I'm sure that shg would not have offered it if there was any bias in the allocation, so I assume there is not. Using his solution I don't think there is any need to freeze the formlae.
Of course its best for him to answer these points!

To answer your question about the sheet you uploaded, there are 2 ways to do it.
1. Select the formulas in the right-most section, for the row that you want to freeze and copy then paste special, Values. (CONTROL-C followed by ALT-E then S then V then ENTER).
Rounding Example.png
2. Do the same thing for the Distribute to column in the third group of columns from the right.
Rounding Example 2.png
This will replace the formulas in that section with the calculated values hence freezing them.
The second method is easier but potentially confusing since the results will be inconsistent with the formulae to the left.

Hey Guys! Thanks for all your help. It was easier than I first thought. I need to stop working on this at the end of the day. I set the spreadsheet to manual calculation and will hit the calc button when I am ready. I can then filter all I want without any changes. Thanks again for your expertise.