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.

 

[JamesCanale]

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

 

[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.

 

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

 

[JamesCanale]

I do something like this - I'll roll one column down one row and put some junk in the front. I don't know if this will be useful for you.

MrExcelPlayground20.xlsx
	A	B	C	D	E	F
1	Start	Finish		Start	Finish	Lag
2	9:30 PM	1:00 AM		9:30:00 PM	1:00:00 AM
3	1:15 AM	4:10 AM		1:15:00 AM	4:10:00 AM	15
4	4:45 AM	6:00 AM		4:45:00 AM	6:00:00 AM	35
Sheet28

Cell Formulas
Range	Formula
D2:E4	D2	=CHOOSECOLS(A2:B4,1,2)
F2:F4	F2	=LET(a,CHOOSECOLS(D2#,1),b,CHOOSECOLS(D2#,2),c,DROP(VSTACK("a",b),-1),d,IF(c="a","",a-c),IFERROR(d*1440,""))
Dynamic array formulas.

 

[ScottTemple]

Hey James,

I was out of the office for a couple of week and didn't get a chance to reply back to you until today.

The formula for D2 returns a #SPILL! error; here's an example of my formula: =CHOOSECOLS(N:O,1,2).

 

[JamesCanale]

N:O is taking the whole columns. When you try to put that starting in row 2, it all just can't fit. If you can make the range smaller, or move the formula up to row 1.

 

[ScottTemple]

Never mind, I see what you're doing here, but I'm a bit confused on the code. A/B are N/O in my sheet and D2/E2/F2 are BE/BF/BG. How does that translate in the formula you provided for F2?

 

[JamesCanale]

What I was doing in D and E, was to simplify what you had with "=CHOOSECOLS(FILTER(Table9,Table9[Date]<>""),1,2,3.." Basically just a clean array of start and finish values. Then my F column is where I sort that into the Lags between the current rows 'end' and the next rows 'begin'.

 

[ScottTemple]

Sorry, bit confused by what you mean. Are you suggesting to create a second CHOOSECOLS spill array to include just the Start and FInish times and then use the formula in F2 to create lag times?