1

If I want to sum the elements of the first row (from the column a to x) of a calc document I can use =sum(a1:x1).

My question is about the possibility to calculate something like this =sum(a1^2/(1+a1):x1^2/(1+x1)) (of course this causes an error).

I mean to obtain the sum

a1^2/(1+a1)+b1^2/(1+b1)+...+x1^2/(1+x1)

Improve this question

2 Answers 2

Reset to default
2

type A1:X1 in cell A3 - press ENTER
type =INDIRECT($A$3)^2/(INDIRECT($A$3)+1) in A4 - press SHIFT+ENTER
type =SUM(INDIRECT($A$3)) in cell B3 - press ENTER

Note that to change A4's content you need to select A4 and then the entire range first! Just clicking on A4 won't work.

To change A3's content - to adjust for any change of the range:
1. Select the entire range starting in A4
2. Edit A4 and make sure to COPY the content.
3. Press ESC to exit editing
4. Press DEL to erase the array formula
5. Change A3
6. Re-enter the copied formula in A4, press SHIFT+ENTER

This procedure is necessary in LibreOffice at least.
Excel does all this in a slightly different manner.

Improve this answer
2
  • In OpenOffice on a Windows computer the key combination for an array formula is Ctrl+Shift+Enter
    – Lyrl
    Jun 19, 2015 at 18:11
  • The above is tested in Libreoffice on Ubuntu 14.04... in Excel (Windows) it the array formula entry is done by CTRL+ENTER
    – Hannu
    Jun 19, 2015 at 19:40
2

In LibreOffice Calc the following works; since the two programs are mostly the same, it should work in OpenOffice Calc, too. Use the formula =SUM(A1:X1^2/(1+A1:X1)); then hit CTRL + Shift + Enter to make it an array formula.

In general, a formula that takes single values and returns a single value (e.g., =A1 + B1 and hit Enter) can be made to take and return a range by making it an array formula (e.g., formula is =A1:A3 + B1:B3 and hit Ctrl + Shift + Enter). In this example, the formula will return three values, A1+B1, A2+B2, and A3+B3. (If you type this in directly in cell C1, the program will also fill cells C2 and C3.) Rather than displaying those values directly on the spreadsheet, you can, as in the question, pass those values to SUM or any other function that accepts a range of values (=SUM(A1 + B1) and hit CTRL + Shift + Enter).

EDIT:
With the mathematical function f(x)=x2/(1+x) from your question, in Calc you can replace x either with a single cell (e.g., A1) in a regular formula, producing a single value, or with a range (e.g. A1:X1), producing one value for each value in the range. In your formula, you just replace the single cell with a range (A1:X1^2 etc. instead of A1^2 etc.) and hit CTRL + Shift + Enter to make it an array formula.

Technical info on how this works under the hood: A1:X1 in the formula is evaluated as the list (or array, hence the name array formula) {A1, B1, ..., X1}. A1:X1^2 produces the list {A1^2, B1^2, ..., X1^2}. Since A1:X1 is in the formula twice, there are two lists; when the division is evaluated, {A1^2, B1^2, ..., X1^2}/{1+A1, 1+B1, ..., 1+X1} becomes {A1^2/(1+A1), B1^2/(1+B1), ..., X1^2/(1+X1)}. This list is what SUM sums.

Improve this answer
2
  • Many thanks, it works. But I do not understand the syntax of =SUM(A1:X1^2/(1+A1:X1)). Here the function is x^2/(1+x). If I have another function like (x+4+2*x^7)/(1+x^2-7*x+) what is the correct syntax.
    – Name
    Jun 20, 2015 at 14:37
  • @Name: I edited the answer; I hope this clarifies the general idea. For your second example, just replace the variable x with the range instead of with a sigle cell.
    – j_foster
    Jun 20, 2015 at 15:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .