IF dates

[BrutalLogiC]

Hey I'm struggling with a formula for cells in yellow to calculate the monthly values for a contract which keeps changing its value. Contract started 30-Jan-24 with an annualised value of 15,000 then this annualised value was replaced/updated 3 times before the contract came to an end on 2-Aug-24.

H6:S6 in grey are just my manual calculations to to show what values should be.

For months where there is no variation change in value I am expecting the result to be the latest annualised value divided by 12, e.g. for April the value should 1/12th of variation 2 annualised value.

For months where there is a change in value (March and June) or a partial month (e.g. January and August where there are only 2 days) the value should be calculated based on the corresponding annualised value divided by 365.

monthly value formula.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2	Contract phase	Start date of contract phase	Annualised value	Daily value		Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Total
3	Start	30-Jan-24	15,000	41														0
4	Variation 1	1-Feb-24	12,000	33
5	Variation 2	5-Mar-24	16,000	44		Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Total
6	Variation 3	28-Jun-24	20,000	55	Values should be:	82	1,000	1,315	1,333	1,333	1,348	1,667	110					8,188
7
8	End date	2-Aug-24
Sheet2

Cell Formulas
Range	Formula
T3,T6	T3	=SUM(H3:S3)
F3:F6	F3	=E3/365
H6	H6	=E3/365*2
I6	I6	=E4/12
J6	J6	=(E5/365*27)+(E4/365*4)
K6	K6	=E5/12
L6	L6	=E5/12
M6	M6	=E5/365*27+E6/365*3
N6	N6	=E6/12
O6	O6	=E6/365*2

 

[hagia_sofia]

Hello, the following is an attempt to address the question - not very elegant but hopefully will do the trick:

=LET(
a,D3:D6,
b,VSTACK(DROP(a,1),D8+1),
c,H2:S2,
d,F3:F6,
e,E3:E6,
seq,SEQUENCE(ROWS(a)),
days,EOMONTH(+c,0)-c+1,
f,DROP(REDUCE("",seq,LAMBDA(x,y,VSTACK(x,BYCOL(c,LAMBDA(z,MAX(MIN(EOMONTH(z,0),INDEX(b,y,0)-1)-MAX(z,INDEX(a,y,0))+1,0)))))),1),
BYCOL(IF(days=f,e/12,f*e/365),LAMBDA(x,SUM(x))))

 

[Tetra201]

Another approach:

=LET(e,$D$8,v,$D$3:$D$8,r,$D$3:$F$8,a,H2,b,EOMONTH(a,0),c,IFERROR(LOOKUP(a,v),e),d,LOOKUP(b,v),
IF(AND(a>=c,b>=d,c=d),VLOOKUP(d,r,2,0)/12,((d=e)+d-a)*VLOOKUP(c,r,3,0)+(b-d+1)*VLOOKUP(d,r,3,0)))

Prerequisites: there should be no more than 1 variation per month; Annualized value and Daily value cells for the End date should be empty or set to 0.

 

[BrutalLogiC]

Hello, the following is an attempt to address the question - not very elegant but hopefully will do the trick:

=LET(
a,D3:D6,
b,VSTACK(DROP(a,1),D8+1),
c,H2:S2,
d,F3:F6,
e,E3:E6,
seq,SEQUENCE(ROWS(a)),
days,EOMONTH(+c,0)-c+1,
f,DROP(REDUCE("",seq,LAMBDA(x,y,VSTACK(x,BYCOL(c,LAMBDA(z,MAX(MIN(EOMONTH(z,0),INDEX(b,y,0)-1)-MAX(z,INDEX(a,y,0))+1,0)))))),1),
BYCOL(IF(days=f,e/12,f*e/365),LAMBDA(x,SUM(x))))

thanks for your reply, unfortunately I get a Spill error when trying this!

 

[BrutalLogiC]

Another approach:

=LET(e,$D$8,v,$D$3:$D$8,r,$D$3:$F$8,a,H2,b,EOMONTH(a,0),c,IFERROR(LOOKUP(a,v),e),d,LOOKUP(b,v),
IF(AND(a>=c,b>=d,c=d),VLOOKUP(d,r,2,0)/12,((d=e)+d-a)*VLOOKUP(c,r,3,0)+(b-d+1)*VLOOKUP(d,r,3,0)))

Prerequisites: there should be no more than 1 variation per month; Annualized value and Daily value cells for the End date should be empty or set to 0.

many thanks this works, is it possible to adapt the formula to suit the data arranged in columns? e.g. to fit the below arrangement

monthly value formula.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
13		Start			Variation 1			Variation 2			Variation 3
14	Reference	Start	Annualised value	Daily value	V1 start	Annualised value	Daily value	V2 start	Annualised value	Daily value	V3 start	Annualised value	Daily value	End date
15	Contract A	30-Jan-24	15,000	41	1-Feb-24	12,000	33	5-Mar-24	16,000	44	28-Jun-24	20,000	55	1-Dec-24
16	Contract B	2-Jan-22	12,000	33										1-Feb-24
17
18
19		Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Total
20	Contract A													0
21	Contract B													0
22
23	Contract A should be:	82	1,000	1,315	1,333	1,333	1,348	1,667	110					8,188
24	Contract B should be:	1,000	33											1,033
Hori

