Does Calc have a summation function?

English
, , ,
#1

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.

#2

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.

#5

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.

#6

Article in documentation with example about SUMIF function - http://help.libreoffice.org/scalc/SC_HID_FUNC_SUMMEWENN?Language=en&System=WIN&Version=3.5#bm_id3151969

#7

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.

#9

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

#3

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.

#4

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.

#8

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

#10

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.

#11

@TJMeneses 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?

#12

“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.

#13

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
#14

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

#15

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.

#16

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

#17

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.

#18

@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)

#19

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

  1. 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.

  1. 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]

#20

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.