Ask Your Question
1

Does Calc have a summation function?

asked 2012-03-03 00:30:52 +0200

TJ Meneses gravatar image

updated 2012-03-06 10:09:16 +0200

I apologize if this question is pretty straightforward and possibly common knowledge, but I've been trying to google it in as many permutations of the question as my brain can allow, and I have not seen a single straightforward answer to this. The only real reference of LibreOffice even having an actual summation (not sum) feature is in Math, which is strictly notation.

Let me kindly clarify my question: I am not asking whether there is a SUM function (that is definitely common knowledge); I am asking whether or not there is a SUMMATION function (i.e. sum [expression] from i=a to b) in Calc.

If there is, kindly refer me to the correct formula/documentation of the function. If not, well... bummer. I'm having trouble finding this function in Excel as well, but a colleague mentioned it did have this, which is called through ADDSUM() or something. I wouldn't know; I'm a faithful user of Calc.

Edit: I suppose my question is still a little vague, or at least the details of what I'm trying to look for, so I'll make an example with a hypothetical function I'll call SUMM(). This function would receive 4 arguments, which is the same number of terms you need to input in the summation formula in Math.

Regarding the arguments. First, there would be the expression to be used, for example "5+6*i", where i is the variable you'll use to define your range. Second would be the letter you used in your expression, in this case "i". The third and fourth arguments would receive the lower and upper bounds of the range of values of i, respectively.

The format of the hypothetical function would look something like SUMM(expression,variable,lower_bound,upper_bound). It could link to other cells for certain values or references of the expression, but can also calculate a value without any references, given you supplied it with the proper information.

I hope I made my question clearer.

edit retag flag offensive close merge delete

Comments

I've discovered a possible solution to the (as of my current knowledge) lack of a summation function: Basic programming. Only problem is, I know next to nothing on how to use it. I'll try to make a user-defined function for the one I'm describing, and if it works, I'll post it as an answer.

TJ Meneses gravatar imageTJ Meneses ( 2012-03-10 17:37:18 +0200 )edit

10 Answers

Sort by » oldest newest most voted
0

answered 2012-03-16 16:07:29 +0200

TJ Meneses gravatar image

Based on my original question, and after doing a bit more research, it's become painfully obvious that LibreOffice Calc simply does not have a function for summations. There is no single function, or even combination of functions, that will calculate the sum of an expression f(x) from x=a to b (there are workarounds, but all techniques I've heard from others are array-based, not single-cell solutions).

The interesting thing is that my calculator has this pretty basic function, and the syntax and logic isn't even that difficult to implement. I suppose there just aren't enough people asking for an implementation of this function.

I'm still trying to implement it myself through the Basic programming language, but I'm also having a bit of trouble finding documentation for it. Maybe when I have free time, I'll decide to learn how to create an extension for it so other people might be able to use it.

edit flag offensive delete link more

Comments

@TJ Meneses The solution I demonstrate in my answer is, of course, array based, but doesn't need evaluation of many cells, hower, as you will see.

BTW Who did mark this non-answer as correct?

Lupp gravatar imageLupp ( 2015-07-26 14:04:35 +0200 )edit

"Who did mark this non-answer as correct?" : There are 2 sets of people allowed to select correct answers:- either the Questioner or a Moderator. My uninformed guess is that the OP selected it.

Alex Kemp gravatar imageAlex Kemp ( 2015-07-27 18:51:44 +0200 )edit
1

answered 2012-03-06 23:03:15 +0200

m.a.riosv gravatar image

Maybe: Menu/Data/MultipleOperations can help. See in the help how it works. To access the menu entry first select a range.

edit flag offensive delete link more
1

answered 2012-03-03 08:27:57 +0200

There is SUMIF(range; criteria; sum_range) function in Calc, which "Adds the cells specified by a given criteria. The search supports regular expressions. Range is the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criterion itself. Sum_range is the range from which values are summed; if it has not been indicated, the values found in the Range are med.". Information about all Calc function you can find here (Appendix B - Calc Functions). I hope this information will be helpful for you.

edit flag offensive delete link more

Comments

Irishman gravatar imageIrishman ( 2012-03-03 08:36:32 +0200 )edit

Thanks for that info. Actually, I have already browsed all of Calc's functions on that page before, and I do know about SUMIF(), but that's another array-based solution. What I'm looking for is a single-cell function where you can input all the data you need as arguments.

TJ Meneses gravatar imageTJ Meneses ( 2012-03-06 09:59:57 +0200 )edit
0

answered 2015-07-26 13:58:34 +0200

Lupp gravatar image

updated 2016-07-06 15:28:26 +0200

(Just coming to this thread by accident, I will try a structured answer.)

1) If the developers addressed the request it wasn't to simply add a function to the given supply, usable just for adding or even more general purposes, but to introduce and implement a new concept: bound variables suitable to describe the variation of an expression when having the bound variable running through a (discrete) range. Lets call the construct a Calculating Loop (CL). Mathematical software I know actually provides one or more functions implementing CL. There may be a SUM and a PRODUCT accepting running indizes. Surely there is at least one function returning a vector or a matrix based on (a) loop(s) to go through. One of the functions may allow for an expression VECTOR(EXP(k*LN(π)),k,0,10), and SUM(VECTOR(EXP(k*LN(π)),k,0,10)) may then return the values of π to the zeroth through tenth power added up. (The formula as shown will work in old DERIVE, e.g.)

