Thread: min and max in a sequence

Hi
I have in column " A" 500 rows with numbers in random sequences each
sequence has random number of rows , each sequence is seperated with a blank
cell (the result of a formula) some sequences have zerrows included.
I need to find the lowest number in each sequence and put that number in
column
" B" I also need to find the highest number and put that number in column "
C"
eg:- A B C
4 2 10
6
2
8
10
blank cell
7 7 50
9
11
13
50
18
21
30
15
blank cell
3 1 17
5
0
1
0
0
17
6

some sequences have two or more of the same numbers , in case of two ore
more of the same numbers only one is needed

can some one help me please

regards bill gras

--
bill gras

In row 1

Min: =MIN(A1:INDEX(A1:$A$40,MIN(IF(A1:$A$40="",ROW(A1:$A$40)))-1))
Max: =MAX(A1:INDEX($A1:$A$40,MIN(IF($A1:$A$40="",ROW($A1:$A$40)))-1))

subsequent rows
Min:
=IF(A1="",MIN(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")
Max:
=IF(A1="",MAX(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> Hi
> I have in column " A" 500 rows with numbers in random sequences each
> sequence has random number of rows , each sequence is seperated with a
blank
> cell (the result of a formula) some sequences have zerrows included.
> I need to find the lowest number in each sequence and put that number in
> column
> " B" I also need to find the highest number and put that number in column
"
> C"
> eg:- A B C
> 4 2 10
> 6
> 2
> 8
> 10
> blank cell
> 7 7 50
> 9
> 11
> 13
> 50
> 18
> 21
> 30
> 15
> blank cell
> 3 1 17
> 5
> 0
> 1
> 0
> 0
> 17
> 6
>
> some sequences have two or more of the same numbers , in case of two ore
> more of the same numbers only one is needed
>
> can some one help me please
>
> regards bill gras
>
> --
> bill gras

Bill,

Insert a blank row for row 1, then in B2, array enter the formula (enter using Ctrl-Shift-Enter)

=IF(A1="",MIN(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")

In C2, array enter
=IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")

Then copy down to match your data in column A.

Change the 500's to at least the number of rows that you actually have.

HTH,
Bernie
MS Excel MVP


"bill gras" <billgras@discussions.microsoft.com> wrote in message
news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> Hi
> I have in column " A" 500 rows with numbers in random sequences each
> sequence has random number of rows , each sequence is seperated with a blank
> cell (the result of a formula) some sequences have zerrows included.
> I need to find the lowest number in each sequence and put that number in
> column
> " B" I also need to find the highest number and put that number in column "
> C"
> eg:- A B C
> 4 2 10
> 6
> 2
> 8
> 10
> blank cell
> 7 7 50
> 9
> 11
> 13
> 50
> 18
> 21
> 30
> 15
> blank cell
> 3 1 17
> 5
> 0
> 1
> 0
> 0
> 17
> 6
>
> some sequences have two or more of the same numbers , in case of two ore
> more of the same numbers only one is needed
>
> can some one help me please
>
> regards bill gras
>
> --
> bill gras

Hi Bernie
Thank you for your reply , it's perfect
bill gras
--
bill gras


"Bernie Deitrick" wrote:

> Bill,
>
> Insert a blank row for row 1, then in B2, array enter the formula (enter using Ctrl-Shift-Enter)
>
> =IF(A1="",MIN(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")
>
> In C2, array enter
> =IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")
>
> Then copy down to match your data in column A.
>
> Change the 500's to at least the number of rows that you actually have.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "bill gras" <billgras@discussions.microsoft.com> wrote in message
> news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> > Hi
> > I have in column " A" 500 rows with numbers in random sequences each
> > sequence has random number of rows , each sequence is seperated with a blank
> > cell (the result of a formula) some sequences have zerrows included.
> > I need to find the lowest number in each sequence and put that number in
> > column
> > " B" I also need to find the highest number and put that number in column "
> > C"
> > eg:- A B C
> > 4 2 10
> > 6
> > 2
> > 8
> > 10
> > blank cell
> > 7 7 50
> > 9
> > 11
> > 13
> > 50
> > 18
> > 21
> > 30
> > 15
> > blank cell
> > 3 1 17
> > 5
> > 0
> > 1
> > 0
> > 0
> > 17
> > 6
> >
> > some sequences have two or more of the same numbers , in case of two ore
> > more of the same numbers only one is needed
> >
> > can some one help me please
> >
> > regards bill gras
> >
> > --
> > bill gras
>
>
>

Hi Bob
Thank you for your reply , as always , it's perfect

bill gras
--
bill gras


"Bob Phillips" wrote:

> In row 1
>
> Min: =MIN(A1:INDEX(A1:$A$40,MIN(IF(A1:$A$40="",ROW(A1:$A$40)))-1))
> Max: =MAX(A1:INDEX($A1:$A$40,MIN(IF($A1:$A$40="",ROW($A1:$A$40)))-1))
>
> subsequent rows
> Min:
> =IF(A1="",MIN(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")
> Max:
> =IF(A1="",MAX(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "bill gras" <billgras@discussions.microsoft.com> wrote in message
> news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> > Hi
> > I have in column " A" 500 rows with numbers in random sequences each
> > sequence has random number of rows , each sequence is seperated with a
> blank
> > cell (the result of a formula) some sequences have zerrows included.
> > I need to find the lowest number in each sequence and put that number in
> > column
> > " B" I also need to find the highest number and put that number in column
> "
> > C"
> > eg:- A B C
> > 4 2 10
> > 6
> > 2
> > 8
> > 10
> > blank cell
> > 7 7 50
> > 9
> > 11
> > 13
> > 50
> > 18
> > 21
> > 30
> > 15
> > blank cell
> > 3 1 17
> > 5
> > 0
> > 1
> > 0
> > 0
> > 17
> > 6
> >
> > some sequences have two or more of the same numbers , in case of two ore
> > more of the same numbers only one is needed
> >
> > can some one help me please
> >
> > regards bill gras
> >
> > --
> > bill gras
>
>
>

Hi Bob

I came accross an other problem , with zero's
as follows :


A B C
0 0 (first part of your formula )
80 0 80 (second part of your formula)
78
62
50
48
0 it seems that the formulas are not
working with
0 these three zero's is it possible to change the
0 formulas to exclude the zerro's

76 45 76
76
76
74
70
70
69
66
62
60
50

74 28 74
71
71
70
64
62
59
56
55
45

69 28 73
69
69
67
67
66
63
63
62
59
59
57
56
28

regards bill gras


--
bill gras


"Bob Phillips" wrote:

> In row 1
>
> Min: =MIN(A1:INDEX(A1:$A$40,MIN(IF(A1:$A$40="",ROW(A1:$A$40)))-1))
> Max: =MAX(A1:INDEX($A1:$A$40,MIN(IF($A1:$A$40="",ROW($A1:$A$40)))-1))
>
> subsequent rows
> Min:
> =IF(A1="",MIN(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")
> Max:
> =IF(A1="",MAX(A2:INDEX(A2:$A$40,MIN(IF(A2:$A$40="",ROW(A2:$A$40)))-1)),"")
>
> which is an array formula, it should be committed with Ctrl-Shift-Enter, not
> just Enter.
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "bill gras" <billgras@discussions.microsoft.com> wrote in message
> news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> > Hi
> > I have in column " A" 500 rows with numbers in random sequences each
> > sequence has random number of rows , each sequence is seperated with a
> blank
> > cell (the result of a formula) some sequences have zerrows included.
> > I need to find the lowest number in each sequence and put that number in
> > column
> > " B" I also need to find the highest number and put that number in column
> "
> > C"
> > eg:- A B C
> > 4 2 10
> > 6
> > 2
> > 8
> > 10
> > blank cell
> > 7 7 50
> > 9
> > 11
> > 13
> > 50
> > 18
> > 21
> > 30
> > 15
> > blank cell
> > 3 1 17
> > 5
> > 0
> > 1
> > 0
> > 0
> > 17
> > 6
> >
> > some sequences have two or more of the same numbers , in case of two ore
> > more of the same numbers only one is needed
> >
> > can some one help me please
> >
> > regards bill gras
> >
> > --
> > bill gras
>
>
>

Hi Bernie
there is a problem with the formulas you gave me , the problem came about
with the zerro's . Can you change the formulas to exclude the zerro's ?

the problem as follows:

A B C

80 0 80
78
62
50
48
0
0
0

76 45 76
76
76
74
70
70
69
66
62
60
50

74 28 74
71
71
70
64
62
59
56
55
45

69 28 73
69
69
67
67
66
63
63
62
59
59
57
56
28

73 28 73
71
66
64
63
62
62
60
60
48
28

regards bill gras
--
bill gras


"Bernie Deitrick" wrote:

> Bill,
>
> Insert a blank row for row 1, then in B2, array enter the formula (enter using Ctrl-Shift-Enter)
>
> =IF(A1="",MIN(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")
>
> In C2, array enter
> =IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",ROW(A2:$A$500),500))-1,1)),"")
>
> Then copy down to match your data in column A.
>
> Change the 500's to at least the number of rows that you actually have.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "bill gras" <billgras@discussions.microsoft.com> wrote in message
> news:387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com...
> > Hi
> > I have in column " A" 500 rows with numbers in random sequences each
> > sequence has random number of rows , each sequence is seperated with a blank
> > cell (the result of a formula) some sequences have zerrows included.
> > I need to find the lowest number in each sequence and put that number in
> > column
> > " B" I also need to find the highest number and put that number in column "
> > C"
> > eg:- A B C
> > 4 2 10
> > 6
> > 2
> > 8
> > 10
> > blank cell
> > 7 7 50
> > 9
> > 11
> > 13
> > 50
> > 18
> > 21
> > 30
> > 15
> > blank cell
> > 3 1 17
> > 5
> > 0
> > 1
> > 0
> > 0
> > 17
> > 6
> >
> > some sequences have two or more of the same numbers , in case of two ore
> > more of the same numbers only one is needed
> >
> > can some one help me please
> >
> > regards bill gras
> >
> > --
> > bill gras
>
>
>

Here's another way...

First, assuming that Column A contains the data, starting at A3, define
the following...

Select B3

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Add

Name: LRec

Refers to:

=MATCH(BigNum,Sheet1!$A:$A)

Click Add

Name: Range

Refers to:

=Sheet1!$A3:INDEX(Sheet1!$A3:INDEX(Sheet1!$A:$A,LRec),LOOKUP(BigNum,CHOOS
E({1,2},LRec-ROW(Sheet1!B3)+1,MATCH(TRUE,Sheet1!$A3:INDEX(Sheet1!$A:$A,LR
ec)="",0)-1)))

Click Ok

Change the sheet reference accordingly. Then, try the following
formulas...

For minimum...

B3, copied down:

=IF(ISNUMBER(A2),"",MIN(IF(Range>0,Range)))

....confirmed with CONTROL+SHIFT+ENTER

For maximum...

C3, copied down:

=IF(ISNUMBER(A2),"",MAX(Range))

Hope this helps!

In article <387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com>,
bill gras <billgras@discussions.microsoft.com> wrote:

> Hi
> I have in column " A" 500 rows with numbers in random sequences each
> sequence has random number of rows , each sequence is seperated with a blank
> cell (the result of a formula) some sequences have zerrows included.
> I need to find the lowest number in each sequence and put that number in
> column
> " B" I also need to find the highest number and put that number in column "
> C"
> eg:- A B C
> 4 2 10
> 6
> 2
> 8
> 10
> blank cell
> 7 7 50
> 9
> 11
> 13
> 50
> 18
> 21
> 30
> 15
> blank cell
> 3 1 17
> 5
> 0
> 1
> 0
> 0
> 17
> 6
>
> some sequences have two or more of the same numbers , in case of two ore
> more of the same numbers only one is needed
>
> can some one help me please
>
> regards bill gras

Hi Domenic

To simplify what I need is
a) from blank row to the next blank row ,(which has from 3 to 24 rows in
between with numbers), the highest humber
b) from the same blank row to the next blank row , the lowest number after
the zero
c) each blank row to the next blank row has a random number of rows in
between .
There are 70 sequences with a maximum of 1800 rows

I hope I made my self clear enough so that you might be able to help me with
this

Thank you in advance

regards bill gras


--
bill gras


"Domenic" wrote:

> Here's another way...
>
> First, assuming that Column A contains the data, starting at A3, define
> the following...
>
> Select B3
>
> Insert > Name > Define
>
> Name: BigNum
>
> Refers to:
>
> =9.99999999999999E+307
>
> Click Add
>
> Name: LRec
>
> Refers to:
>
> =MATCH(BigNum,Sheet1!$A:$A)
>
> Click Add
>
> Name: Range
>
> Refers to:
>
> =Sheet1!$A3:INDEX(Sheet1!$A3:INDEX(Sheet1!$A:$A,LRec),LOOKUP(BigNum,CHOOS
> E({1,2},LRec-ROW(Sheet1!B3)+1,MATCH(TRUE,Sheet1!$A3:INDEX(Sheet1!$A:$A,LR
> ec)="",0)-1)))
>
> Click Ok
>
> Change the sheet reference accordingly. Then, try the following
> formulas...
>
> For minimum...
>
> B3, copied down:
>
> =IF(ISNUMBER(A2),"",MIN(IF(Range>0,Range)))
>
> ....confirmed with CONTROL+SHIFT+ENTER
>
> For maximum...
>
> C3, copied down:
>
> =IF(ISNUMBER(A2),"",MAX(Range))
>
> Hope this helps!
>
> In article <387C0E24-0799-4589-881C-C12EE2DBF7FB@microsoft.com>,
> bill gras <billgras@discussions.microsoft.com> wrote:
>
> > Hi
> > I have in column " A" 500 rows with numbers in random sequences each
> > sequence has random number of rows , each sequence is seperated with a blank
> > cell (the result of a formula) some sequences have zerrows included.
> > I need to find the lowest number in each sequence and put that number in
> > column
> > " B" I also need to find the highest number and put that number in column "
> > C"
> > eg:- A B C
> > 4 2 10
> > 6
> > 2
> > 8
> > 10
> > blank cell
> > 7 7 50
> > 9
> > 11
> > 13
> > 50
> > 18
> > 21
> > 30
> > 15
> > blank cell
> > 3 1 17
> > 5
> > 0
> > 1
> > 0
> > 0
> > 17
> > 6
> >
> > some sequences have two or more of the same numbers , in case of two ore
> > more of the same numbers only one is needed
> >
> > can some one help me please
> >
> > regards bill gras
>

The solution I offered should give you the desired results. Have you
tried it?

In article <DCDBD479-6910-451C-AF65-7E3173038ECD@microsoft.com>,
bill gras <billgras@discussions.microsoft.com> wrote:

> Hi Domenic
>
> To simplify what I need is
> a) from blank row to the next blank row ,(which has from 3 to 24 rows in
> between with numbers), the highest humber
> b) from the same blank row to the next blank row , the lowest number after
> the zero
> c) each blank row to the next blank row has a random number of rows in
> between .
> There are 70 sequences with a maximum of 1800 rows
>
> I hope I made my self clear enough so that you might be able to help me with
> this
>
> Thank you in advance
>
> regards bill gras
>
>
> --
> bill gras

Hi Domenic

Sorry I jump the gun with out trying your method , now that I have tried it
there's only one thing to say YOU ARE A GENIUS !

Thank you for your time and effort

regards bill gras
--
bill gras


"Domenic" wrote:

> The solution I offered should give you the desired results. Have you
> tried it?
>
> In article <DCDBD479-6910-451C-AF65-7E3173038ECD@microsoft.com>,
> bill gras <billgras@discussions.microsoft.com> wrote:
>
> > Hi Domenic
> >
> > To simplify what I need is
> > a) from blank row to the next blank row ,(which has from 3 to 24 rows in
> > between with numbers), the highest humber
> > b) from the same blank row to the next blank row , the lowest number after
> > the zero
> > c) each blank row to the next blank row has a random number of rows in
> > between .
> > There are 70 sequences with a maximum of 1800 rows
> >
> > I hope I made my self clear enough so that you might be able to help me with
> > this
> >
> > Thank you in advance
> >
> > regards bill gras
> >
> >
> > --
> > bill gras
>

You're very welcome! Glad I could help! Also, I wanted to point out
that you'll be able to add additional sets of data in Column A without
having to modify the formulas. All you have to do, after adding new
data, is to copy/drag the formulas down each column....

In article <C70B2151-A660-4495-AEEF-EA855457FE9B@microsoft.com>,
bill gras <billgras@discussions.microsoft.com> wrote:

> Hi Domenic
>
> Sorry I jump the gun with out trying your method , now that I have tried it
> there's only one thing to say YOU ARE A GENIUS !
>
> Thank you for your time and effort
>
> regards bill gras
> --
> bill gras