Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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/sh...ad.php?t=98209 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
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/sh...ad.php?t=98209 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
The first cell is the following: =SMALL(Sheet1!F3:F35,1) After that it is: =IF(MIN(IF(Sheet1!$F$3:$F$35C27,Sheet1!$F$3:$F$35 ))=0,"",MIN(IF(Sheet1!$F$3:$F$35C27,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. 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/sh...ad.php?t=98209 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
Replace this formula = SMALL(Sheet1!F3:F35,1) with below formula =IF(ISERROR(SMALL(Sheet1!F3:F35,1)),"",SMALL(Sheet 1!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$35C27,Sheet1!$F$3:$F$35 ))=0,"",MIN(IF(Sheet1!$F$3:$F$35C27,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. 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/sh...ad.php?t=98209 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
I change the formala from the second cell as follows: =IF(ISERROR(IF(MIN(IF(Sheet1!$F$3:$F$35C27,Sheet1 !$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35C27,Sh eet1!$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$35C27,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/sh...ad.php?t=98209 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
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/sh...ad.php?t=98209 |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
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/sh...ad.php?t=98209 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
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/sh...ad.php?t=98209 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Custum Cell Format - display 0 (zero) when cell is empty
Replace "" with "0" and see threresult .... IF(ISERROR(IF(MIN(IF(Sheet1!$F$3:$F$35C27,Sheet1! $F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35C27,She et1!$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$35C27,Sheet1 !$F$3:$F$35))=0,"",MIN(IF(Sheet1!$F$3:$F$35C27,Sh eet1!$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$35C27,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/sh...ad.php?t=98209 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display Empty Cell When Using SUM formula | Excel Worksheet Functions | |||
format a cell with a formula so an empty reference cell shows blan | Excel Discussion (Misc queries) | |||
Custom format that shows blank cell if another cell is empty | Excel Discussion (Misc queries) | |||
How do I set a cell to "Empty" so that it does not display in a ch | Charts and Charting in Excel | |||
Display a dialog box if cell is empty | Excel Discussion (Misc queries) |