Google-sheets – Drag formula without changing value

google sheets

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

Best Answer

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.

Related Topic