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]

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?

 

[JamesCanale]

No. I just did that to simulate some other calculation that you had shown to get the set of data you were interested in from some larger set that you had that you didn't post. If that isn't true, just replace the "a" and "b" in the let to directly take the data of interest.

 

[ScottTemple]

Sorry still very confused by what you mean. If I already have the times listed in the original spill array, why would I need to create a second array to calculate from?

 

[JamesCanale]

Maybe we can start over again. Post some of your data. I can give one formula to sort it out.