=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and to
still work when some of the numbers are less than 3 numbers in length (there
is text as well in some cells thats why I have limited it to 3 numbers.)

ATishoo,

I would use a column of helper formulas: extract the leading numbers using this array formula in J18

=LEFT(I18,MIN(IF(CODE(MID(I18,ROW(INDIRECT("A1:A"& LEN(I18))),1))57,ROW(INDIRECT("A1:A"&LEN(I18)))))-1)*1

copied to J30, then use this array formula to get the minimum:

=MIN(IF(ISERROR(J18:J30),"",J18:J30))

HTH,
Bernie
MS Excel MVP


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and to
still work when some of the numbers are less than 3 numbers in length (there
is text as well in some cells thats why I have limited it to 3 numbers.)

This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and
to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3 numbers.)

Rick,

That won't find numbers that are less than 3 numbers in length:

But using this will: it isn't flexible (in terms of maximum digits possible) but only the OP can
tell if it works. Array entered as well

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),"")))),1)

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in message
...
This array-entered** formula should get you the smallest value in your range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and to
still work when some of the numbers are less than 3 numbers in length (there
is text as well in some cells thats why I have limited it to 3 numbers.)

One of these days I am going to have to learn how to read.<g Yep! His post
sure does say there can be less than 3 leading digits. You formula is how I
would have done it had I read the post fully... except I think to match the
OP's request, shouldn't you start with LEFT(I18:I30,3), not LEFT(I18:I30,4)?

Rick


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

That won't find numbers that are less than 3 numbers in length:

But using this will: it isn't flexible (in terms of maximum digits
possible) but only the OP can tell if it works. Array entered as well

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),"")))),1)

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3 numbers.)

Rick,

I was just expanding it to show the pattern, since the OP seemed to imply that some were longer than
3 digits, talking about limiting it... Anyway, there is no harm in checking the first four to see
if they are numeric - if there is a letter within those 4, it goes to the first 3, then 2, then 1,
then ignores it altogether.

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in message
...
One of these days I am going to have to learn how to read.<g Yep! His post sure does say there
can be less than 3 leading digits. You formula is how I would have done it had I read the post
fully... except I think to match the OP's request, shouldn't you start with LEFT(I18:I30,3), not
LEFT(I18:I30,4)?

Rick


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

That won't find numbers that are less than 3 numbers in length:

But using this will: it isn't flexible (in terms of maximum digits possible) but only the OP can
tell if it works. Array entered as well

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),"")))),1)

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in message
...
This array-entered** formula should get you the smallest value in your range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and to
still work when some of the numbers are less than 3 numbers in length (there
is text as well in some cells thats why I have limited it to 3 numbers.)

I was just wondering about the extra function call and, although the OP's
post probably rules this out, the possibility of an incorrect result if all
cell entries had 4 or more leading digits (I would assume the OP only wants
to look at a maximum of the first 3 digits no matter matter what follows
them... letters or numbers).

Rick


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

I was just expanding it to show the pattern, since the OP seemed to imply
that some were longer than 3 digits, talking about limiting it... Anyway,
there is no harm in checking the first four to see if they are numeric -
if there is a letter within those 4, it goes to the first 3, then 2, then
1, then ignores it altogether.

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
One of these days I am going to have to learn how to read.<g Yep! His
post sure does say there can be less than 3 leading digits. You formula
is how I would have done it had I read the post fully... except I think
to match the OP's request, shouldn't you start with LEFT(I18:I30,3), not
LEFT(I18:I30,4)?

Rick


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Rick,

That won't find numbers that are less than 3 numbers in length:

But using this will: it isn't flexible (in terms of maximum digits
possible) but only the OP can tell if it works. Array entered as well

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),"")))),1)

HTH,
Bernie
MS Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3
numbers.)

brilliant Rick thanks!!
is there an easy way to add to that to return the entire contents of the
cell containing the smallest number?

"Rick Rothstein (MVP - VB)" wrote:

This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30 and
to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3 numbers.)

Well, as Bernie pointed out, the formula I posted only works if there are 3
or more digits in the beginning of your cell entries; however, you said (and
I overlooked this when I first read your post) that "some of the numbers are
less than 3 numbers in length". So I am thinking, based on this, that my
formula will not handle all the cases you said it would have to. Which is
correct... you only have 3 or more lead digits or you can have less than 3
lead digits?

Rick


"Atishoo" wrote in message
...
brilliant Rick thanks!!
is there an easy way to add to that to return the entire contents of the
cell containing the smallest number?

"Rick Rothstein (MVP - VB)" wrote:

This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and
to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3
numbers.)

