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