Dynamic small function

[ellabella6]

Hello,

I am trying to create a dynamic formula to analyse a dataset that moves down each week and more data is added. For example I have shown in the screenshots what it will look like the first week and then how it will look once you have input 5 weeks of data. The formula that I am struggling to write it what will go in cells J:N. I need the formula to follow the correct date and complete a small function if equal to or great than and then roll the last number up if the date is less than so that there are no blank cells. For example to roll the data up I was using the formula =IF(I2<"05-Sep-2023",J3,"") this works as I want it to in rolling the data up for any dates that are after 5th September, ie 6th,7thsep etc.

What I struggling with is then adding the formula if the date in Column I is equal to or greater than 05-sep-2023 it must do a dynamic small function where I want it to find the 2nd smallest number from 5th sep going back 10 days/cells * 0.36 + find 3rd smallest from 5th Sep going back 10 days/cells*0.64 then *-1 to get a positive number. The basic small formula I was working with is =IFERROR((SMALL(B2:B11,2)*0.36+SMALL(B2:B11,3)*0.64)*-1,"") this ultimately works however is very easy for the formula to go wrong with all the copying and pasting done each week. I just cannot seems to get it to work to complete an offset to find the correct date, go back 10 days and then complete that small formula.

Any help to formulate all of this together where if the date is less than 5th sep it copies the below cell or if the date is equal or great than the inputted date it does an offset small formula, I would greatly appreciate it.

I hope this makes sense

 

[KRice]

Welcome to the MrExcel board! Unfortunately, what you've described doesn't make sense (to me). Can we review some things? The images you've posted are not clarifying the problem for me. I see dates in column A running down the column, stepping back one day at a time, and skipping conventional weekend days (Saturday and Sunday). Are those dates dynamic?

I see dates running across row 1, stepping forward 1 week at a time, beginning on 5-Sep-23. Are you adding a column every week, so column G would at some point be populated with 10-Oct-23?

How do the dates in row 1 relate to the dates in column A? For example, if I examine cell B32 (I think), I see 3642. That value seems to be associated with a column A date of 20230905 and a column heading of 05-Sep-23. If I go down 10 more rows, the value of 3142 seems to be associated with a column A date of 20230822 and the same column heading of 05-Sep-23. So the data relationships are not clear.

You mention the dataset moves down each week, but if I attempt to track the 3642 entry just mentioned, I see it in the same position in the adjacent column, but none of the numbers below it match with the earlier column. And by the time we move to column D, none of the data seems to have actually moved down...its all different. Where did the earlier data go?

I need the formula to follow the correct date and complete a small function if equal to or great than and then roll the last number up if the date is less than so that there are no blank cells.

...it would be helpful to illustrate what you want, even if you manually edit the worksheet to depict the progression.

Using the XL2BB add-in to post a worksheet snippet would also be very helpful. By doing so, you are more likely to get help because the worksheet structure and some data will be readily available to others.