Home 
Search 
Today's Posts 
#1




formula to show ranges in columns
I am trying to find a formula that will show the individual ranges shown in
column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#2




try
=min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#3




Thank you, but that doesn't seem to work because there are 1000s of numbers
in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#4




How do you differencialte the different individual ranges?
"Lori" wrote: Thank you, but that doesn't seem to work because there are 1000s of numbers in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#5




It's not set up to differentiate between individual ranges. What would you
suggest? "bj" wrote: How do you differencialte the different individual ranges? "Lori" wrote: Thank you, but that doesn't seem to work because there are 1000s of numbers in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#6




from your first example are all of them in groups of three separated by one
row. if this is the case you could use in B3 =if(A2="",min(A3:A5),"") and in C3 =if(A2="",max(A3:A5),"") If I understand what you are doing, you will need either a pattern or a identifier of some type which will identify the different grouping you wish to get the range for. If it is not three numbers per group, and you use a blank line between the groups what is the max and minimum number of data ppoints you want to get the range for? Also in your example the trios were in order. Is this always the case. (there are some short cuts which can be used if it is always the case.) "Lori" wrote: It's not set up to differentiate between individual ranges. What would you suggest? "bj" wrote: How do you differencialte the different individual ranges? "Lori" wrote: Thank you, but that doesn't seem to work because there are 1000s of numbers in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#7




The numbers in column A are always in numerical order and there are no spaces separating the individual ranges. The individual ranges vary. There could be 2 numbers in numerical order to hundreds of numbers in numerical order. Here is a better example. A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 1512 1512 1516 1513 1514 1515 1516 "bj" wrote: from your first example are all of them in groups of three separated by one row. if this is the case you could use in B3 =if(A2="",min(A3:A5),"") and in C3 =if(A2="",max(A3:A5),"") If I understand what you are doing, you will need either a pattern or a identifier of some type which will identify the different grouping you wish to get the range for. If it is not three numbers per group, and you use a blank line between the groups what is the max and minimum number of data ppoints you want to get the range for? Also in your example the trios were in order. Is this always the case. (there are some short cuts which can be used if it is always the case.) "Lori" wrote: It's not set up to differentiate between individual ranges. What would you suggest? "bj" wrote: How do you differencialte the different individual ranges? "Lori" wrote: Thank you, but that doesn't seem to work because there are 1000s of numbers in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
#8




from this example it looks as though the ranges are defined as consecutive
numbers and the range divisions are caused by missing numbers if this is the case in B2 enter = if(A2=A1+1,"",A2) in D2 enter =if(A2=A31,"",A2) in C2 enter =if(B2<"",D2,"") copy B22 to the end of your data and hide Column D If my assumption as to what defines the ranges is wrong, Please let me know with another example and I wil trry again. "Lori" wrote: The numbers in column A are always in numerical order and there are no spaces separating the individual ranges. The individual ranges vary. There could be 2 numbers in numerical order to hundreds of numbers in numerical order. Here is a better example. A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 1512 1512 1516 1513 1514 1515 1516 "bj" wrote: from your first example are all of them in groups of three separated by one row. if this is the case you could use in B3 =if(A2="",min(A3:A5),"") and in C3 =if(A2="",max(A3:A5),"") If I understand what you are doing, you will need either a pattern or a identifier of some type which will identify the different grouping you wish to get the range for. If it is not three numbers per group, and you use a blank line between the groups what is the max and minimum number of data ppoints you want to get the range for? Also in your example the trios were in order. Is this always the case. (there are some short cuts which can be used if it is always the case.) "Lori" wrote: It's not set up to differentiate between individual ranges. What would you suggest? "bj" wrote: How do you differencialte the different individual ranges? "Lori" wrote: Thank you, but that doesn't seem to work because there are 1000s of numbers in column A with numerous individual ranges, and I want all the individual ranges to be shown in columns B and C. "bj" wrote: try =min(range1) in column B and =max(range1) in column C "Lori" wrote: I am trying to find a formula that will show the individual ranges shown in column A. The first number in the range will appear in column B and the last number in the range will appear in the adjacent cell in column C. Is this even possible? A B C 1500 1500 1502 1501 1502 1505 1505 1507 1506 1507 1509 1509 1510 1510 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how do I set up a formula using number ranges as the arguments  New Users to Excel  
Formula that will count between dates ranges  Excel Worksheet Functions  
Formula that will count between dates ranges  Excel Worksheet Functions  
Relative Indirect Formula Referencing?  Excel Worksheet Functions  
Show two value ranges on one axis  Charts and Charting in Excel 