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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
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.
 

Some videos you may like

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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
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
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
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
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
ASRS Generic Dual Mast Spares 2020_3_26.xlsx
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
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
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
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
Joined
Jan 23, 2020
Messages
28
Office Version
365
Platform
Windows
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?
 

Forum statistics

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

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top