Rounding numbers to whole number need equal original

[Papeters111]

Hello,

Packing to ship 11 cases and the breakdown of boxes is red 20%, assorted 68% and 13% rainbow.

Total Cases Ordered 11.00
Red 2.20
Assorted 7.43
Rainbow 1.38
Special Order 0.00

We can not ship decimal boxes, It has to be a whole number.
Since all the decimals are less than .5 they all get rounded down and you got a total of 10 cases showing. 2 red, 7 assorted, 1 rainbow =10

(all above .5 will round up for 13 total)

I need the three categories red, assorted, and rainbow to be whole numbers and always equal my total cases ordered.

Thanks,

Paul 3

[Papeters111]

It does not really matter which one gets rounded up but preferably the one with the highest decimal, in this case assorted.

[FlameRetired]

I haven't tested this extensively, but with the data you submitted:
With numbers in column B this formula in column C.

Please Login or Register  to view this content.

Then this formula for the total:

Please Login or Register  to view this content.

I don't know how it will work for the rest of your data.

[Papeters111]

Tried it and no luck but I see what the thought was there.

[MrShorty]

I would probably do something like:

assorted=ROUNDUP(total*0.68,0)
red=ROUNDDOWN(total*0.20,0)
rainbow=total-assorted-red

[Papeters111]

Works, will use this.

Thanks,

P3

[shg]

Another way (two examples):

Row\Col	A	B	C	D
1			Total
2			11	C2: =ROUND(SUM(B4:B6), 0)
3		Ordered	Ship
4	Red	2.20	2	C4: =IF(B4=0, 0, ROUND(B4 * (C$2 - SUM(C$3:C3) ) / ($C$2 - SUM(B$3:B3)), 0) )
5	Assorted	7.43	8
6	Rainbow	1.38	1
7
8
9			Total
10			13	C10: =ROUND(SUM(B12:B14), 0)
11		Ordered	Ship
12	Red	1.28	1	C12: =IF(B12=0, 0, ROUND(B12 * (C$10 - SUM(C$11:C11) ) / ($C$10 - SUM(B$11:B11)), 0) )
13	Assorted	1.51	2
14	Rainbow	9.76	10