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.

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!
================================================== ===

"Motown Mick" <Motown wrote in message
...
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.

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

"Motown Mick" <Motown wrote in message
...
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.

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!
================================================== ===


"Biff" wrote in message
...
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

"Motown Mick" <Motown wrote in message
...
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.

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 !
----------------------------------------------------------------------------
-------------------

"RagDyeR" wrote in message
...
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!
================================================== ===


"Biff" wrote in message
...
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

"Motown Mick" <Motown wrote in message
...
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.

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 HelpAbout.

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

On Sat, 26 Aug 2006 15:07:24 -0700, "RagDyeR" wrote:

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 !
----------------------------------------------------------------------------
-------------------

"RagDyeR" wrote in message
...
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))

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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

Gord,
Thanks. It's Excel 2002.
Mick

"Gord Dibben" wrote:

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 HelpAbout.

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

On Sat, 26 Aug 2006 15:07:24 -0700, "RagDyeR" wrote:

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 !
----------------------------------------------------------------------------
-------------------

"RagDyeR" wrote in message
...
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))

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

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).
--
HTH,

RD

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in message
...
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.

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 specialvalues:

Select E2 (or the range of formula results)
Goto the menu EditCopy
Select the destination cell
Goto the menu EditPaste specialValuesOK

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

Biff

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in
message
...
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.

Biff,
That worked fine. I had to do EditPaste 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

"Biff" wrote:

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 specialvalues:

Select E2 (or the range of formula results)
Goto the menu EditCopy
Select the destination cell
Goto the menu EditPaste specialValuesOK

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

Biff

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in
message
...
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.

You're welcome!

Biff

"Motown Mick" wrote in message
...
Biff,
That worked fine. I had to do EditPaste 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

"Biff" wrote:

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 specialvalues:

Select E2 (or the range of formula results)
Goto the menu EditCopy
Select the destination cell
Goto the menu EditPaste specialValuesOK

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

Biff

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in message
...
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

"Biff" wrote:

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

"Motown Mick" wrote in
message
...
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

"Biff" wrote:

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

"Motown Mick" <Motown wrote in
message
...
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.