SOLVED

# Divide a quantity as whole number among multiple cells

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

66 Replies

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

Hans, thank you very much for tweaking :)

# Re: Divide a quantity as whole number among multiple cells

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?

# Re: Divide a quantity as whole number among multiple cells

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?

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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.

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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...

Kind regards

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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

# Re: Divide a quantity as whole number among multiple cells

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?

# Re: Divide a quantity as whole number among multiple cells

hi,

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

thanks :)