Custum Cell Format - display 0 (zero) when cell is empty

I have an array formula which automatically summarizes all available
values of a certain column, in ascending order. The summary is fixed to
a maximum of 8 cells and in many cases it uses less than the available 8
columns.

Is it possible to format the cells by showing a 0 when the result/cell
is empty

In other cells in the sheet the cells show empty instead of a zero by
using the custom format of

0;-0;""

Now I want an empty cell to show 0


Thanks,

W


-- 
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/19/2009 9:19:57 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
584 Views

Similar Articles

[PageSpeed] 1

willemeulen;350940 Wrote: 
> I have an array formula which automatically summarizes all available
> values of a certain column, in ascending order. The summary is fixed to
> a maximum of 8 cells and in many cases it uses less than the available 8
> columns.
> 
> Is it possible to format the cells by showing a 0 when the result/cell
> is empty
> 
> In other cells in the sheet the cells show empty instead of a zero by
> using the custom format of
> 
> 0;-0;""
> 
> Now I want an empty cell to show 0
> 
> 
> Thanks,
> 
> WWillem, can you supply your array formula?


-- 
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 6:00:57 AM
The first cell is the following:
=SMALL(Sheet1!F3:F35,1)

After that it is:
=IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35
)))

My problem is when there is no value in the cell another formula using
this cell to lookup a small table cant find any result and displays
#N/A; I don't like this to be displayed but mainly when the summary
table contains #N/A the totals change into #N/A as well.:mad:

W


-- 
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 7:50:53 AM
Replace this formula = SMALL(Sheet1!F3:F35,1) with below formula 

=IF(ISERROR(SMALL(Sheet1!F3:F35,1)),"",SMALL(Sheet1!F3:F35,1))




willemeulen;351291 Wrote: 
> The first cell is the following:
> =SMALL(Sheet1!F3:F35,1)
> After that it is:
> =IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35
> )))
> My problem is when there is no value in the cell another formula using
> this cell to lookup a small table cant find any result and displays
> #N/A; I don't like this to be displayed but mainly when the summary
> table contains #N/A the totals change into #N/A as well.:mad:
> W


-- 
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 8:32:33 AM
I change the formala from the second cell as follows:

=IF(ISERROR(IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35)))),"",IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))))

But there are no 0 (zero's) displayed.

Is it not possible with the custom format of the cell, as this will not
affect the formula at all, or will excell still read nothing?


-- 
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 8:57:05 AM
Hi,
could you add a small sample of your data and an example of what you
are trying to do?

Attatchments.

To upload a workbook, click reply then add your few words, scroll down
past the submit button and you will see the Manage Attatchments button,
this is where you get to add files for upload, if you have any trouble
please use this  link or the one at the bottom of the
any page.


-- 
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 9:50:00 AM
Have a look at the attached immage.

The array formula I have moved on sheet2 because it doesn't accept to
be placed in merged cells. The answers are dicerctly coppied by
=sheet2!B20 etc back to sheet 1 into the merged cells. The formula
needly summarizes the diameters (numbers) used on the sheet in acsending
order (column F). 

My problem is the following:

When as is shown on the immage there are only 5 diameters used
(8,10,12,16 and 20) the remaining cells are empty. Together with R and Y
on the left the sumproduct function I calculate the total length, within
this cell the diameter is used to lookup the weight per length. Vlookup
cannot lookup an empty cell so the #N/A is displayed, this messes up my
totals which now also display as #N/A.

I would be able to solve the problem by retruning a 0 (zero) value in
either sheet1 or sheet2, in the lookup table I added the 0 diameter
which will retrun 0 (0 x 0 = 0). This way my summary table will stay in
tact. By using the custom format I mentioned in the first post of this
thread these 0 will not be displayed and will show nothing.


W:o:


+-------------------------------------------------------------------+
|Filename: immage 1.jpg                                             |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=141|
+-------------------------------------------------------------------+

-- 
willemeulen

Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands)
------------------------------------------------------------------------
willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 10:18:39 AM
Thx,
but an XL file would be much more useful than a picture :)


-- 
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/20/2009 11:03:50 AM
Replace "" with "0" and see threresult .... 


IF(ISERROR(IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35)))),"[/COLOR]",


