Second lowest in 28

S

sac73

Thanks to 'Biff', I was able to get the lowest result based on the previous
28 cells... and I thought it would be easy to convert that array so that I
could also get the second lowest in the same 28 cells. Not quite as easy as
I thought.

Can anyone help?

The original was:

{=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<>"",ROW(1:68)),28)))}

and this gave me the lowest in the range.
 
B

Biff

Instead of using MIN, use SMALL:

This is the same as using MIN:

=SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<>"",ROW
(1:68)),28)),1)

For the second lowest:

=SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<>"",ROW
(1:68)),28)),2)

For the third lowest:

=SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<>"",ROW
(1:68)),28)),3)

Etc.,Etc..

These are also array formulas.

Biff
 
S

sac73

This works, but when I have multiple cells with the same entry.... (In my
first set, I have the same low number of 3226 - 3 times), I have to go and
ask for the 4th smallest, to get the actual 2nd lowest entry.

To cure this, would I have to put in multiple IF statements, or is there an
easier way?
 
B

Biff

Well, that's how SMALL and LARGE work if there are
duplicates.

What to do about it depends on what you're trying to do.
How many "next lowest" values do you want to extract?

Biff
 
D

Domenic

Try the following...

First define the following reference...

Insert > Name > Define

Name: Numbers

Refers to:
=INDEX(Sheet1!$W$1:$W$68,LARGE(IF(Sheet1!$W$1:$W$68<>"",ROW(Sheet1!$W$1:$
W$68)-ROW(Sheet1!$W$1)+1),28)):Sheet1!$W$68

Then, try the following formulas...

Y1: =SMALL(Numbers,1)

Y2, copied down:

=SMALL(IF((Numbers<>"")*(1-ISNUMBER(MATCH(Numbers,$Y$1:Y1,0))),Numbers),1
)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 
S

sac73

Just the lowest, and the second lowest.... Problem is, although almost
impossible, all 28 could be the low number.... More often than not, there
are 2 or 3 multiple 'lowest' results.
 
D

Domenic

sac73 said:
Just the lowest, and the second lowest.... Problem is, although almost
impossible, all 28 could be the low number.... More often than not, there
are 2 or 3 multiple 'lowest' results.
Click to expand...

Have you tried the solution I offered?
 
B

Biff

Domenic, just to FYI,

This OP first posted about a week ago.

He has a range of cells 5 columns by 15 rows and within
that range are random blank cells (formula blanks, "").

The OP wanted the MIN of the last 28 cells in the range
that have a number in them. I gave him an Offset formula
that puts the multi column multi row range into a single
column range. That way, it would be easy to get the MIN.

Biff
-----Original Message-----
Click to expand...
 
D

Domenic

Thanks Biff!

Yes, I missed the OP's first post.

Also, thanks for confirming for me that the solution I offered works.
It's nice to know. :)

Biff said:
Domenic, just to FYI,

This OP first posted about a week ago.

He has a range of cells 5 columns by 15 rows and within
that range are random blank cells (formula blanks, "").

The OP wanted the MIN of the last 28 cells in the range
that have a number in them. I gave him an Offset formula
that puts the multi column multi row range into a single
column range. That way, it would be easy to get the MIN.

Biff
Click to expand...
 
S

sac73

Actually the range is quite a bit bigger. It's from H4:K57, but with the
offset formula from Biff, it covers W4:W263. (H1:K3 are not used, and there
is a break from H16:K17)

I have tried your suggestion, but I am getting a #N/A in cell Y2. What am I
doing wrong?

Domenic said:
Thanks Biff!

Yes, I missed the OP's first post.

Also, thanks for confirming for me that the solution I offered works.
It's nice to know. :)
Click to expand...
 
C

CLR

Something to ponder............

=IF(SMALL(therange,2)=MIN(therange),SMALL(therange,4),SMALL(therange,2))
etc, etc........

Vaya con Dios,
Chuck, CABGx3
 
Top