Summing a function

S

Spreadsheet

Is there a way to write a function, specify a range of integers, and
have Excel return the sum of the function evaluated at each integer?
The only way that I know how to do this is to actually create a list
with every integer and the corresponding value of the funciton at that
integer and then sum the list. This is not a good idea, however, when
the number of integers is in the thousands. Is there any way to get
around this?
 
G

Gary L Brown

- How do you know which integers you want to sum?
- Are the integers all in one column?
 
S

Spreadsheet

I want the sum of the function evaluated at a range of integers. I want
to be able to specify the range of integers as a paramater of the
function. For example, suppose there is some function that does what I
want called SUMFUNC.

SUMFUNC(X^2,1,10) would return the sum of squares from 1 to 10 (i.e.
1+4+9+16+...+100).

Or maybe if I have two cells, with the starting integer in A1 and the
ending integer in A2 the funciton would look as follows:
SUMFUCNC((A1:A2)^2). This would also return 1+4+9+...+100.

I just made this up as an example and I'm sure that there is no easy
solution like this. But if anybody has any ideas, please let me know.
 
G

Gary L Brown

I don't believe any such function exists. You'd have to create a
User-Defined-Funtion.
 
H

Herbert Seidenberg

The list of integers could be hidden as an array name.
If the integers are {1,2,3,4,5,6,7,8,9,10}
then Start (A1=1) and End (A2=10) define this list named Seqn
Insert > Name > Define
Seqn Refers To: =ROW(INDEX(A:A,Start):INDEX(A:A,End))
The sum of the squares is then
=SUMPRODUCT(Seqn^2) =385
With the use of Mod and Int, etc, Seqn can be built up to any
complexity you want.
 
D

Daniel Erwin

How about using this array formula:
=sum(row(1:10)^2)
You may change the numbers to suit your needs.

Regards,
Daniel
 
D

Daniel Erwin

Or maybe if I have two cells, with the starting integer in A1 and the
ending integer in A2 the funciton would look as follows:
SUMFUCNC((A1:A2)^2). This would also return 1+4+9+...+100.
Click to expand...

Let's see A1 for first integer, and B1 as last integer, use this array
formula:
=SUM(ROW(INDIRECT("A"&INDIRECT("A1")&":B"&INDIRECT("B1")))^2)

Regards,
Daniel
 
D

Dana DeLouis

Let's see A1 for first integer, and B1 as last integer...
=SUM(ROW(INDIRECT("A"&INDIRECT("A1")&":B"&INDIRECT("B1")))^2)
Click to expand...

Hi. For this particular equation, another option might be:

=(A1*((3-2*A1)*A1-1)+B1*(B1+1)*(2*B1+1))/6

as the sum from 1 to n 'squared' is n*(1 + n)*((1 + 2*n)/6)
 
S

Spreadsheet

Thanks Daniel,

I got the array formula to work, although it didn't work until I
Ctrl-Shift-Entered it.
 
Top