array formula

[bill]

I have an array formula as follows (excell 2000)
=IF(COUNT(IF(st5!$AW$1:$AW$1800=D60,st5!$BB$1:$BB$1800))

=ROW(st5!$1:$1),INDEX(st5!$BB$1:$BB$1800,SMALL(IF(st5!

$AW$1:$AW$1800=D60,ROW(st5!$AW$1:$AW$1800)),ROW(st5!
$1:$1))),"")
When there is no value in the cell it shows an " 0 " is
there a way to show a blank cell instead?

please help. thank you all!

Rgd Bill

 

[dcronje]

Why not use conditional formatting?

Where cell value equals zero set font colour to same colour as th
background.

Hope this help

 

[Jack Schitt]

I should think that the simplest solution is to use custom number format so
that a zero value is hidden.
Would that suffice?
In custom number format, by default the third field (fields normally
separated by ";" specifies the presentation of zero value, ie

#,##0_);[Red](#,##0)
would display the zero, but
#,##0_);[Red](#,##0);
would not

 

[hgrove]

bill wrote...

I have an array formula as follows (excell 2000)
=IF(COUNT(IF(st5!$AW$1:$AW$1800=D60,st5!$BB$1:$BB$1800))
=ROW(st5!$1:$1),INDEX(st5!$BB$1:$BB$1800,
SMALL(IF(st5!$AW$1:$AW$1800=D60,ROW(st5!$AW$1:$AW$1800)),
ROW(st5!$1:$1))),"")
When there is no value in the cell it shows an " 0 " is there a way t show a
blank cell instead?

=IF(
COUNT(
IF(
st5!$AW$1:$AW$1800=D60,
st5!$BB$1:$BB$1800
)
)=ROW(st5!$1:$1),
INDEX(
st5!$BB$1:$BB$1800,
SMALL(
IF(
st5!$AW$1:$AW$1800=D60,
ROW(st5!$AW$1:$AW$1800)
),
ROW(st5!$1:$1)
)
),
""
)


First off, I can't see why you're using ROW(st5!$1:$1) rather than jus
1. This ROW call will return 1, and because its range argument i
row-absolute, if you copy the formula to any other cell it'll remai
unchanged, so always return 1.


Next, the expression

SMALL(IF(st5!$AW$1:$AW$1800=D60,ROW(st5!$AW$1:$AW$1800)),
ROW(st5!$1:$1))

which would reduce to

SMALL(IF(st5!$AW$1:$AW$1800=D60,ROW(st5!$AW$1:$AW$1800)),1)

would always return the same result as the simpler

MATCH(D60,st5!$AW$1:$AW$1800,0)

except when there were no matches at all for D60 in st5!AW1:AW1800, i
which case they'd return different error values (#NUM! vs #N/A)
However, since column AW precedes column BB, you could replace th
whole INDEX call with

VLOOKUP(D60,st5!$AW$1:$BB$1800,6,0)


Finally, I'd guess the problem is that you were expecting

COUNT(IF(st5!$AW$1:$AW$1800=D60,st5!$BB$1:$BB$1800))

to return 0 if the cells in st5!AW1:AW1800 that match D60 corresponde
to cells in st5!BB1:BB1800 that were blank. That's not how the *IF
function works. When you call IF like

IF({TRUE;FALSE;TRUE},A1:A3)

it does *NOT* return range references to A1 and A3 in 1st and 3r
entries and FALSE in the 2nd entry, it returns the *VALUES* of cells A
and A3 with the caveat that blank cells are converted to numeric zeros
So the return value from this simplified IF call if A1:A3 were al
blank would be {0;FALSE;0}, and that would have count 2. What you nee
is

SUMPRODUCT((st5!$AW$1:$AW$1800=D60)*ISNUMBER(st5!$BB$1:$BB$1800))


Put it all together, and you could rewrite your array formula as th
nonarray formula

=IF(SUMPRODUCT((st5!$AW$1:$AW$1800=D60)
*ISNUMBER(st5!$BB$1:$BB$1800)),VLOOKUP(D60,st5!$AW$1:$BB$1800,6,0),"")

This would allow you to display numeric zeros if the st5!BB cel
corresponding to the topmost match for D60 in st5!AW evaluated to zero
which the conditional formatting suggestions you've received from other
would fail to do. Better to deal with the cause than just treat th
symptoms