Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding balance in unused rows

    I'm trying to set up a new spreadsheet to keep track of payroll. Row 2 is the first row where values are entered. Column A is the date, Column B is the gross pay, Column C is Federal withholding, Column D is Medicare tax, Column E is SS tax, Column F is State withholding, Column G is Net Pay (=B2-C2-D2-E2-F2). I also want a Column H for Year to date gross. To keep a running total in H without the balance showing in rows with no values entered yet, I put the following formula in H2: =SUM(B2) and the following formula in H3: =IF(B3="","",B3+H2). This gives me a balance in H3 without that number appearing in every row under H3. Now here's the problem: I also want a Column I for Year to date net, where the formula for I2 is =SUM(G2) and the formula for I3 is =IF(G3="","",G3+I2). It's the same formula I have starting in H3. BUT I'm getting the running balance in every row under I3. If the formula in H3 works the way I want it to, why not the one in I3?

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..it's because G3 isn't blank! It's got a formula in it!
    Use this instead in cell [I3]:
    =IF(B3="","",G3+I2).

    or see attached file.

    zeddy
    Attached Files Attached Files

  3. #3
    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
    ..it's because G3 isn't blank! It's got a formula in it!
    This is not strictly true. It is not that the cells in G have a formula, it is because the formulas will never yield a null string("") but will always yield a number (=B2-C2-D2-E2-F2). With your modification to the G3 formula to display a null, the original formula using G3 will work.

    Steve
    PS Glad to have you back. I hope you are feeling better! No longer all wrapped up with no place to go...

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey thanks! That fixed it.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Steve

    What I meant to say was
    Hi

    ..it's because G3 isn't blank! It's got a formula in it which will never yield a null string!

    It was sloppy on my part, a bit like my insides. Not quite in full working order yet.

    zeddy

Posting Permissions

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