Second lowest in 28

[Guest]

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.

 

[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

 

[Guest]

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?

 

[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

 

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

 

[Guest]

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.

 

[Domenic]

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.

Have you tried the solution I offered?

 

[Biff]

Works for me!

Biff

-----Original Message-----

 

[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-----

 

[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.

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

 

[Guest]

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?

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.

 

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3

 

[Domenic]

I'm not sure why you're getting #N/A. Or are you actually getting a
#NUM! error?