make cell show blank when total is zero

[Guest]

Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead of having a
0 show in the cell?

 

[Frank Kabel]

Hi
you could use a custom format such as
0.0;-0.0;

 

[Guest]

How would I enter that in the formula?

Hi
you could use a custom format such as
0.0;-0.0;

 

[Frank Kabel]

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

 

[Guest]

Frank your solution did not work.

Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side

 

[Debra Dalgleish]

You can hide all the zeroes on the worksheet:

Choose Tools>Options
Select the View tab
Remove the check mark from 'zero values'
Click OK

 

[Frank Kabel]

Hi
what exactly happens?

 

[Dave Peterson]

You could also use:

=if(yourlongformula=0,"",yourlongformula)

This cell has a value of "" (looks empty). The other suggestions still have a
value of 0--just hide it.

 

[Guest]

it worked for me, thank you!

Hi
what exactly happens?

 

[Triple]

Here is my formula:
=COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),"*" &
$B8 & "*")
When the total is zero, how do I show the cell as blank instead of having a
0 show in the cell?

You could click tools, then options, and under the view tab make sure
zero value is not checked/ticked. Of course this will be sheet wide,
so if by chance you do want a zero value to show other posts could be
best!

 

[markstro]

You could click tools, then options, and under the view tab make sure
zero value is not checked/ticked. Of course this will be sheet wide,
so if by chance you do want a zero value to show other posts could be
best!

You can simply conditionally format cells in that range to be white on
white if the cell equals zero

 

[Guest]

"You can simply conditionally format cells in that range to be white on
white if the cell equals zero"

How would I do this?

 

[penri0_0]

Go to Format on the tool bar, select conditional formatting. Select cell
value is 'equal to' and then type 0 in the next box. Then select the
format box and seT the text colour to white. Then if your formula
returns 0 the cell will appear blank.

 

[swatsp0p]

I think what the OP want to do is return a blank cell rather than 'hide
the result with text color.

Alter your formula to this:

=IF(COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Dail
MP'!$5:$5,0)-1),"*" &$B8 & "*")=0,"",COUNTIF(OFFSET('Dail
MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),"*" &$B8 & "*"))

should the result of your formula be zero, a blank cell will be th
result, otherwise the result will be shown.

Is this what you were looking for?

Bruc

 

[swatsp0p]

I think what the OP want to do is return a blank cell rather than 'hide
the result with text color.

Alter your formula to this:

=IF(COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Dail
MP'!$5:$5,0)-1),"*" &$B8 & "*")=0,"",COUNTIF(OFFSET('Dail
MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),"*" &$B8 & "*"))

should the result of your formula be zero, a blank cell will be th
result, otherwise the result will be shown.

Is this what you were looking for?

Bruc

 

[Lindy]

I searched and found this thread. I'm using Excel 2003.

I used Frank's advice of custom cell format to make cell show blank when
total is zero, eg: 0.0;-0.0;

but it only returns me one place of decimal rounded up to nearest 10.
Result: 123 * 11.05 = 1359.2

I changed the custom cell format to 0.00;-0.00;
Result: 123 * 11.05 = 1359.15

What I would like returned is 1,359.15

Can you help me with a custom cell format returning 1,359.15 where it will
make the cell show blank when total is zero?

Lindy

 

[Sandy Mann]

Try a custom format of:

#,##0.00;-#,##0.00;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Lindy]

Thank you so much. Just what I wanted.
Lindy

Try a custom format of:

#,##0.00;-#,##0.00;

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Glad that it worked for you. Thanks fot the feedback.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk