Forum Discussion
Divide as Whole Number and Allocate to Monthly Column
Yes - see the attached workbook.
Many thanks for your reply.
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?
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.
- Hi Peter,
Many thanks for the reply.
Unfortunately, I'm not running Excel 365, just standard Office/Excel 2016.See the attached demo, with a formula that should work in all versions of Excel.
- 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