make cell show blank when total is zero

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Hi
goto 'Format - Cells'. Choose custom and enter this format in the
textbox on the right side
 
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
 
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.
 
jtinne said:
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?
Click to expand...

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 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!
Click to expand...

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

How would I do this?
 
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.
 
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
 
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
 
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
 
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
 
Thank you so much. Just what I wanted.
Lindy

Sandy Mann said:
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
Click to expand...
 
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
 
Back
Top