CODEWITHSUNDEEP
google-sheets
Rawland Hustle
Rawland Hustle

Reputation: 781

IF statement to ArrayFormula

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

Upvotes: 1

Views: 213

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

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)

Upvotes: 1

a-burge
a-burge

Reputation: 1584

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.

Upvotes: 1

Related Questions