how to make cell display something else for zero value?

[juntjoo]

Please Login or Register  to view this content.

If the cell it references on this other sheet doesn't happen to have anything in it, it displays zero. I'd rather it display something else that I tell it to in the event the cell is empty. I was thinking 'if(reference formula, returns nothing, then "- - -", otherwise reference formula)' which is repetitive thus long/inefficient for my taste. Was hoping there was an easier way to do this. Thanks

[CK76]

You can use custom number format

Something like... #,##0.00;-#,##0.00;"---";@

[k64]

2 options:
1. Format it with a custom format like

Please Login or Register  to view this content.

2. Replace the blank cells with a blank. Put ="" in a cell, and then copy and paste as values. Now use that cell to replace you blank cells so they have a BLANK in them.

If you want cells that actually have a 0 in them to be "- - -" use #1. If you don't, then #2.

[Speshul]

you could use an IF to do it as well,

Please Login or Register  to view this content.

Edit: didn't read whole post.

[juntjoo]

Thanks. what do all those symbols mean?

and how would I do this for a cell formatted for dates?

Thanks

[JeteMc]

... how would I do this for a cell formatted for dates?

Tested with version 2010 so I am only guessing that it would work for version 2007. Example of custom formatting that would display a date such as 1/1/2017 or, if the the source cell is blank, - - -

Please Login or Register  to view this content.

Let us know if you have any questions.

[juntjoo]

Tested with version 2010 so I am only guessing that it would work for version 2007. Example of custom formatting that would display a date such as 1/1/2017 or, if the the source cell is blank, - - -

Please Login or Register  to view this content.

Let us know if you have any questions.

Thanks. just tell me how it works so I don't have to keep asking this question in the future if you don't mind. How would I do it for ex. to have it blank if zero rather than the three hyphens? Thanks

[AliGW]

What it's saying is if the contents of the cell are in the format m/d/yyyy, then display the date. If not, display three hyphens.

Try changing it to this:

m/d/yyyy;m/d/yyyy;""

[Phuocam]

Or try this ...

=IFERROR(1/(1/VLOOKUP(…...)),"")

[JeteMc]

Another option for displaying a blank when VLOOKUP finds a zero value would be m/d/yyyy;;""
Excel stores dates as numbers and the options for custom formats follow the pattern postive format;negative format;zero format
Put the desired display format for positive numbers in front of the semi colon, format for negative numbers between semi colons and format for zero after the second semi colon. Since dates aren't negative you can leave that portion of the format unfilled.
Let us know if you have any questions.