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

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

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

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

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

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

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

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

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