I have the formula

=IF(ISBLANK(B48), "",MINUS(B48,B2))

Where 48 should be the current row number (so the formula for row 40 is:

=IF(ISBLANK(B40), "",MINUS(B40,B2))

b2 is always the same. I try to apply this formula to multiple rows, but when I drag the formula It updates the b2 value. So for every row I have to update the b2 value. Is there a way to apply the formula to multiple lines (the best would be the whole column after row 2) without having to change the b2 value for every row? enter image description here


You need to "freeze" the cell B2 by using the dollar sign $ and use B$2 instead.

Then your formula will be:

=IF(ISBLANK(B48), "",MINUS(B48,B$2))

You can take it a step further by using an arrayformula:

=ArrayFormula(IF(ISBLANK(B30:B55), "",MINUS(B30:B55,B$2)))

(please adjust the ranges as needed)

Function ArrayFormula.

  • The arrayformula does make it a lot easier. Don't you agree? May 11 '20 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.