Results 1 to 5 of 5

Thread: Zero Values

  1. #1
    New Lounger
    Join Date
    Mar 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero Values

    Hope someone can help me with what is probably a very simple question: I am constructing a very simple (no macros) spreadsheet for several folks to use. Column D allows input of values W, L, S, or A. Columns F thru I contain a countif formula to show a running total of each value. I have copied that formula for 150 rows. My spreadsheet is functional, but ugly, because it contains 150 rows of zeros where no data has yet been entered. Here's my question: Can I somehow force columns F thru I to remain blank (or format them white on white) if Column D is empty?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Zero Values

    If you go to Tools, Options, View you can set the window options not to show zero value. Just make sure the box beside zero value is unticked.

    This will mean that all zero value will just appear as blanks.

    Andrew C

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values

    you can use this

    =IF(COUNTBLANK(A1:E1)=5;"";COUNTIF(A1:E1;"W"))

    This formula will display zero if there are no "W" characters in the range A1:E1, but will leave the cell empty if nothing was filled in the cells A1 till E1. The right number of "W" characters will appear in case "W" values were entered in the cells A1 till E1

  4. #4
    New Lounger
    Join Date
    Mar 2001
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values

    <P ID="edit"><FONT SIZE=-1>Edited by cal43 on 01/03/19 18:39.</FONT></P>Andrew,
    Thanks very much. I already knew how to suppress the display of zero values. What I wanted to do was to suppress any display if another column in the current row was blank. Hans Pottel gave me the information I needed, and I ended up with
    <font face="Comic Sans MS"><big>=IF(ISBLANK($D5),"",COUNTIF($D$4:$D5,"W") </big></font face=comic>

    Thanks again for your help. I'm very impressed with the speedy replies on this board!

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Zero Values

    Hi Charles,

    Hans's method is better if you do not want to blank all zeros. But just for variety there is another approach using Custom Formats. The cell value will still be zero, but will not display. The following custom format hides zeros, <big>#,##0;-#,##0,""</big>

    Just another way,

    Andrew C

Posting Permissions

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