SOLVED

# Allocate integer values over specified months

Copper Contributor

# Allocate integer values over specified months

Hi all,

I have a table in Excel that has a start date and an end date. I need to allocate a unit value spread over the months between the two dates. However I can only have whole units only, and if there is a remainder value, it is allocated to the final month in the window. This is the table range between JAN-DEC, which is for 2022 only.

If there is nothing in the Revised Units column, the value taken is from Units, otherwise Revised Units takes precedence.

The Unit Month Window calculates the amount of months using DATEDIF. The below table is the ideal state I would like to end up with.

 Unit Start Unit End Units Revised Units Unit Month Window JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 01/01/2022 05/06/2022 100 5 20 20 20 20 20 04/03/2022 06/09/2022 82 6 14 14 14 14 14 12 02/05/2022 03/08/2022 32 34 3 12 12 10 01/12/2022 15/05/2023 15 5 3

I found this great thread regarding spreading values over multiple cells: https://techcommunity.microsoft.com/t5/excel/divide-a-quantity-as-whole-number-among-multiple-cells/...

but it doesn't quite answer the logic I'm looking for. I'm also keeping it in the table format as I would like to transfer the ability to a Sharepoint list using Calculated Columns, but Excel will do for now :)

I hope this makes sense!

2 Replies
best response confirmed by iali4920 (Copper Contributor)
Solution

# Re: Allocate integer values over specified months

See the attached example. I changed the values in F1:Q1 to dates.

# Re: Allocate integer values over specified months

@Hans Vogelaar this is absolutely perfect! I've been scratching my head for a week trying to figure this out! I've modified the formulas to work in a table, and stripped the EOMONTH as Sharepoint Lists doesn't support this functionality, (replaced with a DATE formula that adds a month and subtracts a day to get the end of the month).

Thank you for the speediest reply!