Divide a quantity as whole number among multiple cells

[Remya Ramakrishnan]

Hello,

Any suggestion for an excel formula to derive A2, B2, C2, D2 values.

Eg:

Category	Income	A	B	C	D
Sal1	11	3	3	3	2

if we divide 11 among 4 columns, 2.75 will result in the 4 cells

But I need the excel formula to get the whole number, next integer(3 for 2.75) in all cells.

Any response will be helpful. Thanks

[#2]

@Sergei Baklan 

I am very impressed by your formula and it may be able to solved a problem that I faced.  

I have a number (say 223 in A1) and I want to distribute it over a selected cells (say 20 rows from B1:B20) more or less evenly. The number in each cell must be a whole number and the sum of all rows must be exactly the same as A1. Is there a simple Excel formula to achieve this?

Please see my attached worksheet for details.  Do note that the number of row is variable.  

Thank you. 

[#3]

@Karyn_Tan 

See the attached version. You can change the values in A1 and F1 to see the result change.

[#4]

@Hans Vogelaar 

Wow, that’s fantastic.  The formula works nicely by inputting number of rows into an additional cell.  

I have an additional question.   In a real life worksheet with lots of data and without a blank column to key in number of rows due to worksheet protection, is there anyway to tweet the formula such that it knows how to count a range of selected rows?  You can try to imagine that after row 20, there is another number (say A21=333) and it needs to be redistributed to 15 rows (say B21 to B35).  Hence, I need a flexible formula that can take care of any number of rows.

Thanks a lot for your help.  It is really enlightening to learn from you. 

[#5]

@Karyn_Tan 

A formula doesn't "know" which range you selected. To do what you want would probably require VBA code, but that seems overkill.

Without VBA, see the attached version. I moved the cell with the number of rows to column A.

[#6]

Hans, thank you very much for tweaking :)

[#7]

@Hans Vogelaar 

Hi Hans, how would this formula need to be adjusted to only confine a particular start date and let it run so that the quantity is distributed but does not go into negative values if its not constrained by an end date?

[#8]

@DianneTS 

Hi Dianne,

This is a long discussion, I'm not sure what you are referring to. Could you attach a sample workbook with an indication of what you want?

[#9]

@Hans Vogelaar 

I would like to divide a whole number across multiple cells but using a maximum value (maybe referencing another column as this number could change between each row) and the last cell to calculate the remaining number if its below the maximum. Also, if this allocation could start from a chosen date? See below representation: 

*Note: is there a formula to leave a blank in remaining cells as opposed to zero?

Units	Capacity	Start Date	Finish Date	07/02/2022	14/02/2022	21/02/2022	28/02/2022	07/03/2022	14/03/2022	21/03/2022	28/03/2022	04/04/2022	11/04/2022
10	3	07-Feb	date after last allocation (J1)	3	3	3	1
10	4	28-Feb	L1					4	4	2
10	2	14-Feb	O1			2	2	2	2	2
20	3	21-Feb					3	3	3	3	3	3	2

[#10]

@DianneTS 

In the 3rd row, with Start Date 28/02/2022, why should the numbers start at 07/03/2022 instead of at 28/02/2022?

Similar question for the 4th and 5th rows.

[#11]

Its to create a production plan. So each row is a separate job and each job has different start dates according to capacity

[#12]

@DianneTS 

In the 2nd row, with start date 7 February, the first non-blank value is indeed on 7 February.

But in the 3rd row, with start date 28 February, the first non-blank value in your sample is on 7 March.

I don't understand the logic behind this.

[#13]

So each row is a 'project' consisting of a set amount of units. each project cannot start on the same day due to resources, hence the different start dates. Is there a formula to use to distribute units as per column B (Capacity) across cells from a nominated starting point i.e E2 or F2 to get the result as shown. So basically I need it to have 2 functions that are variable - capacity and start date.

[#14]

@DianneTS 

Why can't the project with start date 14 February start on 14 February? There is no other project that starts on 14 February.

[#15]

The production is order based. So there is no guarantee that it would start then. In my reality, project 1 could end up starting after project 3. Or a start date could change for a number of reasons, hence why I need the capacity and start date to be able to be amendable to account for any real life changes.

[#16]

@DianneTS 

I am very sorry, but I don't understand at all, so I cannot help you.

[#17]

@Hans Vogelaar 

hi guys :) interesting stuff but i also seem to stuck with one thing.

say that A1= number from other sheet i.e. 1190.6

how to break it down so it displays in cells below for equal amounts but last 2 parts to be equal as well?

1. 117.6

2. 117.6

3. 117.6

4. 117.6

5. 117.6

6. 117.6

7. 117.6

8. 117.6

9. 117.6

10. 66.1

11. 66.1

it's just how SAP system on site is configured and need to adjust my excel file... 

Any advice much appreciated :) 

Kind regards

[#18]

@JZJANIK 

What is the 'rule' to determine the sizes 117.6 and 66.1?

[#19]

@Hans Vogelaar 

Hi, thanks for prompt response

117.6 it's unit of measure in this case. 

66.1 are based only on example given (when you split 1190.6 for as many 117.6 as possible this will leave you with 132.2 thus 2x66.1). But if there'd be let's say 1200.6, it would change to 2x 71.1.

I know in theory since there's 132.2 so you could fit one more 117.6 in there, but SAP on site, for whatever reason is set up to split last one separately to 2 equal parts.  

Is that even doable? :) 

kind regards

[#20]

@JZJANIK 

Yes, that should be doable. One more question:

What if the total is evenly divisible by the unit of measure? For example, if the total is 1176.0. Do you want 10 times 117.6, or 9 times 117.6 plus 2 times 117.6/2 = 58.8?

[#21]

hi,

if total is divisible then this is fine and we would want 10x 117.6

thanks :)