Spill Array Formula to Values between Cells on Separate Rows

[ScottTemple]

Hello,

I have a spill array that encompasses columns A - AD with the following formula: =CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,2,3...

I would like to calculate the difference between two times that are located on separate rows from each other (see below). The formula I would use in a non spill array is =IF(N3>=O2,N3-O2,N3+1-2)*1440, but that will not work in this instance. Any assistance would be greatly appreciated.

 

[ScottTemple]

I see what I made a mistake on, but for some reason it is removing all the rows from the spill array now that are missing those dates/times

=CHOOSECOLS(FILTER(Shaver_Data,(Shaver_Data[Date]<>"")*(Shaver_Data[Start Time]<>"")*(Shaver_Data[Finish Time]<>"")*(Shaver_Data[Insp Start Date]<>"")
*(Shaver_Data[Insp Complete Date]<>"")),SEQUENCE(30))

 

[JamesCanale]

This function will filter out anything with a blank in any of those fields. What would you prefer?

Also, since you are taking the first 30 columns, you might just use

TAKE(filter(....),,30)

 

[ScottTemple]

I would need to still leave those rows there, just return a blank value for those cells that are missing data. We're undecided on which columns to bring over currently, so we just brought them all over; will most likely remove some at a later date, but good to know.

 

[ScottTemple]

Went back through the original thread for CHOOSECOLS that another user was assisting me and tried:

=LET(a,CHOOSECOLS(Shaver_Data,SEQUENCE(30)),IF(a="","",a))

Turns out that the one I posted here originally would have removed any rows where the 'Date' value was missing from any cells, which was not good lol. This modified formula now leaves any blank cells in my data sheet, blank in my spill array.

 

[ScottTemple]

Ah, the day rollover... Try this:

=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),IFERROR(d*1440,""))

Hey James, quick follow up, the formula is working great, but it starts populating the data in row 4 instead of row 3. The original formula was =IF(N3>=O2,N3-O2,N3+1-O2)*1440 but it is calculating more like everything has been shifted down a row. Thoughts?

 

[JamesCanale]

This aught to move it up.

=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),DROP(IFERROR(d*1440,""),1))

The only change is at the end - the DROP around the IFERROR.

 

[ScottTemple]

This aught to move it up.

=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),DROP(IFERROR(d*1440,""),1))

The only change is at the end - the DROP around the IFERROR.

Sweet! Thank you James!

 

[ScottTemple]

James,

Would like to revisit this thread. The formula works, however, it stops one line before the end of the current data set. This causes cell AX to have a #N/A error as there is no data populating in AW. You can see in the below picture that the spill (blue box) stops at row 4981, but everything all the other data populates until the last line 4982. Thoughts?

 

[JamesCanale]

There is always going to be one less row in this calculation, because it's basically differentiating. What would you want in the last row, when it's looking to the next row - but that row has no data.

But you can append a blank or a zero if you like:

=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),VSTACK(DROP(IFERROR(d*1440,""),1),""))
=LET(a,CHOOSECOLS(A2#,14),b,CHOOSECOLS(A2#,15),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",IF(a-c>0,a-c,1+a-c)),VSTACK(DROP(IFERROR(d*1440,""),1),0))

 

[ScottTemple]

Thanks James, was messing around trying to add the 0 or the "", but just couldn't figure out where to place it lol.