1. ## Running Balance (2003)

I can do this with an index, match formula but I can't remember the step that allows the formula to automatically include any inserted rows.

Have attached a small example of what we're trying to do

Thanks again!

Aunt Linda

2. ## Re: Running Balance (2003)

Try
<code>
=INDEX(I:I,MATCH(9.99999999999999E+307,I:I))
</code>
9.99999999999999E+307 is the highest number that can be entered in an Excel worksheet; the MATCH function will return the index of the last numeric entry in the column.

3. ## Re: Running Balance (2003)

My mistake (as usual) - should have sent you this - most likely will change your answer . . . sorry!

Aunt Linda

4. ## Re: Running Balance (2003)

Can you explain in detail what you want to accomplish?

5. ## Re: Running Balance (2003)

The person who made this speadsheet has left our place of employment, leaving this for the woman who asked me to help her. She wants to enter purchases for each separate account on any given sheet and have the cell in lime green show the running balance of that particular account. She will obviously have to insert rows as the year goes on. That's about all I know - sorry.

Thanks for trying.

Aunt Linda

6. ## Re: Running Balance (2003)

Someone else will probably come up with a simpler formula. You could enter the following as an array formula in J2 (confirm with Ctrl+Shift+Enter):
<code>
=INDIRECT("I"&(MATCH(0,NOT(ISBLANK(I3:I502))*ROW(1 :500),0)-1+ROW()))
</code>
and copy to J12, J17 etc. The formula assumes that no more than 500 rows per account will be used. See attached version.

7. ## Re: Running Balance (2003)

Is this what you are after?
In K2 enter:
=I2+K1

In J2:
=IF(ISBLANK(I2),K2-SUM(\$J\$1:\$J1),"")

Copy i2:J2 down the columns.

K2 can be hidden if desired.

Steve

8. ## Re: Running Balance (2003)

Aunt Linda

Here is my pennies worth... as Steve and Hans have answered before me, but I used:

9. ## Re: Running Balance (2003)

If your results are what she is after, my answer is wrong, and I misunderstood completely the problem... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

Steve

10. ## Re: Running Balance (2003)

I'm not sure. Let's wait and see what Aunt Linda says.