A sheet that receives imported data has 4 cols. Col A has Datetimes, and in Col D I’d like to use an arrayformula to calc the minutes elapsed from the previous to the current datetime.
I’ve tried a few variations of the arrayformula, adding it to cell D2, eg:
=ARRAYFORMULA(IF(ROW(A:A)=2, "Mins Elapsed", IF(A3:A="", "", (A3:A - A2:A) * 24 * 60)))
This throws err: “Result was not automatically expanded, please insert more rows (1).” The err message doesn’t help.
I tested an arrayformula in cell D4 – since it has to have a previous data cell for the calc – and it works fine:
=ARRAYFORMULA(IF(A4:A="", "", (A4:A - A3:A) * 24 * 60))
I can live with that if necessary, but it would be ‘cleaner’ if I could modify the formula with the header, and place it in D2. I think the problem may be that the actual calcs must start in row 4? What can I try next?
5
For anyone interested in an answer, I used an array to return:
- a Header
- a blank cell
- the arrayformula output
= { "Mins Elapsed"; ""; ARRAYFORMULA(IF(A4:A="", "", (A4:A - A3:A) * 24 * 60)) }
NOTE: For the array to result in a column, not a row, a semi-colon (;) must be used, not a comma (,) to separate the array values.