small function

A

Atishoo

=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.)
 
B

Bernie Deitrick

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
B

Bernie Deitrick

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
 
A

Atishoo

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

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
B

Bernie Deitrick

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
A

Atishoo

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!
 
B

Bernie Deitrick

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
 
A

Atishoo

Bernie you are a genious!! it works and it can handle empty cells and
everything!! I dont need anymore than 4 digits because the times are limited
to 10 minute intervals eg 10.30, 10.40 so 10.4 and 10.3 is sufficient to
differentiate!! thanks!!
Do you happen to know how it might go further and return the entire contents
of the cell containing the earliest time?? ( as the cell contains the full
information I require eg "10.30 peter f cooking")
thanks John
ps do you want to see the finished full thing its pretty amazing now not to
mention big for a spreadsheet (5.4MB) well big by my standards!!
 
B

Bernie Deitrick

=INDEX(I18:I30,MATCH(SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3))),1),IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3)))))

Array entered.....

HTH,
Bernie
MS Excel MVP
 
A

Atishoo

Thanks Bernie!
It works but I am a bit baffled! I have used your formula in a cell with the
cell below repeating the formula but changing K to 2 to get the second
highest then to 3 and so on to get a list in time order but this only seems
to work if the list in range I18:I30 is already in time order itself! if it
is out of order it seems to just repeat the second highest value or similar!
very odd
 
B

Bernie Deitrick

Sorry, my bad. My list was sorted, so I forgot to add a false to require an exact match, which is
why you got your odd results:

=INDEX(I18:I30,MATCH(SMALL(IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3))),1),IF(ISNUMBER(--LEFT(I18:I30,4)),--LEFT(I18:I30,4),IF(ISNUMBER(--LEFT(I18:I30,3)),--LEFT(I18:I30,3))),FALSE))

HTH,
Bernie
MS Excel MVP
 
A

Atishoo

Not bad at all Bernie!! absolute genious in fact it all works like a dream
and my boss wonders how I came to be a good programmer as well as a nurse!!
Shhhh lets not tell her I had help! Thanks Bernie
 
B

Bernie Deitrick

Not bad at all Bernie!! absolute genious in fact...
Click to expand...

I'm only sorry that it's taken you so long to realize that! ;-)
Shhhh lets not tell her I had help! Thanks Bernie
Click to expand...

You're quite welcome. Thanks you letting me know that you got it to work.

Bernie
 
Top