Forum Discussion
Index Array Formula
I have been working on this for quite some time with no avail. Maybe it has been because I struggled in the beginning to outline exactly what I needed in a couple of recent help requests.
Seems like I should be able to make just a couple modifications to the already existing INDEX array formula below to get this to work. Maybe by summing values in column B??? If so, not sure how that should be done.
=INDEX(ROW(A:A),MIN(IF(INDIRECT("A1:A28")>=$J$2+(ROWS($1:1)-1)/(1440/$K$5),ROW(INDIRECT("A1:A28")))))
What current formula does:
Returns row numbers from column A that are 6 minute intervals apart from starting value in cell $L$2. This formula is currently copied down cells G4:G18.
Problem (what is lacking) with current formula:
Hasn't ability to skip time gap between days (example between cells A15:A16), thus I decided to create helper column B. Note: Just know I have a separate formula to figure column B so when there is a gap between days it strips out time from 15:00-8:30.
https://1drv.ms/x/s!AgYDS7saPf0Dg9x-fk-u0Y_fTnKuPA
What I am needing:
Instead of determining row number from calculation based off of column A, I now need it to return proper row by summing??? every 6 minutes (K5 cell value) from start time in cell J2. It would be preferable if formula returned rows for which time is the nearest to 6 minute interval as apposed to using greater, equal, or less than. Maybe could be accomplished with function MROUND.
Current values INDEX formula should return: (also bolded within sheet)
1
4
7
10
13
16
19
22
25
28
Thank you, thank you, thank you!!! :)