How do I leave a field blank when it's a total?
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: How do I leave a field blank when it's a total?

  1. #1
    Guest

    Default

     

    For example:

    I have 4 columns with values 1 2 3 4
    + 1 2 3 4
    A B C D
    6 8

    I want to show only values
    that are higher than 5 as a sum of the numbers vertically for A B C D.

    On the final line, I want to have the totals calculated. I'd have 6 for C and 8 for D, but I don't want anything in the blocks for A and B. Nothing, no zero, no symbology, nothing, long as my qualifier is > 5, I don't want the field populated.

    Is there an easy way to show this?

    Thanks

  2. #2
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 12:46, Anonymous wrote:

    For example:

    I have 4 columns with values 1 2 3 4
    + 1 2 3 4
    A B C D
    6 8

    I want to show only values
    that are higher than 5 as a sum of the numbers vertically for A B C D.

    On the final line, I want to have the totals calculated. I'd have 6 for C and 8 for D, but I don't want anything in the blocks for A and B. Nothing, no zero, no symbology, nothing, long as my qualifier is > 5, I don't want the field populated.

    Is there an easy way to show this?

    Thanks
    Would it be okay if you had the totals in columns A and B but couldn't see them (i.e., A total = 2 and B total = 4)?

    How about if you zeroed out the total if it was less than, or equal to, 5 and then custom formatted it so the zero wouldn't show?

    The reason I ask these questions is I am wondering what you are doing with the totals.

    Regards,

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #3
    Guest

    Default

    =if(sum(**:**)=0,"",sum(xx:xx))

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Question regarding the above response

    =if(sum(**:**)=0,"",sum(**:**))

    The "" formats the cell as a text cell and the result can not be used in further calculations. Is there an alternative?

    Thank you for your time
    -Dave

  5. #5
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 14:22, croweld89 wrote:
    Question regarding the above response

    =if(sum(**:**)=0,"",sum(**:**))

    The "" formats the cell as a text cell and the result can not be used in further calculations. Is there an alternative?

    Thank you for your time
    -Dave
    Sure, how about
    =sum(**:**)

    You can then custom format the cell so the zero doesn't show. Use this for formatting (including the quotes):

    0.00;-0.00;""

    If you have any questions just let me know.

    Regards,



    _________________

    Barrie Davidson
    My Excel Web Page

    [ This Message was edited by: Barrie Davidson on 2002-03-06 14:26 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you Barrie, however...

    My goal is to automate a 25+ worksheet file. Most of the worksheets contain columns of one years worth of months. To meet this goal, I don't want to manually hide and unhide months that are not populated for the current year. Hence, I am using the "IF" statement.

    Is there an alternative way to automate hiding cell contents when the cells contents equals zero that will automatically unhide when not equal to zero? And, allow the cell to be used in further calculations even when the cell is hidden?

    Thank you.
    -Dave

  7. #7
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-06 14:34, croweld89 wrote:
    Thank you Barrie, however...

    My goal is to automate a 25+ worksheet file. Most of the worksheets contain columns of one years worth of months. To meet this goal, I don't want to manually hide and unhide months that are not populated for the current year. Hence, I am using the "IF" statement.

    Is there an alternative way to automate hiding cell contents when the cells contents equals zero that will automatically unhide when not equal to zero? And, allow the cell to be used in further calculations even when the cell is hidden?

    Thank you.
    -Dave
    Dave, that formatting will display nothing if the cell contents equal zero. Give it a try - right click on cell A1, select format cells, select Custom on the Number tab and paste this in the Type box

    0.00;-0.00;""

    You will see nothing if you put zero in cell A1 and you will see the number if you put anything else in.

    Is this any clearer?

    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you Barry!!

    I will need to tweak my file a bit for this, but your recommendation will work. But, this should do the trick. Thank again.

    -Dave

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com