summation

[ExcelQuestion]

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.

 

[T. Valko]

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

 

[Elkar]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

If i want to sum all the numbers between 1 and 100 at an interval of say

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

 

[David Biddulph]

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

 

[ExcelQuestion]

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?

 

[T. Valko]

Try this:

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

Where B9 holds the variable

Biff

 

[ExcelQuestion]

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]

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]

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?

 

[Ragdyer]

See if this old post helps:


http://tinyurl.com/2alkj2