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

SJB0814

New Member
Joined
Feb 19, 2024
Messages
3
Office Version
  1. 365
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.

1708349411240.png
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
i have an End Date cell (z1) off screen that has =Today()
then the start date (z2) is 6 mos prior: =EDATE(z1,-6)
 
Upvote 0
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
IJKLMNOPQ
10
11
12
13Date2024-01-012024-02-012024-03-012024-04-012024-05-012024-06-012024-07-01
14Value35383237343726
152nd Smallest Date of Last 62024-03-01
16
17
18
19Date2024-01-012024-02-012024-03-012024-04-012024-05-012024-06-012024-07-012024-08-01
20Value3538323734372627
212nd Smallest Date of Last 62024-04-01
Sheet2
Cell Formulas
RangeFormula
J15,J21J15=LET(Dates,$J13:$AZ13,Last6,TAKE(FILTER(Dates,Dates<>"",""),,-6),SMALL(Last6,2))
 
Upvote 0
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
 
Upvote 0
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.

Excel Formula:
=SMALL(OFFSET(P13,0,-6,1,6),2)
 
Upvote 0
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.

Excel Formula:
=SMALL(OFFSET(P13,0,-6,1,6),2)
yes that works, great help everyone, many thanks
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top