Second lowest in 28

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

[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

>-----Original Message-----
>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.

>
>.
>

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

"Biff" wrote:

> 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
>
> >-----Original Message-----
> >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]

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

>-----Original Message-----
>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" wrote:
>
>> 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
>>
>> >-----Original Message-----
>> >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.
>>
>> >
>> >.
>> >
>>
>.
>

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

In article <[email protected]>,
"sac73" <[email protected]> wrote:

> 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" wrote:
>
> > 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
> >
> > >-----Original Message-----
> > >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.
> >
> > >
> > >.
> > >
> >

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

"Biff" wrote:

> 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
>
> >-----Original Message-----
> >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" wrote:
> >
> >> 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
> >>
> >> >-----Original Message-----
> >> >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.
> >>
> >> >
> >> >.
> >> >
> >>
> >.
> >
>

[Domenic]

In article <[email protected]>,
"sac73" <[email protected]> wrote:

> 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-----
>In article <E4965B9D-9188-451D-B1C9-
[email protected]>,
> "sac73" <[email protected]> wrote:
>
>> 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]

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-----
>In article <E4965B9D-9188-451D-B1C9-
[email protected]>,
> "sac73" <[email protected]> wrote:
>
>> 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?
>.
>

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

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> 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-----
> >In article <E4965B9D-9188-451D-B1C9-
> [email protected]>,
> > "sac73" <[email protected]> wrote:
> >
> >> 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?
> >.
> >

[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" wrote:

> 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.
>
> In article <[email protected]>,
> "Biff" <[email protected]> wrote:
>
> > 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-----
> > >In article <E4965B9D-9188-451D-B1C9-
> > [email protected]>,
> > > "sac73" <[email protected]> wrote:
> > >
> > >> 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?
> > >.
> > >
>

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3


"sac73" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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
> >
> > >-----Original Message-----
> > >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.
> >
> > >
> > >.
> > >
> >

[Domenic]

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

In article <[email protected]>,
"sac73" <[email protected]> wrote:

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