IF statement to ArrayFormula-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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.