Formula That Is Resistant To A Row Insert

[MikeG]

I have a table and Column A can have a value of either 1 or 0. Column B has a formula that generates a running total of the number of 1's in Col A. If on the current row the value of Col A is 1, then I take the total from the row above in Col B and add 1. If Col A equals 0, I just take the value of Col B from the row above. So for example' the formula in B9 is:

=IF(A9=1,B8+1,B8)

My problem is, if a user inserts a row and copies down the formula from above, but only down to the new blank row, then the formulas below get messed up. Because I want the users to be allowed do this type of insert and copy I am trying to get a bulletproof way of doing this.

Now in reality, I have several columns that require this treatment, and many thousands of rows. I thought of using

=COUNTIF($A$1:A9,1)

But this significantly slows down the performance of the sheet to an unacceptable response time for the user.

Any suggestions.

Thanks,

Mikeg

 

[seenfresh]

What are the fields in your table, is the running total date sensitive? When someone adds a new record does it need to be in a specific order?

Just thinking a pivot table may be of better use, this way it is dynamic and you do not need to enter a formula in Column B and constantly monitor it.

 

[Greg Truby]

why isn't the formula in B9:

• =B8+A9

And I'm not understanding how a row insertion causes problems. Can you give us an example of before & after formulas following a specific insertion action?

 

[MikeG]

why isn't the formula in B9:

• =B8+A9

And I'm not understanding how a row insertion causes problems. Can you give us an example of before & after formulas following a specific insertion action?

Greg:

Thanks - actually in most cases, the values in Col A are text values e.g. Southeast, or Product 6754 etc. I just simplified for the post.

The insert problem comes because when a user inserts say a row. Let's use your formula of B8+A9 in B9. We now insert a new row 9 and copy the formula from row 8 to row 9 (which given the spreadsheet, is the natural thing for the user to want to do). But row 10 is now wrong because it says A10+B8. I could just tell the user to remember to copy the formula down to B10, but just given the type of data and the number of columns, some of which can't be copied down to row 10 from 9 because it would overwrite data.

Thanks,

Mike

 

[MikeG]

What are the fields in your table, is the running total date sensitive? When someone adds a new record does it need to be in a specific order?

Just thinking a pivot table may be of better use, this way it is dynamic and you do not need to enter a formula in Column B and constantly monitor it.

Thanks,

I'll think about using a PivotTable.

MikeG

 

[Greg Truby]

Give this a shot (in a test copy of the workbook):

=IF(A9=1,INDIRECT("R[-1]C",FALSE)+1,INDIRECT("R[-1]C",FALSE))

INDIRECT is a volatile function, so you'll need to see if the recalc drag is heavier than your COUNTIF() proved to be.

 

[MikeG]

Give this a shot (in a test copy of the workbook):

=IF(A9=1,INDIRECT("R[-1]C",FALSE)+1,INDIRECT("R[-1]C",FALSE))

INDIRECT is a volatile function, so you'll need to see if the recalc drag is heavier than your COUNTIF() proved to be.

Thanks Greg - brilliant.

MikeG