If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly?

=SUM(INDEX(ROW(1:100),,1))

suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.

Need more detail. An example?

Biff

"ExcelQuestion" wrote in message
...
If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.
I think it should be relatively simple...but i cant seem to figure it out.

It is a relatively simple, but not very intuitive trick you can use to do
this. You can use the ROW() function in an array formula. So, the formulas
for your examples would be:

=SUM(ROW(1:100))

=SUM(ROW(1:10)/2+4)

Array formulas must be commited with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula will be enclosed in { }.

Also note that since the number of rows in an Excel spreadsheet is limited
(65536 in Excel 2003 and earlier and 1048576 in Excel 2007) this trick will
only work for numbers within that range. So, no negative numbers either.

HTH,
Elkar


"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.
I think it should be relatively simple...but i cant seem to figure it out.

The sum of the first N integers = N*(N+1)/2

The sum of the first N evaluations of N/2+4 = 4*N+N*(N+1)/4

These are direct calculations and can be performed outside of Excel if
needed.

Rick

If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.
I think it should be relatively simple...but i cant seem to figure it
out.

The sum of the first N integers = N*(N+1)/2

The sum of the first N evaluations of N/2+4 = 4*N+N*(N+1)/4

These are direct calculations and can be performed outside of Excel if
needed.

The last formula simplifies to...

The sum of the first N evaluations of N/2+4 = N*(N+17)/4

Rick

=(1+100)*100/2
and similarly
=((1/2+4)+(10/2+4))*10/2

Or otherwise
=AVERAGE(1,100)*100
and
=AVERAGE(1/2+4,10/2+4)*10
--
David Biddulph

"ExcelQuestion" wrote in message
...
If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.
I think it should be relatively simple...but i cant seem to figure it out.

Ok, I think i almost got it to work. Here is the equation i came up with:
=SUM(B6*(1+B7)^(ROW(1:B9)))
When i specify a number for B9 it works fine... ie:
=SUM(B6*(1+B7)^(ROW(1:8))) but i wanted to be able to compute the value
without going in and manually changing it, ie. through a link. is that
possible?



"Elkar" wrote:

It is a relatively simple, but not very intuitive trick you can use to do
this. You can use the ROW() function in an array formula. So, the formulas
for your examples would be:

=SUM(ROW(1:100))

=SUM(ROW(1:10)/2+4)

Array formulas must be commited with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula will be enclosed in { }.

Also note that since the number of rows in an Excel spreadsheet is limited
(65536 in Excel 2003 and earlier and 1048576 in Excel 2007) this trick will
only work for numbers within that range. So, no negative numbers either.

HTH,
Elkar


"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

Try this:

=SUM(B6*(1+B7)^(ROW(INDIRECT("1:"&B9))))

Where B9 holds the variable

Biff

"ExcelQuestion" wrote in message
...
Ok, I think i almost got it to work. Here is the equation i came up with:
=SUM(B6*(1+B7)^(ROW(1:B9)))
When i specify a number for B9 it works fine... ie:
=SUM(B6*(1+B7)^(ROW(1:8))) but i wanted to be able to compute the value
without going in and manually changing it, ie. through a link. is that
possible?



"Elkar" wrote:

It is a relatively simple, but not very intuitive trick you can use to do
this. You can use the ROW() function in an array formula. So, the
formulas
for your examples would be:

=SUM(ROW(1:100))

=SUM(ROW(1:10)/2+4)

Array formulas must be commited with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula will be enclosed in { }.

Also note that since the number of rows in an Excel spreadsheet is
limited
(65536 in Excel 2003 and earlier and 1048576 in Excel 2007) this trick
will
only work for numbers within that range. So, no negative numbers either.

HTH,
Elkar


"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of
say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1
and 10.
I think it should be relatively simple...but i cant seem to figure it
out.

actually i just figured out what row was doing... and im not sure if its
quite what i need because it would only be giving me values for whole numbers
and i would have to specify the variables in the equation beforehand as
opposed to making a link to a number somewhere else. Is there any way to do
the same type of thing for decimals? so 1, 1.1, 1.2... 2.5 lets say? so
something like the sum of (10(ratex)^n) where n is between 1 and 2.5 at
intervals of .1?

"Elkar" wrote:

It is a relatively simple, but not very intuitive trick you can use to do
this. You can use the ROW() function in an array formula. So, the formulas
for your examples would be:

=SUM(ROW(1:100))

=SUM(ROW(1:10)/2+4)

Array formulas must be commited with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula will be enclosed in { }.

Also note that since the number of rows in an Excel spreadsheet is limited
(65536 in Excel 2003 and earlier and 1048576 in Excel 2007) this trick will
only work for numbers within that range. So, no negative numbers either.

HTH,
Elkar


"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

To get decimal values, you could do something like: (ROW(10:25)/10) This
would effectively give you 1 through 2.5 in .1 increments.

HTH,
Elkar


"ExcelQuestion" wrote:

actually i just figured out what row was doing... and im not sure if its
quite what i need because it would only be giving me values for whole numbers
and i would have to specify the variables in the equation beforehand as
opposed to making a link to a number somewhere else. Is there any way to do
the same type of thing for decimals? so 1, 1.1, 1.2... 2.5 lets say? so
something like the sum of (10(ratex)^n) where n is between 1 and 2.5 at
intervals of .1?

"Elkar" wrote:

It is a relatively simple, but not very intuitive trick you can use to do
this. You can use the ROW() function in an array formula. So, the formulas
for your examples would be:

=SUM(ROW(1:100))

=SUM(ROW(1:10)/2+4)

Array formulas must be commited with CTRL-SHIFT-ENTER rather than just
Enter. If done properly, the formula will be enclosed in { }.

Also note that since the number of rows in an Excel spreadsheet is limited
(65536 in Excel 2003 and earlier and 1048576 in Excel 2007) this trick will
only work for numbers within that range. So, no negative numbers either.

HTH,
Elkar


"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

I got it working... here is what i ended up doing:

=SUM(B6*(1+B7)^(ROW(INDIRECT("1:"&C9))/12))

But i swear there must be a more efficient way of doing this.. no?

"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this? also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and 10.
I think it should be relatively simple...but i cant seem to figure it out.

See if this old post helps:


http://tinyurl.com/2alkj2



--
HTH,

RD

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

"ExcelQuestion" wrote in message
...
I got it working... here is what i ended up doing:

=SUM(B6*(1+B7)^(ROW(INDIRECT("1:"&C9))/12))

But i swear there must be a more efficient way of doing this.. no?

"ExcelQuestion" wrote:

If i want to sum all the numbers between 1 and 100 at an interval of say
one
(ie... 1+2+3...100) what do i do exactly? is there a formula for this?
also
suppose i wanted to sum all the values for n/2+4 where n is between 1 and
10.
I think it should be relatively simple...but i cant seem to figure it
out.