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" <(E-Mail Removed)> wrote in message
news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
> 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 <C...@discussions.microsoft.com> 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" <(E-Mail Removed)> wrote in message
> news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:1FC04841-17BE-4B60-90CD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
> > news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
news:54D1B50B-C5E2-4F6D-8114-(E-Mail Removed)...
> Use: General;-General; and the 0 date will not show.
>
>
> --
> Regards
> Dave Hawley
> www.ozgrid.com
> "CW" <(E-Mail Removed)> wrote in message
> news:1FC04841-17BE-4B60-90CD-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:62B5E705-0FD4-4884-8FC0-(E-Mail Removed)...
>>> > 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 <C...@discussions.microsoft.com> 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
>
> .
>