If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#3
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#4
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#5
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#6
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#7
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#8
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 |
#9
|
|||
|
|||
Build 'show 0 as -' into existing formula
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 . |
Thread Tools | |
Display Modes | |
|
|