# Allocate Yearly Cost Across a Different # of Months for Each Record

#### Fluff

##### MrExcel MVP, Moderator
All 365 subscribers should have the dynamic arrays, except those on the semi-channel.
@Kclynn
If you are not on the semi annual channel, it would be worth checking that you have all updates installed.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Kclynn

##### New Member
I think the UNIQUE function is exclusive to O365 insider members currently and won't be released for a few months if I am correct.

I believe I have excel 365 in the office but due to the virus I am working at home on an older version. Just for my own knowledge if I have that function would the formula you provided work?

So if you had months number = 7, would you then fill Jan-Jul with figures, and put the final formula in for August?
No, unfortunately, the start month would be based on the book date.

#### Kclynn

##### New Member
I am not sure that I answer the question exactly. So yes, "if you had months number = 7, would you then fill Jan-Jul with figures, and put the final formula in for August?" but the start month would be based on the book date.

#### sanrv1f

##### MrExcel MVP
Couple of questions,
What is the max number of months an amount to be allotted? 12?
In your sample data, Which column in your data is Book date? Column O? If yes, then, why is your split filled from 'Jan' instead of from Oct?

#### Kclynn

##### New Member
Hi, I am not the creator of the document I am working on but I believe to start yes the number of months will be 12. Yes, the book date is in column O and if the dates started in the wrong month that was done in error. Should start with the month indicated in book date. Any advice is appreciated.

#### sanrv1f

##### MrExcel MVP
Can you re-do the post #7 manually, with the expected outcome i.e. months to be filled and months to be left blank and the month on which the balance need to be shown, so that I could write a formula with better understanding

#### Kclynn

##### New Member
ASRS Generic Dual Mast Spares 2020_3_26.xlsx
68DUTPMagna - HP, Move GM BuildPC300,0000Y20%3/20538,40038,40038,40038,40038,400108,000
69DIHRaymond Handling - UltraBotSC200,000200,00010%10/19264,00064,00072,000
Projected Margins 11x17 (2)
Cell Formulas
RangeFormula
L68L68=IF(\$C68<>"D",K68,0)
Z68:AC68,T68:V68T68=IF(COLUMNS(\$R68:T68)>\$P68,"",(\$I68*0.64/\$P68))
Y68Y68=I68-(SUM(T68:X68))

#### Kclynn

##### New Member
I hope that is what you are looking for. I think it needs to include 24 months. Rolling year to year would even be better. But I just need to get the formula figured. Thank you for trying to help me. Wish I was better at this.

#### sanrv1f

##### MrExcel MVP
Couple more questions,
where does this 0.64 comes from? does that mean always 64% of amount to be distributed over x number of months mentioned in column P?
if the above is true , the balance to be placed in the cell next to last month would always be 36% of column I?
If the above is not true and the amount to be distributed over 12 months, then the numbers in your above example are not correct

#### Kclynn

##### New Member
Couple more questions,
where does this 0.64 comes from? does that mean always 64% of amount to be distributed over x number of months mentioned in column P?
if the above is true , the balance to be placed in the cell next to last month would always be 36% of column I?
If the above is not true and the amount to be distributed over 12 months, then the numbers in your above example are not correct
This is would be correct.
where does this 0.64 comes from? does that mean always 64% of amount to be distributed over x number of months mentioned in column P?
if the above is true , the balance to be placed in the cell next to last month would always be 36% of column I?

1,089,515
Messages
5,408,729
Members
403,224
Latest member
rholmesa