SOLVED

# Divide as Whole Number and Allocate to Monthly Column

I have a sheet with start date, finish date and quantity, then have a formula that divides the quantity over the selected months and enters the result into the appropriate month column.

What I am struggling with, is that the result should be a whole number only (cannot be decimal).

So for example, Cust1, the quantity (formulated) would show 11 + 12 in the selected monthly columns, not 11.5 + 11.5.

My sheet resembles the following:

A                 B                 C               D              E               F               G               H

1     Name     Start Date    Finish Date     Qty        Jan 23       Feb 23      Mar 23      Apr 23

2     Cust1        Jan-23         Feb-23           23            11             12              -               -

3     Cust2        Feb-23        Apr-23            70             -               23            23             24

I require guidance on achieving a solution using excel formula, I cannot use VBA due to network restrictions.

8 Replies

# Re: Divide as Whole Number and Allocate to Monthly Column

The batch sizes can be calculated from

``= QUOTIENT(+Qty, 1+ DATEDIF(Start_Date, Finish_Date, "m"))``

or, if you are using 365,

``````= LET(
months,   1+ DATEDIF(Start_Date, Finish_Date, "m"),
batchQty, QUOTIENT(+Qty, months),
batchQty
)``````

might be more intelligible.  It remains to distribute the batches together with any final balance.

# Re: Divide as Whole Number and Allocate to Monthly Column

Based on:

Start Date: Feb-23

Finish Date: Apr-23

Qty: 70

Your formula has equated to 23. So that would be 23 in each of the months Feb, Mar, Apr, which is total 69.

The requested value is 70, so i'm missing 1.

Also, once i drag your formula across the range of months, it equates to 23 in every month, not just the selected months (Feb, Mar & Apr).

Could you provide further support to assist?

# Re: Divide as Whole Number and Allocate to Monthly Column

I could, but the problem is I am not sure that you are using Excel 365, since the technique of dragging across is a legacy technique rather than anything one would use in a dynamic array solution.

``````= LET(
months,      1 + DATEDIF(startDate, finishDate, "m"),
interval,   (period>=startDate)*(period<=finishDate),
finalPeriod, period=finishDate,
batchQty,    QUOTIENT(+qty, months),
balance,     MOD(+qty, months),
batchQty*interval + balance*finalPeriod
)`````` The danger is that, by replying, I put others off who may be better positioned to provide the solution you are looking for.

# Re: Divide as Whole Number and Allocate to Monthly Column

Hi Peter,
Unfortunately, I'm not running Excel 365, just standard Office/Excel 2016.

# Re: Divide as Whole Number and Allocate to Monthly Column

See the attached demo, with a formula that should work in all versions of Excel.

# Re: Divide as Whole Number and Allocate to Monthly Column

Hans Vogelaar,

That is fantastic, thank you.

Is there are way to reverse the increment?
To try and explain ....
In the monthly columns, Instead of 15 + 15 + 15 + 14 + 14 = 73
Can I get 14 + 14 + 15 + 15 + 15 = 73
best response confirmed by MHolmes007 (Copper Contributor)
Solution

# Re: Divide as Whole Number and Allocate to Monthly Column

Yes - see the attached workbook.

Hans Vogelaar,,