how to keep a rolling 6 month range in my formula?

[SJB0814]

Hi all,.

Could someone suggest what I need in my formula to keep a rolling work out over the last 6 months when I add a new month column in my sheet?

I have a formula in P13 looking at the 2nd lowest number in a range of 6 months,

=SMALL($J$13:$O$13, 2)

but when I add a new month in column P the formula doesn't change to K13:P13. (i tried without the $ signs too).

Anyone able to let me know what I need to add into my formula? Many Thanks.

 

[ranman256]

i have an End Date cell (z1) off screen that has =Today()
then the start date (z2) is 6 mos prior: =EDATE(z1,-6)

 

[awoohaw]

Are you going to use this for any kind of filtering or lookup? This identifies the 2nd smallest dates in a Let function, which you can leverage into more information:

Book1
	I	J	K	L	M	N	O	P	Q
10
11
12
13	Date	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01
14	Value	35	38	32	37	34	37	26
15	2nd Smallest Date of Last 6	2024-03-01
16
17
18
19	Date	2024-01-01	2024-02-01	2024-03-01	2024-04-01	2024-05-01	2024-06-01	2024-07-01	2024-08-01
20	Value	35	38	32	37	34	37	26	27
21	2nd Smallest Date of Last 6	2024-04-01
Sheet2

Cell Formulas
Range	Formula
J15,J21	J15	=LET(Dates,$J13:$AZ13,Last6,TAKE(FILTER(Dates,Dates<>"",""),,-6),SMALL(Last6,2))

 

[SJB0814]

Hi, No more filtering or look up required.

If I used the =EDATE(z1,-6), how do I add that with the existing formula? =SMALL($J$13:$O$13, 2)

Thanks

 

[FormR]

Hi, welcome to the forum! If I understand correctly you can try this in P13, you should note that's this is a volatile function, which means it will re-calculate with every recalculation. I don't think it will be a big problem for you though as it's a pretty lightweight formula, but you should still try to use it sparingly.

=SMALL(OFFSET(P13,0,-6,1,6),2)

 

[SJB0814]

Hi, welcome to the forum! If I understand correctly you can try this in P13, you should note that's this is a volatile function, which means it will re-calculate with every recalculation. I don't think it will be a big problem for you though as it's a pretty lightweight formula, but you should still try to use it sparingly.

=SMALL(OFFSET(P13,0,-6,1,6),2)

yes that works, great help everyone, many thanks