You see: At least in the background there must be generated a vector (or a matrix of higher dimension) which then can be passed to a function for summing, or whatever ...

2) Can we do this within a Calc formula, too? Yes! Within reason! The only way to actually do it is, however, to use (abuse ?) the functions ROW() or/and COLUMN() for the generation of a running index under the condition of so called array-evaluation. The mentioned example would then read:

{=SUM((EXP((ROW(A1:A11)-1)*LN(PI()))))}
=SUMPRODUCT((EXP((ROW(A1:A11)-1)*LN(PI()))))

The first one entered as an array formula (Ctrl+Shift+Enter), the second one forcing array evaluation according to the specification of SUMPRODUCT.

This also demonstrates a way to apply a range not strictly limited to successive positive integer values.

3) There are issues, of course. I think we cannot introduce 3 or more independent bound variables this way, and we will get complicated formulae if the bound variable(s) shall occur in more than one places anyone.

[Edit with respect to the comment by @aplatypus 2016-07-05 ]
The announced example: ask835SumFunctionRCnotation001.ods
[/Edit]

edit flag offensive delete link more
0

answered 2016-07-07 22:11:34 +0200

mark_t gravatar image

updated 2016-07-07 22:26:39 +0200

You can create a user defined function in basic to return an array of integers from the lower to upper bound, then use an array function inside the SUM function.

Example:- (Edit to match example of OP)

=SUM( 5 + 6 * RANGEARRAY(1,10))

Where RANGEARRAY is defined as :-

REM  *****  BASIC  *****

Option Explicit

Function RANGEARRAY(lower As Long, upper As Long) As Variant

    Dim v As Variant
    Dim i As Long

    If upper >= lower Then
        Redim v ( 0 To upper - lower)

        For i = 0 to upper - lower
            v( i ) = lower + i
        Next i
    End If

    RangeArray = v
End Function

Note that as the array function is summed by the SUM function to a single value there is no need to enter the expression as an array function. If RANGEARRAY is used without the SUM function, then you need to enter as an array function, using ctrl-shift-Enter, in which case it returns a horizontal array of cells.

edit flag offensive delete link more
0

answered 2012-03-03 00:49:21 +0200

m.a.riosv gravatar image

Maybe it is possible to do with an array expression, but with a more detailed explanation about what to do, would be easier to find a solution.

edit flag offensive delete link more

Comments

Yeah, my current solution is array-based, but that takes up so much space than if there were a single function to fill one cell with. Additionally, trying to account for the variable nature of the upper and lower bounds is pure hell.

TJ Meneses gravatar imageTJ Meneses ( 2012-03-03 01:19:14 +0200 )edit
0

answered 2017-04-30 22:21:30 +0200

only thing i can think of is rationalising it as an equation:

x=(n^2+n)*0.5

unfortunately it only works for a basic additive equation (1+2+3...), and doesn't work for anything fancy like indices, though i've not tried it with multiplication or division. i based it on the idea that 1+4 and 2+3 both give you 5, so n=5, ∑(n)=15.

gotta love sigma.

edit flag offensive delete link more
0

answered 2012-09-08 05:48:36 +0200

This requires the Eval() function from here, but this seems to work fine:

Function SUMMATION(lowerbound, upperbound, variable, formula)
    SUMMATION=0
    FOR i=lowerbound to upperbound
        this=join(split(formula, variable), i)
        SUMMATION=SUMMATION+EVAL(this)
    NEXT i
End Function
edit flag offensive delete link more

Comments

There isn't an EVAL function available for user programming for Calc.

Lupp gravatar imageLupp ( 2015-07-26 22:02:48 +0200 )edit
0

answered 2015-07-26 08:17:17 +0200

Duoas gravatar image

It most certainly does have a summation function, and it is named... SUM!

Your problem is that you must select a range of cells. For example, if I wish to sum cells A1 through A7, the code would be a simple

=SUM(A1:A7)

Hope this helps.

edit flag offensive delete link more

Comments

The original questioner explicitly stated that, and for what reasons, SUM wasn't a solution for his problem.

Lupp gravatar imageLupp ( 2015-07-26 13:03:23 +0200 )edit

I'm quite disappointed. The "SUM()" function was one of the foundation operators in VisiCalc, it is simple and straightforward in spreadsheet products where it is implemented correctly. Unfortunately on LibreOffice, this formula:

  • =SUM(R2C:R14C)

Row/Column reference, it Displays: ".00"

The correct answer is there are two numbers in that column that add to 737, the rest of the cells are empty. EVERY single spreadsheet I've ever used and that's at least half-a-dozen all give me 737.

aplatypus gravatar imageaplatypus ( 2016-07-05 14:39:21 +0200 )edit

@aplatypus: May I ask you to prove your statement?
You may refer to the example I will attach to my original answer (2015-07-26)

Lupp gravatar imageLupp ( 2016-07-06 15:25:27 +0200 )edit
-1

answered 2012-03-06 10:57:44 +0200

yecril71pl gravatar image

Sum of (5 + 6 i), where (i) ranges from (0) to (N), will be (N (3 N + 8) + 5). Pretty straightforward.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

Stats

Asked: 2012-03-03 00:30:52 +0200

Seen: 5,335 times

Last updated: Apr 30 '17