Formula change needed every 24 rows

Question

I think I'm missing something very basic here :( - My 1st sheet "Time Pred" -> MY SHEET LINK

Trying to get formula in Col E to change every 24 rows- it should refer to Col D cells corresponding to Col C value being 1, 13, 24 - the nearest ones.

So in linked example: E3 to E26 have a formula ; then E27 to E50 the formula changes (I've pasted the right formula I want there) - it references the Col D values as explained above.

But right now I have to manually change formula every 24 roes to make it look at the desired values - looking for help to redefine formulae so it changes as I drag it down as there are over 12000 rows in final sheet.

Detailed explanation: so in my sheet, if u see cells E3 to E15 - formula takes time value in those 2 cells and divides into 12 equal time periods and assigns that time to cells E4 to E14 (proven by control col F that counts duration between successive rows)

Similarly for next 12 cells - formula takes time value in those 2 cells (E15 & E 27) and divides that time into 12 equal time periods and assigns that time to cells E16 to E26 -

Now for next 12 cells- it should work likewise- so it should now refer to cells E27 to E39 - take time value in those 2 cells and divides that time into 12 equal time periods and assigns that time to cells E28 to E38.

Col F tells us if formula achieves desired results - which means duration will be constant every 12 periods (shown by Col C)

Answer 1

I think I got it. Some of the times toward the end doesn't seem to match up. I think your "Control(Duration)" might be a little off at the end. Ther's 1 formula per column and can be dragged down. https://docs.google.com/spreadsheets/d/1H_JHGV5ZtwDauO4zMmOt7Fq6pMfbpgUePUqdQEAL15o/edit#gid=0

Explanation:

• Updated the reference sheet ('Tref1') with columns M and T with day and night increments
• Updated the formula in column E ('Time pred') to vlookup and add each increment
• Formula in column E is updated to check for conditions and do a different formula for each range of periods (1, 13, between 1 and 14 (excl 1 and 13), and from 14 to 24))