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

[willemeulen]

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

 

[Simon Lloyd]

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)

 

[willemeulen]

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.

W

 

[mubashir aziz]

Replace this formula = SMALL(Sheet1!F3:F35,1) with below formula

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

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.
W

 

[willemeulen]

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?

 

[Pecoflyer]

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.

 

[willemeulen]

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:


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

 

[Pecoflyer]

Thx,
but an XL file would be much more useful than a picture

 

[mubashir aziz]

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)))),"[/QUOTE]",

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?