Forum Discussion

MHolmes007's avatar
MHolmes007
Copper Contributor
Nov 15, 2022
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...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 15, 2022

MHolmes007 

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.

 

MHolmes007's avatar
MHolmes007
Copper Contributor
Nov 16, 2022
Hi Peter,
Many thanks for the reply.
Unfortunately, I'm not running Excel 365, just standard Office/Excel 2016.
    • MHolmes007's avatar
      MHolmes007
      Copper Contributor
      Nov 16, 2022
      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

Resources