Display nothing in a cell if value = 0

D

Dom Sinclair

I have a range of cells formatted to display numbers in currency format.
They have a formula applied to them so if the value of the Cell is nothing I
get £0.00 showing.

I would rather that nothing was shown in these instances. How is this
achieved?

Thanks

Dom
 
B

Bob Phillips

Couple of ways,

use conditional formatting and display with a white font if the value is
zero

change the display option to suppress zeroes, Tools>Options>General and
uncheck the Zero Values option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
A

Andy B

Or you could use a custom format

--
Andy.


Bob Phillips said:
Couple of ways,

use conditional formatting and display with a white font if the value is
zero

change the display option to suppress zeroes, Tools>Options>General and
uncheck the Zero Values option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

nothing
Click to expand...
 
D

Dom Sinclair

Thank you so much. Just the ticket

Dom
Bob Phillips said:
Couple of ways,

use conditional formatting and display with a white font if the value is
zero

change the display option to suppress zeroes, Tools>Options>General and
uncheck the Zero Values option.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

nothing
Click to expand...
 
L

Lise

What if i have a drop down menu and in order for it to add
up my cells using "sumproduct" formula how do i set the
None $0 to not show up but be able to add totals so that i
do not get an #Value! error?

Lise
 
B

Bob Phillips

I knew there were 3 ways, but couldn't recall the third<g>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Any of the 3 ways indicated here, as there will still; be 0 in the cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

What if i have a drop down menu and in order for it to add
up my cells using "sumproduct" formula how do i set the
None $0 to not show up but be able to add totals so that i
do not get an #Value! error?

Lise
 
F

Frank Kabel

Hi
you may post your current formula

--
Regards
Frank Kabel
Frankfurt, Germany

What if i have a drop down menu and in order for it to add
up my cells using "sumproduct" formula how do i set the
None $0 to not show up but be able to add totals so that i
do not get an #Value! error?

Lise
 
L

Lise

=sumproduct(mid(b11:b15,FIND("$",b11:b15),255)*1)
-----Original Message-----
Any of the 3 ways indicated here, as there will still; be 0 in the cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

What if i have a drop down menu and in order for it to add
up my cells using "sumproduct" formula how do i set the
None $0 to not show up but be able to add totals so that i
do not get an #Value! error?

Lise


.
Click to expand...
 
Top