Remember Me?

#1
May 20th 05, 07:41 PM
 Lori Posts: n/a
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
May 20th 05, 08:09 PM
 bj Posts: n/a

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
May 20th 05, 08:56 PM
 Lori Posts: n/a

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
May 20th 05, 09:02 PM
 bj Posts: n/a

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
May 20th 05, 09:11 PM
 Lori Posts: n/a

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
May 20th 05, 09:41 PM
 bj Posts: n/a

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
May 20th 05, 09:57 PM
 Lori Posts: n/a

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
May 23rd 05, 12:44 PM
 bj Posts: n/a

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=A3-1,"",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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Ballykea New Users to Excel 1 April 28th 05 05:47 PM jbsand1001 Excel Worksheet Functions 4 April 1st 05 11:41 PM jbsand1001 Excel Worksheet Functions 0 March 7th 05 07:41 PM Damian Excel Worksheet Functions 1 January 7th 05 05:16 AM NOKIA Charts and Charting in Excel 5 December 23rd 04 02:51 PM

All times are GMT +1. The time now is 11:33 PM.