Many thanks Pete I have been entering using cse still not getting any joy
using the following formula

{=SMALL(LEFT(B18:B30,4),3)}

"Pete_UK" wrote:

Here is one of my standard replies for array formulae:

* Array formulae need to be committed using CTRL-SHIFT-ENTER (CSE)
rather than the normal ENTER. If you do this correctly then Excel
will
wrap curly braces around the formula when viewed in the formula bar -
do not type these yourself. If you edit the formula you will need to
use CSE again.

Hope this helps.

Pete


On Jun 26, 10:19 am, Atishoo
wrote:
I thought an array formula represented the range as "B1*B2* etc!
have tried representing the range as B18:B30 as you suggest but this just
returns #value even on a simple range just containing numbers, am scratching
head!



"Bob Phillips" wrote:
Why do you have *, don't you want the 3rd smallest?

You must mean

=SMALL(--(LEFT(B18:B30,4)),3)

As I said it is an array formula.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
so as an array should it not be:
=SMALL(LEFT(B18*B19*B20*B21*B22*B23*B24*B25*B26*B2 7*B28*B29*B30,4),3)

it dont wanna work for me the swine

"Bob Phillips" wrote:

It can be applied to a range of cells in an array formula.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
Hi bob
thanks for that!
Am still strugling a bit as far as i can tell the left function can
only
be
applied to a single cell and not a range whereas im using the small
function
to return the contents of the cell with the lowest time etc, It also
falls
foul where there is no time and it tries to apply small to a text
string
instead!
any inspiration?

"Bob Phillips" wrote:

Extract the time with

=--LEFT(A2,FIND(" ",A2)-1)

and use SMALL on that.

--
__________________________________
HTH

Bob

"Atishoo" wrote in message
...
I have a range of cells that are a mix of blank cells and cells
containing
a
time, name and purpose eg "10.30 John S Golf"
there should be no more than 10 cells containing text.
i want a range of cells say b2:b12 to represent this information in
time
order
I have tried using the small function but this will only work for
cells
containing numbers only.
Some cells will contain text only with no time I want those to
appear
either
at the end or the start of the list.
i think this will require a combination of the search if and small
functions
but not sure quite how!!
Any suggestions gratefully received.- Hide quoted text -

- Show quoted text -