Formula help

[Kclynn]

I need the very next cell in the row to be the difference left between the sum of the previous cells in the row.

ISDProjectedGrossMargins2020_3_27.xlsx
	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
56	Recognize		Funded	%	Book	Mos.	Dec	Jan-20	Feb-20	Mar-20	Apr-20	May-20	Jun-20	Jul-20	Aug-20	Sep-20	Sep-20	Jan-00	Dec-20
57	200,000	0		30%	7/19	7
58		0	Y	330%		4
59	21,000	0		20%	6/19	4
60	128,000	128,000		99%	3/20	6		0	0	13,653	13,653	13,653	13,653	13,653	13,653	0	0	0	0
61	530,000	530,000		70%	4/20	7		0	0	0	48,457	48,457	48,457	48,457	48,457	48,457	48,457	0	0
62	350,000			35%	5/20	6		0	0	0	0	37,333	37,333	37,333	37,333	37,333	37,333	0	0
Projected Margins 11x17 (2)

Cell Formulas
Range	Formula
R60:AC62	R60	=IF(AND(R$56>=$O60,R$56<EDATE($O60,$P60)),(($K60/$P60)*0.64),0)
L57:L61	L57	=IF($C57<>"D",K57,0)

 

[jasonb75]

Next cell and Previous cells are a bit ambiguous, it might be obvious to you, but you will need to narrow it down with some more precise definitions before we will be able to provide you with an answer.

 

[Kclynn]

Next cell and Previous cells are a bit ambiguous, it might be obvious to you, but you will need to narrow it down with some more precise definitions before we will be able to provide you with an answer.

My apologies I will provide the best explanation that I can. So 64% of the amount in column "K" the "Recognize" amount is being allocated over the number of months shown in column "P" "Mos." with the start date being shown in the column "O" "Book" the next column i.e. if mos is 7 then column 8 would be the difference between column K and the sum of allocated cells shown in the row.

 

[jasonb75]

Unless I'm still missing something, I think that you just need

=K60-SUM(R60:AC60)

 

[Kclynn]

Unless I'm still missing something, I think that you just need

=K60-SUM(R60:AC60)

I need it to be incorporated with the formula "=IF(AND(T$56>=$O60,T$56<EDATE($O60,$P60)),(($K60/$P60)*0.64),0)" so that the formula works throughout

 

[jasonb75]

I think that I get it now, try this one in R60, then drag to fill the rest of the table.

=IF(AND(R$56>=$O60,R$56<EDATE($O60,$P60)),(($K60/$P60)*0.64),IF(AND(Q$56<EDATE($O60,$P60),R$56>=EDATE($O60,$P60)),$K60-SUM($Q60:Q60),0))

Note that the formula works on the assumption that column Q will be empty, there needs to be at least one empty column between 'mos' and the first formula.

 

[Kclynn]

I think that I get it now, try this one in R60, then drag to fill the rest of the table.

=IF(AND(R$56>=$O60,R$56<EDATE($O60,$P60)),(($K60/$P60)*0.64),IF(AND(Q$56<EDATE($O60,$P60),R$56>=EDATE($O60,$P60)),$K60-SUM($Q60:Q60),0))

Note that the formula works on the assumption that column Q will be empty, there needs to be at least one empty column between 'mos' and the first formula.

Yes, this is definitely doing what I wanted, for the most part, the one question I have is why does the difference always appear in column AC instead of the next cell adjacent. i.e. Row 60 difference $ should be where it is column Z, row 61 difference $ should be in column AA and row 61 difference $ should be in column AA. So very close to what I'm looking for.

 

[Kclynn]

Yes, this is definitely doing what I wanted, for the most part, the one question I have is why does the difference always appear in column AC instead of the next cell adjacent. i.e. Row 60 difference $ should be where it is column Z, row 61 difference $ should be in column AA and row 61 difference $ should be in column AA. So very close to what I'm looking for.

Never mind I found my error. I want to thank you so very much for all your help!! I'm not for great at this and truly appreciate the help. I just love this forum. Stay healthy and have a great day!!

 

[jasonb75]

Never mind I found my error.

Out of curiosity, what was the error? I've just been looking but couldn't see how it would happen the way you said.

 

[Kclynn]

Out of curiosity, what was the error? I've just been looking but couldn't see how it would happen the way you said.

My dates in row 56 AA and AB where incorrect