small function
 

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.

small function
 

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.

small function
 

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.

small function
 

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.

small function
 

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.

small function
 

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.

small function
 

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.

small function
 

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 -

small function
 

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 -

small function
 

Ahhhhhh
it works!!! Sorry the problem was that a couple of cells in the range were
returning as #value so that was what the formula was returning.
but now I have a new problem !! i want the range to be sorted in order of
its time this just returns the time and not the rest of the contents of the
cell! i could use find to return the full cell contents based on the time
returned but thats messey! any better ideas??


"Atishoo" wrote:

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 -