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

[Kclynn]

Hello,
I have figured out how to allocate the cost across the different months but I want the very next cell in the array to have a different calculation based sum of the array. I am sure that I am not explaining this well. Example: I want the number in a cell (say 200,000) to be allocated over duration indicated in a cell (duration 7) then the very next cell (8th month) to but the sum of the 7 months array subtracted from the cell holding the 200,000.

Here are my formulas:
=IF(COLUMNS($R68:R68)>$P68,"",($I68*0.64/$P68)) This is in the first 7 cells
=I68-(SUM(R68:V68)) this is in the 8 cell

Is there a way to combine this so it would be automatically calculated no matter what the duration number is.

 

[Fluff]

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.

 

[Kclynn]

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]

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]

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]

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]

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]

ASRS Generic Dual Mast Spares 2020_3_26.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
68	D	U	TP		Magna - HP, Move GM Build	PC	300,000			0	Y	20%	3/20	5				38,400	38,400	38,400	38,400	38,400	108,000
69	D	I	H		Raymond Handling - UltraBot	SC	200,000			200,000		10%	10/19	2											64,000	64,000	72,000
Projected Margins 11x17 (2)

Cell Formulas
Range	Formula
L68	L68	=IF($C68<>"D",K68,0)
Z68:AC68,T68:V68	T68	=IF(COLUMNS($R68:T68)>$P68,"",($I68*0.64/$P68))
Y68	Y68	=I68-(SUM(T68:X68))

 

[Kclynn]

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]

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]

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?