How to create an array of values in LibreOffice Calc?

Question

I'm using LibreOffice 5.1.4.2 Calc and need to calculate the internal rate of return for a certain payment (say, in cell A1), a certain number of times (say, 100).

If I had the value repeated 100 times (say, in A1:A100), I could do:

=IRR(A1:A100)

But it seems odd (what if it's 100,000 times?).

The problem is, the function IIR expects “an array containing the values”.

How can I pass along to IRR an array of the value in A1 repeated 100 times?

Solution 1

I think you are asking Libreoffice Calc to do something it cannot do.

In LibreOffice Calc, an array is a "a linked range of cells on a spreadsheet containing values" (per Help article Array Functions). There is such a thing as an "Inline Array Constant", but that's for putting values into an array. IRR() is not an array function, it is a single-valued function which takes an array as an argument.

I think LibreOffice Calc wants to you allocate those 100 (or 100,000) cells with the same value.

You can populate the cells in various ways. I would put the value in the first cell, then give the second cell a formula that read from the first cell with an absolute reference, then fill that second cell down 100 (or 100,000) times to get the array I needed. All I have to change is the value in the first cell, and it gets propagated through the array.

If you really want to type a concise formula to calculate the Internal Rate of Return for 100,000 payments of the same amount, maybe LibreOffice isn't the best tool for the job. It's a spreadsheet. A programming language, like the Python language and the numpy.irr() function, might work better.

Solution 2

Yes, it is possible to write the array directly in your formula.
Calc refers to this as an "inline array constant".

A simple 3x2 example is

{1;2;3|"a";"b";"c"}.

Calc's Documentation has full details...

Comments

[tripu]

Thank you, @jim-delahunt. I still find it odd that there's no elegant way to do what I need (without actually using n cells), so I'm not accepting your answer as the good one just yet, in case someone else weighs in on this.

[tripu]

Again, my problem is that I may need to use a very long array. Imagine 300x2,000 instead. This syntax doesn't help with that.

[kwutchak]

@tripu I assume this isn't what you are personally after (feel free to correct me on that!). I'm adding this answer in as it would be a solution, and may be acceptable to someone in a similar situation.

[kwutchak]

... personally if somebody else is interested in filling in the implementation details for Calc in this solution I'd love to see them!