Formula Help

[swifttl]

Hi,

I need a formula that will show me 77% for first 6 months followed by 60% for the remaining 6 months then 60% 3 months later so it shows as below.

Any help would be much appreciated.

 

[Bosquedeguate]

Hi,

I need a formula that will show me 77% for first 6 months followed by 60% for the remaining 6 months then 60% 3 months later so it shows as below.

View attachment 95456

Any help would be much appreciated.

Are your percentages calculated from something? Can they possibly change?

 

[swifttl]

Hi,

At the moment they are fixed

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

23 07 18.xlsm
	E					J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	Month 1						Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24
3	Aug-23						77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%	0%
4	Sep-23						0%	77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%
swifttl

Cell Formulas
Range	Formula
K3:Z4	K3	=LOOKUP(IFERROR(DATEDIF($E3,K$2,"m")+1,0),{0,1,7,13,15,16},{0,0.77,0.6,0,0.6,0})

 

[swifttl]

Welcome to the MrExcel board!

Does this do what you want?

23 07 18.xlsm
	E					J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2	Month 1						Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24
3	Aug-23						77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%	0%
4	Sep-23						0%	77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%
swifttl

Cell Formulas
Range	Formula
K3:Z4	K3	=LOOKUP(IFERROR(DATEDIF($E3,K$2,"m")+1,0),{0,1,7,13,15,16},{0,0.77,0.6,0,0.6,0})

Hi,

Thanks for this I'll give it a go and see if it works

Much Appreciated

 

[Bosquedeguate]

Create a month Difference Table to lookup percentages

GetLastRow.xlsm
	A	B	C
33	Months		Pct
34	-99	0	0
35	1	6	0.770
36	7	12	0.600
37	13	14	0.000
38	15	17	0.600
39	18		0.000
Sheet2

Use the VLOOKUP function to get the desired PCT value based on the Month difference. Month Difference is calculated with a custom VBA function (MonthDiff).

GetLastRow.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
41	App Start		Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25
42	01-Aug-23		77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%	60%	60%	0%	0%	0%
43	01-Sep-23		0%	77%	77%	77%	77%	77%	77%	60%	60%	60%	60%	60%	60%	0%	0%	60%	60%	60%	0%	0%
Sheet2

Cell Formulas
Range	Formula
C42,C43:V43	C42	=VLOOKUP(monthdiff($A42,C$41),TableDatePct,3,TRUE)
D42:V42	D42	=VLOOKUP(monthdiff($A42,D41),TableDatePct,3,TRUE)

Named Ranges
Name	Refers To	Cells
TableDatePct	=Sheet2!$A$34:$C$39	C42:V43

VBA function
Function MonthDiff(dtRef As Date, dtStart As Date) As Integer
  Dim dt1, dt2
  dt1 = DateSerial(Year(dtRef), Month(dtRef), Day(dtRef))
  dt2 = DateSerial(Year(dtStart), Month(dtStart), Day(dtStart))
  
  MonthDiff = DateDiff("m", dt1, dt2) + 1
End Function