ArrayFormula With Header [duplicate]

Question

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?

From the google-sheets tag wiki: **Do NOT share spreadsheets/images as the only source of data. Use markdown TEXT tables instead. ** — Ken White
– Ken White, Commented Dec 14, 2024 at 1:47
I did see that... but I couldn't figure out how to do the markdown text tables. As it's a simple example, I didn't think it would be a major prob. — maxhugen
– maxhugen, Commented Dec 14, 2024 at 2:01
Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Dec 14, 2024 at 5:32
Use the answer by @player0 in the referenced duplicate question, and wrap as an array (curly brackets). Something like ={"Mins Elapsed";INDEX(IFERROR(1/(1/(ROUND(24*(B3:B-A3:A), 2)))))} — Tedinoz
– Tedinoz, Commented Dec 15, 2024 at 0:43
@Tedinoz - thanks, I had worked out a solution - using an array - before seeing updated comments! Cheers, Max — maxhugen
– maxhugen, Commented Dec 15, 2024 at 5:17

maxhugen · Accepted Answer · 2024-12-15 05:08:10Z

0

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.

answered Dec 15, 2024 at 5:08

maxhugen

1,9644 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ArrayFormula With Header [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related