Extracting monthly information from changing yearly information

[not an excel geek]

Hi there,

I have information in an annual format that may change.

	Year 1	Year 2	Year 3	Year 4	Year 5
Option 1	20%	40%	60%	80%	100%
Option 2	5%	25%	70%	99%	100%
Option 3	1%	2%	3%	100%	100%

I want to to allow the table above to be an assumption that can change at any time. EG: this may be sales penetration where we may change views over time.
I then want to take the assumption table above, and put this into a dynamic monthly view. EG: there may be two cities. Each City may have different amounts of options 1, 2 and 3. However year one for City A may be 2020, year one for City B may be 2025 and so on. I have been able to build all of the above.

The issue I am facing is how do I get the different between the years, eg: I want to show the information monthly, not annually. Using Option 2 as the example, in the first year I want to be able to use 5%/12 (for year one), for year two I want ((25%-5%)/12)+5%. However if you do this formula you don't know if your in month 1 or month 12 of the year.

I have used Index match to do the formula and done ((25%-5%)/12)+prior month. The issue is I am always out by the first year. Keen to hear any suggestions on:
- Any ideas on other formula's that may help to short cut blending between the years to get monthly information (happy for this to be 1/12th).
- Any ideas on what may be causing the issue of the first year not being there, (note: the formula kicks in on the correct date)

Thank you!!!

 

[BSALV]

not excel geek

in row 8 you choose the options in row 9 the startmonth

Not_Excel_geek.xlsx
	A	B	C	D	E	F	G	H
1	ave information in an annual format that may change.
2		0	12	24	36	48	60	1000
3			Year 1	Year 2	Year 3	Year 4	Year 5
4	Option 1	0%	20%	40%	60%	80%	100%	100%
5	Option 2	0%	5%	25%	70%	99%	100%	100%
6	Option 3	0%	1%	2%	3%	100%	100%	100%
7
8		option	1	1
9		startmonth	12	25
10	month	index	shop A	Shop B
11	jan/20	0	-	-
12	feb/20	1	-	-
13	mrt/20	2	-	-
14	apr/20	3	-	-
15	mei/20	4	-	-
16	jun/20	5	-	-
17	jul/20	6	-	-
18	aug/20	7	-	-
19	sep/20	8	-	-
20	okt/20	9	-	-
21	nov/20	10	-	-
22	dec/20	11	-	-
23	jan/21	12	0,00%	-
24	feb/21	13	1,67%	-
25	mrt/21	14	3,33%	-
26	apr/21	15	5,00%	-
27	mei/21	16	6,67%	-
28	jun/21	17	8,33%	-
29	jul/21	18	10,00%	-
Blad1

Cell Formulas
Range	Formula
B2:G2	B2	=SEQUENCE(,6,0,12)
B11:B29	B11	=DATEDIF($A$11,A11,"m")
C11:C29	C11	=IF([@index]>=C$9,FORECAST([@index]-C$9,OFFSET($A$3,C$8,MATCH([@index]-C$9,$B$2:$H$2,1),,2),OFFSET($A$3,-1,MATCH([@index]-C$9,$B$2:$H$2,1),,2)),"-")
D11:D29	D11	=IF([@index]>=D$9,FORECAST([@index]-D$9,OFFSET($A$3,$D$8,MATCH([@index]-D$9,$B$2:$H$2,1),,2),OFFSET($A$3,-1,MATCH([@index]-D$9,$B$2:$H$2,1),,2)),"-")
A12:A29	A12	=EDATE(A11,1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C8:D8	Whole number	between 1 and 3
C9:D9	Whole number	between 0 and 90