Build 'show 0 as -' into existing formula

[CW]

Hi All,

I have a working formula as follows:
=SUMPRODUCT(('Timelines Data'!B4:B52="Modelling")*('Timelines
Data'!D452<=TODAY())*('Timelines Data'!E4:E52>=TODAY()))

This returns the number of projects active in the 'Modelling' phase. The
results table is large and to make it more readable I would like to show '0'
as '-'.

Would it be possible to show how I could build this is or is there an
alternative option.

Thanks
Carol

 

[ozgrid.com]

Custom format the cell as: General;-General;"-"
http://www.ozgrid.com/Excel/CustomFormats.htm

 

[Jarek Kujawa]

Excel 2003

1. Format->Cells->Custom and define an own format like this: # ##0;-#
##0;-
(mind the separator might be , instead of ; as I'm using a national
version of Excel)

2. to make the worksheet more readable you may also hide zeros Tool-

Options->View and uncheck Zero Values

HIH

 

[CW]

Thanks Dave, that's great. I never knew you could do that, very simple.
Thank you!

 

[CW]

Hi Dave

Is it also possible for this to work with dates? So for example I have a
column that picks up a date from another sheet e.g. '=Data1!D43'. The data
should be shown as a date however if field in the other sheet is blank the
value defaults to '0-Jan'.

Could a similar solution work for this?

Thanks
Carol

 

[ozgrid.com]

Use: General;-General; and the 0 date will not show.

 

[Jacob Skaria]

Use the custom format to design a format in which you want to display the
date as below...

dd-mmm-yyyy;;"-"
OR
dd-mmm-yyyy;;

 

[ozgrid.com]

To hide all zeros, there's an option not to display them under Excel
Options.

 

[CW]

Thanks Jarek, unchecking zero values does the job for what I need.