Hide duplicate running total values.

  • Issue: Have To columns A B: A is my entry column. A row in column A may, or may not have a value imputed. Column B is used for running total. (see example)


    [ATTACH=CONFIG]72452[/ATTACH]



    I am using the following formula to generate the running total: = IF(ISBLANK(A2),(A2+B1+A2),B1+A2) and have copied it down column B.

    The issue that I would like to resolve is to Hide the duplicate running values, when a Cell value in column A is not entered (Blank).
    Without interrupting the running total calculation down the column.
    I would like to not have to use conditional formatting; which seems to somewhat work.


    I would like the outcome to present like this:


    [ATTACH=CONFIG]72453[/ATTACH]


    Using Excel 2016

  • Re: Hide duplicate running total values.


    Try this:


    =IF(A2="","",$B$1+SUM($A$2:$A2))

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Hide duplicate running total values.


    Thank you AliGW.


    This appears to have resolved the issue.
    There is a small error issue when the formula omits adjacent cells, but I think I can resolve this easily enough.

  • Re: Hide duplicate running total values.


    The formula should work regardless of blank cells. If you mean something else, then you need to explain more fully and provide sample data that is more realistic.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Hide duplicate running total values.


    The formula worked fine. Thank you!
    However, it does produce the below error for cells that have no values.
    I can turn off the Error checking rule to resolve this, but it would be a bonus if the formula checked for the error.
    This is not that big of an issue, just a little aesthetically unpleasing.




    [ATTACH=CONFIG]72460[/ATTACH]

  • Re: Hide duplicate running total values.


    I don't get the error message here, and as I said there is no reason why you should.

  • Re: Hide duplicate running total values.


    I am not doubting, that you don't see the error on your worksheet.


    However, it does present in two different worksheets where I have tested the formula.


    With the Error handling rules : "Formulas which omit cells in a region" Unchecked, these errors will not present across the worksheet.


    Might be a Excel 2016 bug.


    [ATTACH=CONFIG]72462[/ATTACH]

  • Re: Hide duplicate running total values.


    It's no good attaching an image - attach the file instead.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Re: Hide duplicate running total values.


    Thanks. I've looked and to be honest it's inconsequential. Once you have protected the sheet, the green triangles will disappear. It's a bit like a false positive.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!