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

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