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

1 Answer 1


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, 2020 at 14:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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