Macro for splitting the number to a specific proportion

[jackyung]

Dear all,

Attached the excel. Columns A to D is the raw data while Columns F to I is the result I want.
I want to split the invoice number to three difference invoice number and the quantity split according to the proportion 5:3:2.
However, the quantity must be integer and the split total quantity need to match with the original quantity of each item.
In the example, if the decimal place is 0.5, I can't use round up or round down because it may not match with the original quantity.
Is there anyway to solve this issue?

Thanks
Jack

[Special-K]

Surely this may not be possible.

Split is 5:3:2, ie 50% 30% and 20%
If the total invoice is 100, the split would be 50, 30, 20 - thats fine.
If the total invoice is 150, the split would be 75, 45, 30 - thats fine.

If the total invoice is 163 the split should (mathematically) be 81.50, 48.90 and 32.60

But you say the result must be integers, then you'd have to take the integer amount or round up or down, which would, in all cases, change the split values 5:3:2 very slightly and unless you adjust the final figures properly they may not add up to the original total.

Who made these rules in the first place?

[jackyung]

Hi, thanks for your reply. I also think it is not possible in some case.

So I would like to change the split to 6:4 and it should be matched with the original quantity.

But how can I split the invoice to two (add A,B after the invoice number) according to the split quantity?

Attached the new excel. Thanks.

[Special-K]

It doesn;t matter what the split is - you're gonna get the same problem again.

Lets say the invoice is 100, with 6:4 the result would be 60 and 40 - both integers, together they add up to 100 - that's fine.
Or let's say the invoice is 240, with 6:4 the result would be 144 and 96 - both integers, together they add up to 240 - that's fine.
But if the invoice value is 113 that would be 67.8 and 45.2
And you're saying you want integers but but you want the sum of them to be exactly the total.
It's not gonna work with 113 (or a whole host of other numbers).

That's like saying
I'm gonna start with two numbers, 4 and 7.
I want to add them together.
I don't want the total to be 11.
But I'm not allowed to change them to anything else, they must stay at 4 and 7.
How can I make the total something other than 11 without changing either number.
You can't.

[KOKOSEK]

I don't understand idea of that but in the case of split for 2 values (6:4) you can ex. rounddown part 60% and roundup part 40%, then you will get two integers and also sum will match with original value.

[jackyung]

Hi KOKOSEK,

That's what I want to mention. 113 that would be 67.8 would become 68 and 45.2 would become 45 after rounding.

Hi Special-K

Now the major issue is how to split a invoice to two invoice with split value according to the format I want (Column F to I).

Thanks for both kindly helping

[KOKOSEK]

It is hard to really help if we don't understand the idea but I am happy that my advise could be helpful.

[jindon]

Simply

Please Login or Register  to view this content.

[jackyung]

It seems perfect! Thanks for all which helping in this thread. I really appreciate!