min and max in a sequence
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($A 1:$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" wrote in message
...
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
|