So I have a certain function f(x), where x is a positive integer value.

I would like another function g(y) which is the sum of all values of f(x), with
x ranging from 0..y

This is trivial using Sigma notation.  Unfortunately, this is not available in
any spreadsheet I know of (I use MS Office at work, and OpenOffice.org at
home).

I'd rather not write a macro for this, as that seems to me as useful as writing
a macro to do addition (in fact, it *is* addition).  Just the thought that I'd
have to script something so basic and intuitive is abhorant to me.  Not to
mention that every time I opened the spreadsheet, I'd have to deal with those
annoying security warnings about dangerous macros.

I could populate an entire column on a separate sheet with the values I need,
but then every time the calculation is done all the cells in that column would
have to be calculated.  The programmer in me hates all that wasted calculation,
and wants to "optimize" it away by only calculating what I actually need
(although I might not call it optimization, since really I'm just getting it to
do what I want it to do in the first place).

I've tried googling, but came up with nothing but introductions on how to use
the SUM() function, which is not what I want.  If Google is my friend, then it
isn't one with benefits.

Does anyone here have an idea of how to calculate a sum this way?

....Chambers
www.pacificwebguy.com

Post a reply to this message

Chambers wrote:
> So I have a certain function f(x), where x is a positive integer value.
> 
> I would like another function g(y) which is the sum of all values of f(x), with
> x ranging from 0..y
> 
> This is trivial using Sigma notation.  Unfortunately, this is not available in
> any spreadsheet I know of (I use MS Office at work, and OpenOffice.org at
> home).
> 
> I'd rather not write a macro for this, as that seems to me as useful as writing
> a macro to do addition (in fact, it *is* addition).  Just the thought that I'd
> have to script something so basic and intuitive is abhorant to me.  Not to
> mention that every time I opened the spreadsheet, I'd have to deal with those
> annoying security warnings about dangerous macros.
> 
> I could populate an entire column on a separate sheet with the values I need,
> but then every time the calculation is done all the cells in that column would
> have to be calculated.  The programmer in me hates all that wasted calculation,
> and wants to "optimize" it away by only calculating what I actually need
> (although I might not call it optimization, since really I'm just getting it to
> do what I want it to do in the first place).
> 
> I've tried googling, but came up with nothing but introductions on how to use
> the SUM() function, which is not what I want.  If Google is my friend, then it
> isn't one with benefits.
> 
> Does anyone here have an idea of how to calculate a sum this way?
> 
> ....Chambers
> www.pacificwebguy.com
> 
> 

If you have a specific f(x) and it is not too complex then you may be 
able to drive an expression for g(y) directly.

For example in the simple case of f(x) = x you can get to:
g(y) = x (x + 1) / 2

This is a simple case but many forms of f(x) could be turned into a 
series whose sum can be derived directly rather than by actually adding 
up the values.

Otherwise the approach that you outline to have three columns x, f(x), 
S(f(x)) would work well so long as the range is not great.  The 
calculation in S(f(x)) just accumulates the total from row to row.  You 
can then lookup the sum for a particular value of y.

I would not worry too much about optimisation since spreadsheets do not 
generally recalculate values if it is not necessary.

Otherwise as far as I can see you need to write a function.

Post a reply to this message

Paul Fuller <pgf### [at] optusnetcomau> wrote:
> If you have a specific f(x) and it is not too complex then you may be
> able to drive an expression for g(y) directly.
>
> For example in the simple case of f(x) = x you can get to:
> g(y) = x (x + 1) / 2

The original function is f(x) = M*R*(D^x), where M, R and D are constants.

I'd be using the function g(A) = sum of f(x), where x=0..A

Unfortunately, I don't know how to get a clean summation formula for this.  Even
integration doesn't work, since I want every value from floor(x) to ceil(x) to
return the same value; that is, the function is only defined for integral
values of x.

....Chambers
www.pacificwebguy.com

Post a reply to this message

"Chambers" <bdc### [at] yahoocom> wrote:

> The original function is f(x) = M*R*(D^x), where M, R and D are constants.
>
> I'd be using the function g(A) = sum of f(x), where x=0..A

If x is just integers from zero to a, then each term differs by an additional
factor of D.  Then you have the sum of a geometric series,

