CODEWITHSUNDEEP
google-sheetsexcel-formulaarray-formulas
Cinghialino
Cinghialino

Reputation: 13

ARRAYFORMULA does not increase cell as it expands

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?

Upvotes: 1

Views: 724

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9907

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

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9345

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)))}).)

Upvotes: 1

Related Questions