How do I segment a column of data into size ranges in Excel?

M

Motown Mick

I have a column of data listed in ascending order. I would like to pull
sections of it out (i.e. 0-10, 11-20, etc.) without having to manually go
through the column and highlight that section, copy, paste, etc. Ideally, if
there is a formula command that can point to a section of data in a column
like this, and perform an operation on it, that I can direct to a particular
cell, I would like to know how to do that.

I can't figure out what version of Excel I have. It's whatever comes with
MS Windows XP.
 
R

RagDyeR

Say your data was in Column A, and you enter the row you wish to *start*
copying from into B1:

=INDEX(A:A,ROWS($1:1)-1+$B$1)

And copy down as many rows as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I have a column of data listed in ascending order. I would like to pull
sections of it out (i.e. 0-10, 11-20, etc.) without having to manually go
through the column and highlight that section, copy, paste, etc. Ideally,
if
there is a formula command that can point to a section of data in a column
like this, and perform an operation on it, that I can direct to a particular
cell, I would like to know how to do that.

I can't figure out what version of Excel I have. It's whatever comes with
MS Windows XP.
 
B

Biff

Hi!
Ideally, if
there is a formula command that can point to a section of data in a column
like this, and perform an operation on it, that I can direct to a particular
cell, I would like to know how to do that.
There is, but you need to tell us *EXACTLY* what you want to do and tell us
*EXACTLY* where the data is.

For example, This formula, when copied down, will sum every 10 rows in
column A:

=SUM(OFFSET(A$1,(ROWS($1:1)-1)*10,,10))

The first cell will sum A1:A10
The next cell will sum A11:A20
The next cell will sum A21:A30
etc
etc

Biff
 
R

RagDyeR

I didn't read the question as to performing any sort of calculation, but you
may be right.

SO ... to Sum a range of cells contained in Column A,
Enter the cell to *start* into B1,
And enter the *ending* cell into B2,
And try this:

=SUM(INDEX(A:A,B1):INDEX(A:A,B2))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi!
Ideally, if
there is a formula command that can point to a section of data in a column
like this, and perform an operation on it, that I can direct to a particular
cell, I would like to know how to do that.
There is, but you need to tell us *EXACTLY* what you want to do and tell us
*EXACTLY* where the data is.

For example, This formula, when copied down, will sum every 10 rows in
column A:

=SUM(OFFSET(A$1,(ROWS($1:1)-1)*10,,10))

The first cell will sum A1:A10
The next cell will sum A11:A20
The next cell will sum A21:A30
etc
etc

Biff
 
R

RagDyeR

And needless to say, I *should* have said:

"Enter the row number of the cells to 'start' and 'end' into B1 & B2.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

I didn't read the question as to performing any sort of calculation, but you
may be right.

SO ... to Sum a range of cells contained in Column A,
Enter the cell to *start* into B1,
And enter the *ending* cell into B2,
And try this:

=SUM(INDEX(A:A,B1):INDEX(A:A,B2))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi!
Ideally, if
there is a formula command that can point to a section of data in a column
like this, and perform an operation on it, that I can direct to a particular
cell, I would like to know how to do that.
There is, but you need to tell us *EXACTLY* what you want to do and tell us
*EXACTLY* where the data is.

For example, This formula, when copied down, will sum every 10 rows in
column A:

=SUM(OFFSET(A$1,(ROWS($1:1)-1)*10,,10))

The first cell will sum A1:A10
The next cell will sum A11:A20
The next cell will sum A21:A30
etc
etc

Biff
 
G

Gord Dibben

I'll get out of way of the formula kings and address the Excel version question.

No Version of Excel comes with Windows XP.

Your seller must have installed MS Office or Excel on your computer.

To see which version, open Excel and Help>About.

You will see the version there.

Suggest you check the disks that came with your computer to see if your seller
provided any for MS Office or Excel.

You may need a disk at some point for a repair/re-install.


Gord Dibben MS Excel MVP
 
M

Motown Mick

Biff,

This is almost like what I want to do except that:

1. Say I'm dealing with Column A. I'm looking for a way to sum by VALUE,
not cell designation. So rather than summing A1:A10, A11:A20, I would like
to know how to sum all the entries in Column A that actually have an entered
data value of, say, 0-249, 250-499, etc.

