multiple arrays for SMALL command

C

Chickalett

How do I use the SMALL command when I want it to look at values liste
on different sheets? The problem is that it won't let me list more tha
one array like the minimum command allows me. Or is there some way t
list cell numbers inside a bracketed array instead of values? Fo
example, I can do:
=SMALL({6,23,5,2.3}, 2)
but I can't do:
=SMALL({A3,C5,D18,D21}, 2)
which is what I need to be able to do.
Is there another command I can use
 
A

Alan Beban

Chickalett said:
How do I use the SMALL command when I want it to look at values listed
on different sheets? The problem is that it won't let me list more than
one array like the minimum command allows me. Or is there some way to
list cell numbers inside a bracketed array instead of values? For
example, I can do:
=SMALL({6,23,5,2.3}, 2)
but I can't do:
=SMALL({A3,C5,D18,D21}, 2)
which is what I need to be able to do.
Is there another command I can use?
Click to expand...
Use parentheses instead of braces.

Alan Beban
 
C

Chickalett

=SMALL((A3,C5,D18,D21), 2)
Click to expand...
Thanks,
What if my values are on different sheets, like for instance:
=SMALL((A3,C5,D18,Sheet2!A2,Sheet3!A1), 2)
It doesn't seem to work then
 
C

Chickalett

Alan said:
Chickalett wrote:
How do I use the SMALL command when I want it to look at value
listed
on different sheets? The problem is that it won't let me list mor
than
one array like the minimum command allows me. Or is there some way to
list cell numbers inside a bracketed array instead of values? For
example, I can do:
=SMALL({6,23,5,2.3}, 2)
but I can't do:
=SMALL({A3,C5,D18,D21}, 2)
which is what I need to be able to do.
Is there another command I can use?


Use parentheses instead of braces.

Alan Beban
Click to expand...


Thanks,
What if my values are on different sheets, like for instance:
=SMALL((A3,C5,D18,Sheet2!A2,Sheet3!A1), 2)
It doesn't seem to work then
 
A

Aladin Akyurek

If you download and install the morefunc.xll add-in (try google to
locate)...

=SMALL(ARRAY.JOIN(A3,C5,D18,Sheet2!A2,Sheet3!A1), 2)
 
Top