Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Attached is example

    I need to carry a balance (column C) with new charges (column B). The balance is carrying over from the previous balance [10] when I have no entry in the charge column [B5], I would like to have no balance show on that row [C5] and following balance cells {C6 & down] untiI I have a charge[number] in that row[C5]. I assume I need a "sum C, If B >0" type of formula, but none I tried works.

    TIA
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Len,

    What you might want to try is to set conditional formatting so that if there is no charge the running total column cell is formatted with the foreground color the same as the background color {the value will still be there but not visible}. By doing this you can just copy the formula in the running total column down the column w/o worrying about exceptions.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks RetiredGeek but I tried that but I could not get it to work. How can I do this in conditional format? I assume I use "Formula is", but then what formula do I use?
    Any other suggestions welcomed.

  4. #4
    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
    How about in C4
    =IF(B4="","",SUM($B$3:B4))

    Copy this down the column...

    Steve

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Len,

    Ok the attached sheet has the Conditional format formula.

    Note to enter the formula you highlight the whole range and use the addresses of the activecell in the range to construct the formula, i.e. if C4:C17 in the example is highlighted you would use B4 as your reference in the formula. =CELL("Contents",B4)=0. Excel will automatically adjust the formula for the other cells.

    Steve, which would be more efficient your Sum formula or the conditional formatting?
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks SteveA.
    That is what I wanted, but did not know how to write the formula. Once I see it it is obvious. Since I wanted to carry an original balance in C3, I just added that to the formula. Works as needed.
    =IF(B4="","",SUM($B$3:B4)+($C$3)
    I was using =0 and could not get it to work. Now I see I did not want "0" because it would not work with a blank.

    I was trying to do the math with C4+B5 [add charge to last balance] to get new balance. =IF(B5="","",SUM(C4+B5))
    Both work fine.
    Again, thanks.

  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
    RetiredGeek

    Instead of:

    =CELL("Contents",B4)=0

    You can just use the simpler:
    =B4=0


    Len,
    Instead of
    =IF(B4="","",SUM($B$3:B4)+($C$3)

    You can use:
    =IF(B4="","",SUM($B$3:B4,$C$3)

    And having a blank in C3 will not give an error.

    The formula:
    =IF(B5="","",SUM(C4+B5))

    Will not work correctly if you have (and perhaps this will never come up) some blank cells in B and some ones down further in the rows. Also the one I give allows inserting rows within the range without the formulas becoming corrupted
    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by SteveA View Post
    RetiredGeek

    Instead of:

    =CELL("Contents",B4)=0

    You can just use the simpler:
    =B4=0
    Steve, yes I realized that shortly after I posted, thanks. But my question still remains, which is more efficient using a sum which could reference hundreds or thousands of cells or the conditional formatting?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  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
    I think the sum makes more sense. conditional format checks all the cells in col B just as the IF does. The if (I don't think) sums the cells if does nto have to and the cond formatting will have to. Also "masking" with conditional format works on the display but does not always work with printing...

    Steve

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Good points...thanks again.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Conditional formatting is also volatile.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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