Splitting a volume between cells without fractions/decimals

[xDav3yRox]

Hi,

I am looking to split a volume between multiple cells but keeping whole numbers.
For example, I need to split 10 units across 3 cells. But rather than 3.3, i would like it to split this to be 4, 3, 3

Any help would be appreciated if this is even possible!

 

[Joe4]

Welcome to the Board!

Let's say that your value of 10 is in cell A1.
And you want your values to be in cells B1, C1, and D1.

Put this formula in cells C1 and D1:

=ROUND($A1/3,0)

And then put this formula in cell B1:

=A1-SUM(C1:D1)

 

[mikerickson]

If you have the sum that you want in A1 and the target cells are C1:F1, you could put

=INT($A1/3)+(MOD($A$1,3)>=COLUMNS($C1:C1)) in C1 and drag right

If A1 = 11, this will give 4,4,3.

 

[xDav3yRox]

That works great thanks, however i may require a slightly more complex formula now...

So the purpose of this sheet, is to look at sales volumes, and then amend these based on sales/returns.
The file is required to work out the WEEKLY volume, and then split this across the count of the days on the customer profile (varies based on customer).

I am currently using this to work out the weekly volume
=SUMIFS([Delivered Quantity],[SALES ACCOUNT],B9,[DELIVERY ACCOUNT],C9,[PRODUCT],D9)

It the does a calculation based on the returns, and then uses the below formula to split the volume
=IF([@COUNT]>0,[@[Profile Quantity]],IF([@[Delivered Quantity]]=0,[@[Profile Quantity]],L9/COUNTIFS([SALES ACCOUNT],B9,[DELIVERY ACCOUNT],C9,[PRODUCT],D9,[Delivered Quantity],">0")))

This currently leaves me with (for example) 11 units split over 3 days, meaning the file presents me with the volume of 3.6666. Ideally I need this to show (in this example) 4, 4, 3 (over three days, days can vary)

 

[xDav3yRox]

If you have the sum that you want in A1 and the target cells are C1:F1, you could put

=INT($A1/3)+(MOD($A$1,3)>=COLUMNS($C1:C1)) in C1 and drag right

If A1 = 11, this will give 4,4,3.

Thanks for this!

Do you know if it is possible to do this down a column, rather across rows?
The below is similar to what I require it for, where there is another column which the divisor takes the quantity from (COUNTIFS).
While your solution works wonders going across a row, i need to try and work this to go down a column, and give the result in the right hand column of the below.

Reference 1	Reference Two	Volume	Split
A	1	15	8
A	1	15	7
A	2	6	6
B	1	14	5
B	1	14	5
B	1	14	4

 

[mikerickson]

Change COLUMNSS($C1:C1) to ROWS(C$1:C1) and use COUNTIFS to get the equivalent of 3.

 

[xDav3yRox]

Change COLUMNSS($C1:C1) to ROWS(C$1:C1) and use COUNTIFS to get the equivalent of 3.

Perfect thanks mike!

Now to work out how to amend the rows part so it only looks at the rows based on the reference 1/2 combination, rather than all of the rows in the column.

 

[mikerickson]

=COUNTIFS(A:A, A1, B:B, B1) will give you the number of rows that go with row 1.

=SUMIFS(C:C, A:A, A1, B:B, B1) will give you the sum for that combination

=INT(SUMIFS(C:C, A:A, A1, B:B, B1) / COUNTIFS(A:A, A1, B:B, B1))+(MOD(SUMIFS(C:C, A:A, A1, B:B, B1), COUNTIFS(A:A, A1, B:B, B1))>=COLUMNS($C1:C1))

 

[xDav3yRox]

=COUNTIFS(A:A, A1, B:B, B1) will give you the number of rows that go with row 1.

=SUMIFS(C:C, A:A, A1, B:B, B1) will give you the sum for that combination

=INT(SUMIFS(C:C, A:A, A1, B:B, B1) / COUNTIFS(A:A, A1, B:B, B1))+(MOD(SUMIFS(C:C, A:A, A1, B:B, B1), COUNTIFS(A:A, A1, B:B, B1))>=COLUMNS($C1:C1))

Hi Mike,

using the above you suggested, it gives the split at the end, and i believe this is due to the fact it takes a sum of the volume (for Reference A1 for example, takes the two 15s (30))

Reference 1	Reference Two	Volume	Split
A	1	15	15
A	1	15	15
A	2	6	6
B	1	20	20
B	1	20	20
B	1	20	20

The formula you helped with previously below (S2 refers to the count of days) works perfectly, however with the ROWS function at the end, it reads from the top of the table down,
=INT($C2/S2)+(MOD($C2,S2)>=ROWS(D$2:D2))

It is fine for the first entry A1, and A2 only has one line. but without amending the ROWS part to read from row D5 downwards (for the first B1 reference) it takes into consideration the above rows.
Do you know a way to solve this last part easily, or if it is manageable by vba?

 

[mikerickson]

Try = COUNTIFS($A$1:$A2, A2, $B$1:$B2, B2) instead of the ROWS()