2. A formula that can be copied and dragged down is not necessary. If I
could just get a formula that could be applied to each of those data ranges,
that would do the trick for me.

Thanks.

Motown Mick
 
M

Motown Mick

Gord,
Thanks. It's Excel 2002.
Mick

Gord Dibben said:
I'll get out of way of the formula kings and address the Excel version question.

No Version of Excel comes with Windows XP.

Your seller must have installed MS Office or Excel on your computer.

To see which version, open Excel and Help>About.

You will see the version there.

Suggest you check the disks that came with your computer to see if your seller
provided any for MS Office or Excel.

You may need a disk at some point for a repair/re-install.


Gord Dibben MS Excel MVP
Click to expand...
 
B

Biff

Ok.....

Set up some cells to hold your value ranges:

............C...........D.............E.....
1.........0..........249.......formula
2.......250........499.......formula
3.......500........749.......formula

Assume the range of numbers is in A1:A25

Enter this formula in E1 and copy down as needed:

=SUMIF(A$1:A$25,">="&C1)-SUMIF(A$1:A$25,">"&D1)

Biff
 
R

Ragdyer

In that case, you could simply designate 2 cells ... one to contain the
lower and another to contain the upper values that you wish to total.

Say the lower number is in B1, and the upper threshold is in B2:

=SUMPRODUCT((A1:A100>=B1)*(A1:A100<=B2)*A1:A100)

With Sumproduct(), you *can't* use entire column references (A:A - B:B).
 
M

Motown Mick

Biff,

That worked fine. A couple of further ques.:

1. I only tried if for a couple of the value ranges that I knew would fall
into the area of A1:A25. I'm assuming, the way you set up the syntax, that
Excel would disregard entries in A that fell out of that segment. So for
instance, for the range 500-749, if all the entries in A that fell into those
bounds were in cells of A greater than 25 (say, A26:A99), given the formula
you've given me, the sum would be zero. I would like to set it so that it
searches the entire column of A for values that fall into the specified
ranges. How do I do that?

2. The final value range is open at the top; in other words, I want to sum
all values in Col. A that take a value of, say, 250,000 on up to infinity.
What would I write in col. D to represent "infinity"?

Mick
 
B

Biff

Ok....

1. I just used a range of A1:A25 for demonstration purposes. Use your actual
range whatever it may be, or if you want, use the entire column A:A.

2. You can do a couple of things for this.

A. use an upper boundary value that you know you will never exceed. Like

250,000..........10,000,000,000

B. use a separate formula for this last group with just the single boundary:

=SUMIF(A:A,">="&C10)

Where C10 = 250,000

Biff
 
M

Motown Mick

Biff,

Thanks, that worked great. I thought of the "guhzillion" thing in there, I
guess that's the simplest and easiest thing to do.

Now I am trying to take the results in the E column and divide them by a
single constant scalar and display the results in an adjoining column. I
know about the "paste special" command. But for some reason, I cannot copy
those E column results to another column where I can do that on it, and leave
the E results unharmed. Do you know how I can do this?

Mick
 
B

Biff

Ok.....

Not sure I understand why you want to do it the way you describe.....

What's wrong with:

E2 = formula result

F2 = =E2/some_number

You can paste special>values:

Select E2 (or the range of formula results)
Goto the menu Edit>Copy
Select the destination cell
Goto the menu Edit>Paste special>Values>OK

That'll copy/paste the formula results as constants and still leave the
original formulas in tact.

Biff
 
M

Motown Mick

Biff,
That worked fine. I had to do Edit>Paste Special >Values for the
denominator as well, because when I did it by just by just copying it as is,
the results came out screwed up with an error saying I was dividing by zero.
But that worked great, I got it all now, I think. Thanks for all your help.
-Mick
 
B

Biff

You're welcome!

Biff

Motown Mick said:
Biff,
That worked fine. I had to do Edit>Paste Special >Values for the
denominator as well, because when I did it by just by just copying it as
is,
the results came out screwed up with an error saying I was dividing by
zero.
But that worked great, I got it all now, I think. Thanks for all your
help.
-Mick
Click to expand...
 
Top