Build 'show 0 as -' into existing formula

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

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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


On 29 Kwi, 10:18, CW wrote:
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

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

"ozgrid.com" wrote:

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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" wrote:

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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


--
Regards
Dave Hawley
www.ozgrid.com
"CW" wrote in message
...
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" wrote:

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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;;


--
Jacob (MVP - Excel)


"CW" wrote:

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" wrote:

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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



--
Regards
Dave Hawley
www.ozgrid.com
"ozgrid.com" wrote in message
...
Use: General;-General; and the 0 date will not show.


--
Regards
Dave Hawley
www.ozgrid.com
"CW" wrote in message
...
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" wrote:

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


--
Regards
Dave Hawley
www.ozgrid.com


"CW" wrote in message
...
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

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

"Jarek Kujawa" wrote:

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


On 29 Kwi, 10:18, CW wrote:
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

.