0

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)

0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.