willemeulen;351337 Wrote: 
> I change the formala from the second cell as follows:
> 
> =IF(ISERROR(IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35)))),"",IF(MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35>C27,Sheet1!$F$3:$F$35))))
> 
> But there are no 0 (zero's) displayed.
> 
> Is it not possible with the custom format of the cell, as this will not
> affect the formula at all, or will excell still read nothing?


-- 
mubashir aziz

If this post helps Don't 4get to click Yes
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=98209

0
5/21/2009 3:03:20 AM
Reply:

Similar Artilces:

Saving a scrollable image in bitmap and jpeg format
I have a picture control in which i am diaplaying images of various sizes if size go beyond the size of the dialog then scrollbars becomes active so view the hidden image. But when wriiten two functions to save that bitmap following problems occured: 1> For the first function it was saving only viewable area of teh bitmap 2> for second if i have specified the dimensions as 800 X 600 and picture control diamensions is 345 X 330 then it is taking area outside dialog to get image of the 800 X 600 How can i save the image with same dimensions with which i am displaying it ? Thanking in ant...

Populate cell with a value if another cell has a certain value
All, good morning. i have a issue, i need to populate cell E4 with a value CX/025966 when cell F4 has a text Bond Street. Is there a simple way of doin this? EXCEL 2007 Try:- =IF((F4="Bond Street"),"CX/025966","") If my comments have helped please hit Yes. Thanks. "B2ORL" wrote: > All, good morning. i have a issue, i need to populate cell E4 with a value > CX/025966 when cell F4 has a text Bond Street. > > Is there a simple way of doin this? Use IF() =IF(F4="Bond Street","CX/02...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Copying of non-consecutive, columnar data into consecutive-columnar cells
Hi: I have data located at a1:a9, then a:10 is blank; then, I have 9 more lines of data from a11:a19, and a20 is blank... a1, a11, a21...are all the texts/numbers that I want printed on a different page. But here's the catch: I'd like to print it at locations a1, a2, a3... Specifically, the data that I have appears like this on THIS IS ON SHEET 1: A1: prefix: Mr A2: emailAddress: john.smith@1.com A3: firstName: John A4: lastName: Smith A5: company: ABC Company A6: A7: prefix: Mrs A8: emailAddress: kim.davids@2.com A9: firstName: Kim A10: lastName: Davids A11: company: X...

Dynamic Image references in a cell
I am exporting a report from Access 2007 to Excel 2007 containing product information. I have an image for each product. Access does not export Images to Word or Excel. I have the URL or local path for the image standardized so the path is C;/temp/image[ID Num].jpg or http://www.temp.com/images/image[ID num].jpg. I have [ID num] in Column B for each row. I want to reference the image column A of each row. How do I get the image to display in the cell? -- Jim Fidler ...

MS CRM 3.0 - Availability of User for Assignment
Hello All, I am having this unusal requirement. I am having 3 Businsss Units with its User base. I have set Roles for all the Users from All the BU such that they can view/read Users from their own Business Units. So whenever User assigns the record they can assign it to any User withing their BU. However I have requirement where a specific User can be assinged to the record by Users in any BU. This user can be in any BU (it can have its own BU also). So special Incidents can be assigned to this Special User by Users in any BU. How to achieve this by Roles configuration? I tried all...

Assign a value to a cell
I'll apreciate your help with this in A1 i have a value in days that is used to make several calculations but I need it to change if a specific condition is given something lik this: A1=300 If b1<a1 then a1=b1 where b1 is a value calculated somewhere else. Thank you for your help. Rodolf -- Message posted from http://www.ExcelForum.com Hi try the following formula in A1: =MIN(B1,300) Frank > I'll apreciate your help with this > > in A1 i have a value in days that is used to make several > calculations, but I need it to change if a specific condition is > giv...

IF statement with FALSE value being text AND cell reference? #3
Once again, figured it out on my own. Oh, and thanks for the repl Frank. Needed to use the ROUND function: =IF(D9>1,D9*D8,"<" & ROUND((D9*D10),2) -- jcob ----------------------------------------------------------------------- jcob2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1560 View this thread: http://www.excelforum.com/showthread.php?threadid=27157 ...

What If or conditional formatting?
I am trying to create a spread sheet for company shirt orders. I don't know if this is possible, but I hope that it is. I have two cells. In the first cell I have a list of available shirts that a person can choose from. What I am looking to do is, when an individual selects a shirt, I want the adjacent cell to generate a list of available shirt colors based on the "Shirt" selection. Is this possible? Take a look at this: http://www.contextures.com/xlDataVal02.html -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes...

Check if data has already been formatted.
Hello, I have a sub() as part of a larger set of macros. This particular sub() will take the raw data pulled from an in-house reporting system and format it to suit our needs (adding a couple of columns with formulas to facilitate pivot table creation). This is the code as I have it so far: Sub FormatData() Application.ScreenUpdating = False ActiveWindow.DisplayGridlines = False ' Declarations Dim DataWks As Worksheet Dim LastRow As Long Dim LastCol As Long ' Set the DataWks variable Set DataWks = Worksheets("Data") ...

display result of formula
How do I make a cell which contains a formula display the result of that calculation rather than the formula (it is formatted as "number" and I have tried "recalculating") Peter Peter Chadbund expressed precisely : > How do I make a cell which contains a formula display the result of that > calculation rather than the formula (it is formatted as "number" and I have > tried "recalculating") > Peter Change the cell format to 'General'. Redo the formula. Change the cell format to 'Number' and apply your display preferenc...

I have a file that is 4mb. Cleared all formating...still big
I have a file that has cols a-p, rows 1-40. Had a lot of colors and stuff but I cleared all formatting and saved file. Still 4mb. Can't figure out where the issue is. Can anyone help? Hi Who knows what people do with spreadsheet sometime. I experienced once the situation where somebody put nonvisible large object so there was a spreadsheet with a simple calculations but it was huge. I found it and got rid off it, but I think that easier will be copy all to a new spreadhseet and save it. Click yes if helped -- Greatly appreciated Eva "JDH" wrote: ...

Display all messages?
Is there a way in Outlook 2002 to display all messages from all folders in one list? We have managed to end up with duplicate messages in multiple folders, and the best way to clear them out would be to see all messages together in one list sorted by subject or author. I can do that where I work with Lotus Notes, and I hope I can do that with Outlook, but I haven't found it. Thanks, Jack Jack B <jbruss@wi.rr.com> wrote: > Is there a way in Outlook 2002 to display all messages from all > folders in one list? We have managed to end up with duplicate > messages i...

Exporting Formats
I see QIF only. I wonder why not OFX. Probably unlikely it will be included in whenever the next release might be eh? Never mind, after reading a few other threads here I see it's been discontinued. ...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

Integrating Great Plains 8.0 and CRM 1.2 and Windows Small Busines
Integrating Great Plains 8.0 and CRM 1.2 I run a Windows Small Business Server 2003 Premium box along with a Windows Server 2003 Standard box used for my Great Plains 8.0 and SQL 2000. I’m considering introducing CRM 1.2 Professional into the mix. From a hardware stand point I know I’m fine all around. I’ve been researching how I should configure CRM and am a bit confused. The info I’ve found in KB887153 states “When you install Microsoft Business Solutions CRM Integrations for Great Plains 7.0, 7.5, or 8.0, Microsoft BizTalk Server is also installed. However, BizTalk Server 2000 and B...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

CRM 4.0 External Connector
Can anyone point me to CRM 4.0 External Connector installation guide? Cheers, Mandy. "Mandy" <Mandy@discussions.microsoft.com> wrote in message news:BF297AE0-9446-46CB-91FA-CFF19A65F1DF@microsoft.com... > Can anyone point me to CRM 4.0 External Connector installation guide? The external connector is a license so nothing you need to install. -- Robert MacLean http://www.sadev.co.za thanks for update. would you know if there's any specs on how it integrates with crm? "Robert MacLean" wrote: > "Mandy" <Mandy@discussions.microsoft.com&g...

Report Format
Env: CRM 3, VS 2003, remote connect to CRM server. I've created a QUOTE in VS2003 and when viewed using PREVIEW from within the VS environment it produces a beautiful QUOTE (at least in my opinion). When printed, again from the VS environment, it is perfect. When I upload the RDL file to the CRM server, create an new REPORT using this RDL then produce a REPORT, the formatting is all over the place. When I inspect the source (in VS) there are no fields that extend past the defined size. It appers that the CRM report engine randomly adds CRLFs all thru the report ... ??? ...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

modify linked cells without breaking link
I have a workbook with a number of worksheets (2003.) The 2nd and 3rd worksheets have cells that are linked to the 1st worksheet. This workbook will be used to schedule production. The 1st worksheet has a list of products that we produce. The 1st worksheet has a column for the min # of cases we need to keep in stock at all times and the max # of cases we must keep in stock at all times. The 1st worksheet also contains a column where the production scheduler would enter the actual # of cases in stock. The 2nd worksheet in the workbook takes the actual cs in stock and compares ...

Add data to cell w/o loosing initial data
I would like to know if there is a way to add data to data without retyping. For example I have a colum of 18015555555 and I want to add [rfax:(cell #)@/fn=(phone number)] So I would like to add the brackets - copy from a cell - @/fn= and not loose the data already in the spread sheet. Example 2. Add [rfax:company name@fn/=(saved data here) then close bracket. So I want to add data to cells without loosing the data already in the cells. I have about 600 of them to do and I really don't want to do each one by hand. Please let me know if anyone knows how to accomplish this. Tha...

OWA Premium Client Not Displaying Correctly
Hello, I wonder whether anyone has seen this problem before because i would really appreciate any help on this one please. I have six Exchange 2003 servers in my organisation and there are two Exchange servers in three sites. Four of the Exchange servers are running Windows Server 2003 and Exchange Server 2003 [With Exchange SP1]. The front-end server is running Windows Server 2003 SP1 and Exchange Server 2003 SP2. Users with mailboxes on those four Back-End Exchange servers do not have any problems using OWA. The two Exchange servers on my site are using Windows Server 2003 SP1 with Exc...