Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 05, 07:41 PM
Lori
 
Posts: n/a
Default 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   Report Post  
Old May 20th 05, 08:09 PM
bj
 
Posts: n/a
Default

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

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

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

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

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


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

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I set up a formula using number ranges as the arguments Ballykea New Users to Excel 1 April 28th 05 05:47 PM
Formula that will count between dates ranges jbsand1001 Excel Worksheet Functions 4 April 1st 05 11:41 PM
Formula that will count between dates ranges jbsand1001 Excel Worksheet Functions 0 March 7th 05 06:41 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 04:16 AM
Show two value ranges on one axis NOKIA Charts and Charting in Excel 5 December 23rd 04 01:51 PM


All times are GMT +1. The time now is 02:43 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017