Offset formula with condition

[sillynanny]

Hello All, I am trying to modify the following formula to pick up the last 12 months from specific date. So the 12% is the below formula, what I am trying to do is for the below formula to pick up last 12 months from certain date, like 12/31/21 or 3/31/22. So from 3/31/22 it should be 12%, but if I want the formula be based on a date, then 12/31 will be 8.1% since it will be the last 12 from that date.

Column E is the following formula:
=FVSCHEDULE(1,OFFSET(C3,COUNTA(C:C)-12,0,12))-1

Column B	Column C	Column D	Column E	Column F
1/31/2021​	0.1%​
2/28/2021​	0.2%​
3/31/2021​	0.3%​
4/30/2021​	0.4%​
5/31/2021​	0.5%​
6/30/2021​	0.6%​
7/31/2021​	0.7%​
8/31/2021​	0.8%​
9/30/2021​	0.9%​
10/31/2021​	1.0%​
11/30/2021​	1.1%​		Current	Should be
12/31/2021​	1.2%​	12/31/2021​	12.0%​	8.1%​
1/31/2022​	1.3%​
2/28/2022​	1.4%​
3/31/2022​	1.5%​	3/31/2022​	12.0%​	12.0%​

Thank you,

 

[Takae]

Please try this formula.
=FVSCHEDULE(1,offset(c14,-11,0,12))-1
However, this formula is valid from the line of 12/31/2021.

 

[sillynanny]

Please try this formula.
=FVSCHEDULE(1,offset(c14,-11,0,12))-1
However, this formula is valid from the line of 12/31/2021.

Hello, thank you for your reply. However, i am looking for a formula that will reference the date and be calculated based on the date of the cell.