wow fascinating conversation about this blinkin small function but I owe you
and bernie an appology as i have not explained myself well at all! again!

what I have is a page that contains days of the week across the top and
client names down the side! I then enter staff names and times and actions
into the grid!

I have a set of seperate sheets for each staff member again with days of the
week across the top! I want to display the client name and the time they are
visting in time order (a diary) for each staff member.

I have filtered out each individuals client vists using if function in a
nother grid on an unused area of the staff diary sheet:

=IF(ISERROR(SEARCH(staff1,'Main Board'!G7)),"",'Main Board'!F7&" "&'Main
Board'!E7&" "&'Main Board'!F8)

this then returns a set of single strings beginning with a time eg
9.30, christopher B, Budgeting. or completely empty cells if there is
nothing enetered or cells without a time if they havent got a fixed time.

so the time could be 3 or 4 digits witha decimal point.

i was thinking of using the small function to return the contents of the
cell with the earliest time then the second earliest time etc but hit
problems with empty cells, cells with no time and with returning the entire
contents of the cell not just the lowest number!

(Ill catch my breath after that long winded explination) ill tell you what
though its gonna be a kick ass system when finished I have got it up on a
touch sensative smart board and its fantastic to play with!

"Rick Rothstein (MVP - VB)" wrote:

Well, as Bernie pointed out, the formula I posted only works if there are 3
or more digits in the beginning of your cell entries; however, you said (and
I overlooked this when I first read your post) that "some of the numbers are
less than 3 numbers in length". So I am thinking, based on this, that my
formula will not handle all the cases you said it would have to. Which is
correct... you only have 3 or more lead digits or you can have less than 3
lead digits?

Rick


"Atishoo" wrote in message
...
brilliant Rick thanks!!
is there an easy way to add to that to return the entire contents of the
cell containing the smallest number?

"Rick Rothstein (MVP - VB)" wrote:

This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and
to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3
numbers.)

Do you need further help with the function? Clearly, you may need 5 digits, for a time like
10.30....

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,5)),--LEFT(I18:I30,5),IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),IF(ISNUMBER(--LEFT(I18:I30,2)),--LEFT(I18:I30,2),IF(ISNUMBER(--LEFT(I18:I30,1)),--LEFT(I18:I30,1),""))))),1)


HTH,
Bernie
MS Excel MVP


"Atishoo" wrote in message
...
wow fascinating conversation about this blinkin small function but I owe you
and bernie an appology as i have not explained myself well at all! again!

what I have is a page that contains days of the week across the top and
client names down the side! I then enter staff names and times and actions
into the grid!

I have a set of seperate sheets for each staff member again with days of the
week across the top! I want to display the client name and the time they are
visting in time order (a diary) for each staff member.

I have filtered out each individuals client vists using if function in a
nother grid on an unused area of the staff diary sheet:

=IF(ISERROR(SEARCH(staff1,'Main Board'!G7)),"",'Main Board'!F7&" "&'Main
Board'!E7&" "&'Main Board'!F8)

this then returns a set of single strings beginning with a time eg
9.30, christopher B, Budgeting. or completely empty cells if there is
nothing enetered or cells without a time if they havent got a fixed time.

so the time could be 3 or 4 digits witha decimal point.

i was thinking of using the small function to return the contents of the
cell with the earliest time then the second earliest time etc but hit
problems with empty cells, cells with no time and with returning the entire
contents of the cell not just the lowest number!

(Ill catch my breath after that long winded explination) ill tell you what
though its gonna be a kick ass system when finished I have got it up on a
touch sensative smart board and its fantastic to play with!

"Rick Rothstein (MVP - VB)" wrote:

Well, as Bernie pointed out, the formula I posted only works if there are 3
or more digits in the beginning of your cell entries; however, you said (and
I overlooked this when I first read your post) that "some of the numbers are
less than 3 numbers in length". So I am thinking, based on this, that my
formula will not handle all the cases you said it would have to. Which is
correct... you only have 3 or more lead digits or you can have less than 3
lead digits?

Rick


"Atishoo" wrote in message
...
brilliant Rick thanks!!
is there an easy way to add to that to return the entire contents of the
cell containing the smallest number?

"Rick Rothstein (MVP - VB)" wrote:

This array-entered** formula should get you the smallest value in your
range...

=SMALL(IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3),""),1)

**commit this formula using Ctrl+Shift+Enter, not just Enter by itself

Rick


"Atishoo" wrote in message
...
=SMALL(--LEFT(I18:I30,3),1)

how do I get this function to ignore empty cells in the range I18:I30
and
to
still work when some of the numbers are less than 3 numbers in length
(there
is text as well in some cells thats why I have limited it to 3
numbers.)