I have the following formula in cell D2 in a Google Spreadsheet:
=IF(ISBLANK(B2),"",B2-C2)
How do I apply that formula to the whole column D with ArrayFormula? Because ArrayFormula is the best solution, right?
Thanks
-
did you post this question twice?MattKing– MattKing2021-09-15 15:22:32 +00:00Commented Sep 15, 2021 at 15:22
-
No, they are slightly different.Rawland Hustle– Rawland Hustle2021-09-15 15:23:40 +00:00Commented Sep 15, 2021 at 15:23
Add a comment
|
2 Answers
Enclose the formula in the google sheets Arrayformula() function. Then change the reference from B2 to B2:B500 (or leave blank for open ended reference). Same for the second reference.
So =ARRAYFORMULA(IF(B2:B="","",B2:B-C2:C)) should work. Note that I changed ISBLANK to X="" since the arrayformula is bound to find values in the entire array so it won't return blank.
--
Whether this is the best way I don't know. The benefit is that this grows with the dataset and avoids errors when you haven't filled the formula far enough down. However it can sometimes slow sheets down (I've heard but not experienced anything severe myself). If you're experiencing slow sheets I recommend Ben Collin's blog on the topic.
5 Comments
Rawland Hustle
Thanks! Your formula works great but it turns out I gave the wrong formula as example. I want to subtract B3 from B2, B4 from B3, B5 from B4 and so on... What formula should I use then?
a-burge
Good question, I guess you could try using closed ended references (i.e. B3:B501-B2:B500).
Rawland Hustle
That did not work unfortunately. Any other ideas?
a-burge
This works for me as an example
=ARRAYFORMULA(if(B3:B8="","",B4:B9-B3:B8))
Mike Steelson
You can also use MMULT to avoid using closed ended references so that you will not forget new lines.
Whithout closed ended references
=mmult( arrayformula(1*(ROW(A2:A)<TRANSPOSE(ROW(A2:A)+1))*(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))) -1*(ROW(A2:A)>TRANSPOSE(ROW(A2:A)))*(ROW(A2:A)<=TRANSPOSE(ROW(A2:A)+1)) ) , A2:A)
Comments
