Zero Values

[cal43]

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?

[Andrew Cronnolly]

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

[Hans Pottel]

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

[cal43]

<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!

[Andrew Cronnolly]

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