Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

    Please help

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

    Thanks again!

    Aunt Linda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Balance (2003)

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

    Aunt Linda

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Balance (2003)

    Can you explain in detail what you want to accomplish?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Balance (2003)

    Aunt Linda

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


    =OFFSET(I2,MAX(IF(NOT(ISBLANK(I3:I15)),ROW(I3:I15) ,1))-ROW(I2),0)......you can adjust your ranges to suit
    Jerry

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Balance (2003)

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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Balance (2003)

    I guess we'll never know <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Running Balance (2003)

    She must have lost her balance! <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •