Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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="",RO W(A2:$A$500),500))-1,1)),"") In C2, array enter =IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",RO W(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" 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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="",RO W(A2:$A$500),500))-1,1)),"") In C2, array enter =IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",RO W(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" 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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($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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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($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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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="",RO W(A2:$A$500),500))-1,1)),"") In C2, array enter =IF(A1="",MAX(OFFSET(A2,0,0,MIN(IF(A2:$A$500="",RO W(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" 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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,LR ec),LOOKUP(BigNum,CHOOS E({1,2},LRec-ROW(Sheet1!B3)+1,MATCH(TRUE,Sheet1!$A3:INDEX(Sheet 1!$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(Range0,Range))) ....confirmed with CONTROL+SHIFT+ENTER For maximum... C3, copied down: =IF(ISNUMBER(A2),"",MAX(Range)) Hope this helps! In article , bill gras 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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,LR ec),LOOKUP(BigNum,CHOOS E({1,2},LRec-ROW(Sheet1!B3)+1,MATCH(TRUE,Sheet1!$A3:INDEX(Sheet 1!$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(Range0,Range))) ....confirmed with CONTROL+SHIFT+ENTER For maximum... C3, copied down: =IF(ISNUMBER(A2),"",MAX(Range)) Hope this helps! In article , bill gras 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
The solution I offered should give you the desired results. Have you
tried it? In article , bill gras 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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 , bill gras 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
min and max in a sequence
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 , bill gras 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|