Cell Formulas
Range	Formula
H15,E15:E16,N15,K15	H15	=G15/365
O20:O21,O23:O24	O20	=SUM(C20:N20)
C23	C23	=E15*2
D23,G23	D23	=G15/12
E23	E23	=H15*4+K15*27
F23,I23	F23	=J15/12
H23	H23	=K15*27+N15*3
J23	J23	=N15*2
C24	C24	=D16/12
D24	D24	=E16*1

 

[Tetra201]

See if the following formula works for your new data layout:

=LET(s,{1,4,7,10,13},x,$C15:$Q15,e,$O15,v,INDEX(x,s),y,INDEX(x,s+1),z,INDEX(x,s+2),a,C$19,b,EOMONTH(a,0),c,IFERROR(LOOKUP(a,v),e),d,LOOKUP(b,v),
IF(AND(a>=c,b>=d,c=d),LOOKUP(d,v,y)/12+(a=e)*LOOKUP(EDATE(e,-1),v,z),((d=e)+d-a)*LOOKUP(c,v,z)+(b-d+1)*LOOKUP(d,v,z)))

 

[BrutalLogiC]

hi Tetra, thanks for helping with this. where it refers to $C15:$Q15, should that be $C15:$N15?

 

[BrutalLogiC]

hi @Tetra201 I'm not sure why it's $C15:$Q15 but it works...! will I be able to amend the formula to account for more variations?

when amending dates I come across some problems which I can't figure out how to fix, e.g.
1. contract B start date change from 2-Jan-2022 to 2-Jan-2024 it produces N/A errors for Feb-24 to Dec-24
2. contrct start date 1-Jan-2024 and end 31-Dec-24 seems to produce wrong value for Dec-24

monthly value formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
13			Start			Variation 1			Variation 2			Variation 3
14		Reference	Start	Annualised value	Daily value	V1 start	Annualised value	Daily value	V2 start	Annualised value	Daily value	V3 start	Annualised value	Daily value	End date
15		Contract A	30-Jan-24	15,000	41	1-Feb-24	12,000	33	5-Mar-24	16,000	44	28-Jun-24	20,000	55	1-Dec-24
16		Contract B	2-Jan-24	12,000	33										1-Feb-24
17		Contract C	1-Jan-24	12,000	33										31-Dec-24
18
19			Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Total
20		Contract A	82	1,000	1,315	1,333	1,333	1,348	1,667	1,667	1,667	1,667	1,667	55	14,800
21		Contract B	986	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
22		Contract C	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,019	12,019
23
24		Contract A should be:	82	1,000	1,315	1,333	1,333	1,348	1,667	1,667	1,667	1,667	1,667	55	14,800	manual calc
25		Contract B should be:	986	33											1,019	manual calc
26		Contract C should be:	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	1,000	12,000	manual calc
27
28		A - delta	0	0	0	0	0	0	0	0	0	0	0	0	0	should be nil
29		B - delta	0	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	should be nil
30		C - delta	0	0	0	0	0	0	0	0	0	0	0	19	19	should be nil
Hori

Cell Formulas
Range	Formula
H15,E15:E17,N15,K15	H15	=G15/365
C20:N22	C20	=LET(s,{1,4,7,10,13},x,$C15:$Q15,e,$O15,v,INDEX(x,s),y,INDEX(x,s+1),z,INDEX(x,s+2),a,C$19,b,EOMONTH(a,0),c,IFERROR(LOOKUP(a,v),e),d,LOOKUP(b,v), IF(AND(a>=c,b>=d,c=d),LOOKUP(d,v,y)/12+(a=e)*LOOKUP(EDATE(e,-1),v,z),((d=e)+d-a)*LOOKUP(c,v,z)+(b-d+1)*LOOKUP(d,v,z)))
O20:O22,O28:O30,O24:O26	O20	=SUM(C20:N20)
C24	C24	=E15*2
D24,G24	D24	=G15/12
E24	E24	=H15*4+K15*27
F24	F24	=J15/12
H24	H24	=K15*27+N15*3
I24:M24	I24	=$M$15/12
N24	N24	=N15*1
C25	C25	=30*E16
D25	D25	=E16*1
C26:N26	C26	=$D$17/12
C28:N30	C28	=C20-C24

 

[Tetra201]

The following revamped formula seems to address the issues:

=LET(s,{1,4,7,10},cr,$C15:$N15,cx,INDEX(cr,s),cy,INDEX(cr,s+1),cz,INDEX(cr,s+2),cs,$C15,ce,$O15,
ms,MAX(cs,C$19),me,MIN(ce,EOMONTH(C$19,0)),xs,XLOOKUP(ms,cx,cx,,-1),xe,XLOOKUP(me,cx,cx,,-1),
IF(AND(DAY(ms)=1,EOMONTH(ms,0)=me,xs=xe),XLOOKUP(xs,cx,cy,,-1)/12,IF(ms>me,,(xe-ms)*XLOOKUP(xs,cx,cz,,-1)+(me-xe+1)*XLOOKUP(xe,cx,cz,,-1))))

 

[BrutalLogiC]

Amazing work thanks so much, tried it in a few examples and works great.