Need to add multiple numbers that are reduced to 2 decimal points and thus don't match

[BobbyB0909]

OK, I have designed an inventory / accounting program for my work which tracks all purchases and categorizes them into different departments.

Some of the items, however, split across multiple categories (eg. celery purchases split 20% into chili, 25% into chowder, 50% into soup, 5% into misc. food), thus creating an amount of something like $12.155 into a particular category. Since I need to reduce the number to 2 decimal places for our accountant, that example would become $12.16. Therefore, when all the numbers for all the departments are added together, they do not match because of the odd penny.

I tried truncating the numbers, but I get a similar problem because they can now be LOWER by a penny or two. Then I attempted to add or subtract the difference from the original invoice total with the department breakdown, but that caused a circular reasoning error.

So what method could I utilize to deal with this issue? Any help would be truly appreciated.

[shg]

One way:

Row\Col	A	B	C	D
1			Total
2			$ 243.1000	C2: Input
3	Used In	Portion	Allocation
4	Chili	20%	$ 48.6200	C4: =IF(B4=0, 0, ROUND(B4 / (1 - SUM(B$3:B3)) * ($C$2 - SUM(C$3:C3)), 2))
5	Chowder	25%	$ 60.7800
6	Soup	50%	$ 121.5500
7	Misc	5%	$ 12.1500

[BobbyB0909]

That looks ingenious, but I don't understand it at all.

[shg]

In the first instance, it calculates 20%/100% of $243.10 and rounds the answer to the penny.

In the next, it calculates 25%/(100% - 20%) of ($243.10 - $48.62) and rounds the answer to the penny.

In the next, it calculates 50%/(100% - 20% - 25%) of ($243.10 - $48.62 - $60.78) and rounds the answer to the penny.

In the last, it calculates 5%/(100% - 20% - 25% - 50%) of ($243.10 - $48.62 - $60.78 - $121.55) and rounds the answer to the penny.

[BobbyB0909]

OK, I get it now. The only problem is it would require an extreme amount of work because of the way I have this "purchase breakdown" tab laid out currently.

I have many items which are broken down into smaller sub-categories--produce items, condiments, French fries & onion rings, etc--and they are all currently done so within the cell formula along with many other items. This would require lots of extra work. But I certainly appreciate the input...if it's the only way then I may need to do it. Thanks for your help.

[shg]

You're welcome.

[BobbyB0909]

I set up a new table to make this easier, but I'm still struggling with the formula.

Is that one formula you listed for the entire breakdown? And what does the B$3 refer to?

[shg]

Is that one formula you listed for the entire breakdown?

Yup.

Ad what does the B$3 refer to?

B3 is just a place to start for the sum. It's text, so SUM ignores it.

Post your actual workbook?

[BobbyB0909]

I got it!! Thanks again.

[shg]

You're welcome.