Deleting row creating a ref issue in excel-CodePudding

I have a helper column with the following code

=IF(A1<>A2,1-SUM(B1),SUM(B1))

The table looks like

When I delete a row, say row 21 in this example, I end up with a ref issue in cell B21 -

=IF(#REF!<>A21,1-SUM(#REF!),SUM(#REF!))

Is there any way to resolve this, perhaps with a way to sequence the formula in cell B2 such that it automatically runs the calculation and corrects based on rows being removed?

CodePudding user response：

You can use the offset function to avoid this and always make the formula look to the above cell like this:

=IF(OFFSET(A2,-1,0)<>A2,1-SUM(OFFSET(B2,-1,0)),SUM(OFFSET(B2,-1,0)))

This tells to use A2 but 1 row less, making it A1. (And same for B2.)

CodePudding user response：

In B2 use: =IF(INDEX(A:A,ROW()-1)<>INDEX(A:A,ROW()),1-SUM(INDEX(B:B,ROW()-1)),SUM(INDEX(B:B,ROW()-1)))

By using INDEX you can avoid referencing cells that may be deleted, which is causing the issue you have.