# How to create an array of values in LibreOffice Calc?

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?

• I believe I've been confused what you were after here, sorry about that. In your example above do the cells A1:A100 all contain a single fixed payment? That is, the spreadsheet equivalent of { 7 | 7 | 7 | 7 ... 95x ... 7 }. Is your question "What if I needed { 7 | 7 | 7 | 7 ... 100,995x ... 7 } instead"? Now that I read more carefully, that appears to be how Jim DeLaHunt has interpreted the question. Nov 23 '18 at 19:48

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.

• 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. Jul 7 '17 at 13:45

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

• 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. Nov 20 '18 at 23:22

Write a function which creates a repeating array based on cells in the spreadsheet.

I haven't written any code for Calc, so I'm not familiar with the syntax used, but in pseudo-code:

``````function repeat( value, amount ) -> { value | value | value ... value }
``````

... then call this code from IRR.

Alternatively, create a function that does everything:

``````function repeatIrr( value, amount ) -> value
``````

... and call that from the spreadsheet.

• @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. Nov 23 '18 at 20:05
• ... personally if somebody else is interested in filling in the implementation details for Calc in this solution I'd love to see them! Nov 23 '18 at 20:07