sum_{x=0...A} ( M*R*(D^x) )   =   M*R*(1-D^(A+1)) / (1-D).

This looks much nicer on the wikipedia page,
http://en.wikipedia.org/wiki/Geometric_progression .  Correct me if I'm
misinterpreting, but this should give you the summation in a single expression.

 - Ricky

Post a reply to this message

>> The original function is f(x) = M*R*(D^x), where M, R and D are 
>> constants.
>>
>> I'd be using the function g(A) = sum of f(x), where x=0..A

In open office the seriessum function comes close to what you want.
http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_SERIESSUM_function
I don't think spreadsheets have a way to pass a function as a
first class value though, you could write some basic macro but
that's what you want to avoid.

Post a reply to this message

Chambers wrote:
> So I have a certain function f(x), where x is a positive integer value.
> 
> I would like another function g(y) which is the sum of all values of f(x), with
> x ranging from 0..y
> 
> This is trivial using Sigma notation.  Unfortunately, this is not available in
> any spreadsheet I know of (I use MS Office at work, and OpenOffice.org at
> home).
> 
> 
> Does anyone here have an idea of how to calculate a sum this way?
> 

if for example, you have x values in a range C6:C7,
then you can use array functions (CTRL+Shift+Enter) like
=SUM(C4^C6:C7)

or, if there are not too many x values:

=SUM(C4^{1;2;3;4})

it can be used in both Excel and OpenOffice spreadsheets,
but check the list element delimiters.

Post a reply to this message

> The original function is f(x) = M*R*(D^x), where M, R and D are constants.
> 
> I'd be using the function g(A) = sum of f(x), where x=0..A
> 
> Unfortunately, I don't know how to get a clean summation formula for this.  Even
> integration doesn't work, since I want every value from floor(x) to ceil(x) to
> return the same value; that is, the function is only defined for integral
> values of x.

Also possible worksheet function for Excel:
if we have A in B4, M in B5, R in B6 and D in B7
then the (array) summation formula can be written as

=SUM(B5*B6*B7^(ROW(INDIRECT("a1:"&"a"&B4+1))-1))

Post a reply to this message

And lo on Fri, 09 May 2008 22:42:05 +0100, Chambers  
<bdc### [at] yahoocom> did spake, saying:

> So I have a certain function f(x), where x is a positive integer value.
>
> I would like another function g(y) which is the sum of all values of  
> f(x), with x ranging from 0..y
>
> This is trivial using Sigma notation.  Unfortunately, this is not  
> available in
> any spreadsheet I know of (I use MS Office at work, and OpenOffice.org at
> home).

I think the assumption is that anyone who knows Sigma notation will  
already know both the geometric and arithmetic progression formulas.

> I'd rather not write a macro for this,
[snip]

How would you input the x value into the function? if you had your  
M*R*(D^x) then had a GEOPROG(lower, upper, step, formula) how would you  
write the formula containing the variable x in a way Excel (or any  
spreadsheet) could interpret?

> Not to mention that every time I opened the spreadsheet, I'd have to  
> deal with those annoying security warnings about dangerous macros.

Yes annoying, but understandable that user-defined functions trigger the  
macro security flag. Though if it's something you're going to use a lot  
then you could just sign it and add the signature as a trusted source.

> I could populate an entire column on a separate sheet with the values I  
> need,

Yes unfortunately that's what the Excel SERIESSUM formula requires, and it  
wouldn't work for you anyway as the formula equivalent is sum(x*(D^x))  
where x is the range.

-- 
Phil Cook

--
I once tried to be apathetic, but I just couldn't be bothered
http://flipc.blogspot.com

Post a reply to this message

"triple_r" <nomail@nomail> wrote:
> "Chambers" <bdc### [at] yahoocom> wrote:
>
> > The original function is f(x) = M*R*(D^x), where M, R and D are constants.
> >
> > I'd be using the function g(A) = sum of f(x), where x=0..A
>
> If x is just integers from zero to a, then each term differs by an additional
> factor of D.  Then you have the sum of a geometric series,

Thanks; it's been long enough that I'd completely forgotten the geometric series
summation formula!

....Chambers

Post a reply to this message