1

I'm trying to get an ARRAYFORMULA to subtract the current cell from the previous one, but it appears that if I write

=ARRAYFORMULA((A3:A)-(A2))

The formula does not increase of one cell as it expands.

here's an example of what I get

https://docs.google.com/spreadsheets/d/1ci4kiNCgjBZK0Ue218IRIk5t8tSmElF5EgbIxb1_Q9w/edit?usp=sharing

it's clear that it keeps referencing A2 (but I'm not using $A$2 in the formula)

if I wouldn't use ARRAYFORMULA I would simply use =SUM(A3-A2)

Can anyone suggest me a solution?

James Z
  • 12,209
  • 10
  • 24
  • 44
Cinghialino
  • 13
  • 4
  • What you are requesting is not clear. Are the results in the column on the right in your image correct or incorrect? If incorrect, what *should* they be. In addition, I recommend that you post a link to your sheet (or to a spreadsheet that contains your Col-L data for a few rows with the correct results *hand-entered* in Col M. Otherwise, you are expecting the volunteers here to type out your data into their own sheet; and that will likely keep many volunteers from choosing to help. – Erik Tyler Jan 12 '22 at 17:19
  • Hi Erik, I have updated my original question with more info and a spreadsheet – Cinghialino Jan 12 '22 at 17:30
  • Your sheet is locked ("access denied") to everyone but you. Please change the share permissions to "Anyone with the link..." and "Editor." – Erik Tyler Jan 12 '22 at 17:34
  • it's available now to anyone with the link. Sorry about that and thank you for helping. – Cinghialino Jan 12 '22 at 17:51

2 Answers2

1

Couple Comments

You should accept Erik Tyler's answer as he put in more work and helped you present your problem more clearly. However these formulas allow for some more flexibility if you want any values below the rows (which now that I think about it doesn't make a lot of sense based on having a dynamic array flowing down, but conceptually maybe someone will find it useful).

Simple Formula will require one cell below your range to be blank:

=iferror(filter(filter(N(A2:A),A2:A<>"")-N(A1:A),A1:A<>""),"")

Complex Formula will allow values directly below (which I again admit seems nonsensical considering the whole point of this is a dynamic expansion down rows...)

=Filter(filter(filter(N(A2:A),A2:A<>"")-N(A1:A),A1:A<>""),ISNUMBER(filter(filter(N(A2:A),A2:A<>"")-N(A1:A),A1:A<>"")))

I gave a demo on your page.

enter image description here

enter image description here

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
1

Your sample spreadsheet is still "View only"; so neither I nor anyone else can directly leave our solutions. However, place the following formula in D1:

=ArrayFormula({"Difference"; IF(A2:A="",,IFERROR(A2:A-OFFSET(A2:A,-1,0)))})

This will create the header (which you can change within the formula itself as you like) and all results.

(Adjusting to the location in your actual spreadsheet, as indicated in your post, this would be =ArrayFormula({"Difference";IF(L7:L="",,IFERROR(L7:L-OFFSET(L7:L,-1,0)))}).)

Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • That `;` sorcery again! Very cool answer. I like that mine isn't a volatile function, but you get an upvote for the header maneuver that I keep forgetting about. – pgSystemTester Jan 12 '22 at 18:04
  • What is "volatile" about OFFSET in this context? I think people just hear "OFFSET is volatile" and it scares them. But in actuality, any volatility is contextual. – Erik Tyler Jan 12 '22 at 18:06
  • Hey thanks for the update. The link is editable to whoever goes there. Not sure why an header was added (it's a cool feature though). – Cinghialino Jan 12 '22 at 18:27
  • the solution is on sheet2 – Cinghialino Jan 12 '22 at 18:30
  • 1
    See my newly added sheet "Erik Help." – Erik Tyler Jan 12 '22 at 18:36
  • Sorry my previous comment on `Offset` not being volatile in Excel was incorrect. It is volatile in Excel, but not Sheets. Weird. I posted [more detail in an answer here](https://stackoverflow.com/a/70687188/11732320). – pgSystemTester Jan 12 '22 at 19:16