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.

 

[tyija1995]

Hey, I hope I am understanding this correctly,

Is this what you are after?

TestSheet
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
68	200000							7		18285.71	18285.71	18285.71	18285.71	18285.71	18285.71	18285.71	72000
69	250000							9		17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	90000
70	285000							4		45600	45600	45600	45600	102600
Sheet1

Cell Formulas
Range	Formula
R68:Y68,R70:V70,R69:AA69	R68	=IF(TRANSPOSE(ROW(INDIRECT("$1:$"&$P68+1)))<=$P68,($I68*0.64/$P68),$I68-$P68*($I68*0.64)/$P68)
Dynamic array formulas.

Also, wouldn't the final answer always be equal to 0.36*I68? As the first set of numbers are n-sized multiples of 64% (summing to 64%)

 

[Kclynn]

Hey, I hope I am understanding this correctly,

Is this what you are after?

TestSheet
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
68	200000							7		18285.71	18285.71	18285.71	18285.71	18285.71	18285.71	18285.71	72000
69	250000							9		17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	17777.78	90000
70	285000							4		45600	45600	45600	45600	102600
Sheet1

Cell Formulas
Range	Formula
R68:Y68,R70:V70,R69:AA69	R68	=IF(TRANSPOSE(ROW(INDIRECT("$1:$"&$P68+1)))<=$P68,($I68*0.64/$P68),$I68-$P68*($I68*0.64)/$P68)
Dynamic array formulas.

Also, wouldn't the final answer always be equal to 0.36*I68? As the first set of numbers are n-sized multiples of 64% (summing to 64%)

Holy cow!
Yes, I think you understood me, that's amazing in itself, I will try it. Thank you so much.

 

[Kclynn]

Holy cow!
Yes, I think you understood me, that's amazing in itself, I will try it. Thank you so much.

What you are showing is what I am after. I must be missing something I copy and paste the formula but it's not working for me. Gives me the same number all the way across. What am I misunderstanding?

 

[tyija1995]

Hard to tell without a visual aid, do you have the XL2BB add-in ? If so can you use that to show me exactly what you have done?

It should give the same number in all of the columns EXCEPT the last one which is essentially 36% of the original number

 

[Kclynn]

I'm sorry not that proficient in excel I don't know what XL2BB add-in is? But I will google it.

 

[Kclynn]

Here is my try with XL2BB

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
55	Status	Type	Salesman	Job	Account	Source	Sell	Total G.M.	Recognize	Funded	%	Book	Actual			Jan	Feb	Mar	Apr	May	June	July	Aug
56	Status	Type	Salesman	Job	Account	Source	Sell	Total G.M.	Recognize		Funded	%	Book	Mos.	Dec	Jan	Feb	Mar	Apr	May	June	July	Aug
68	D	U	TP		Magna - HP, Move GM Build	PC	300,000			0	Y	20%	10/19	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	7		28,571	28,571	28,571	28,571	28,571	28,571	28,571
Projected Margins 11x17 (2)

Cell Formulas
Range	Formula
L68	L68	=IF($C68<>"D",K68,0)
R68	R68	=IF(TRANSPOSE(ROW(INDIRECT("$1:$"&$P68+1)))<=$P68,($I68*0.64/$P68),$I68-$P68*($I68*0.64)/$P68)
X68:Y68,S68:V68	S68	=IF(COLUMNS($R68:S68)>$P68,"",($I68*0.64/$P68))
W68	W68	=I68-(SUM(R68:V68))
R69:Y69	R69	=IF(COLUMNS($R69:R69)>$P69,"",$I69/$P69)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q139:AV147,Q60:AV135	Expression	=$N60 >= 50%	text	NO
N60:N135,N137:N147	Cell Value	>=50	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E57:E139	List	BJ,TP,H,CS,RP,RS
H57:H69	List	K,M,AMH,PC,SC

 

[tyija1995]

Hmm, it seems as if you don't have dynamic array functions, can you see if you have access to a function called unique?

 

[Kclynn]

Hmm, it seems as if you don't have dynamic array functions, can you see if you have access to a function called unique?

I am not seeing a function called unique. Hmm what does that mean?

 

[tyija1995]

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.

So if you had months number = 7, would you then fill Jan-Jul with figures, and put the final formula in for August?