How to create an array of values in LibreOffice Calc?
up vote
3
down vote
favorite
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?
worksheet-function openoffice libreoffice spreadsheet openoffice-calc
asked Sep 23 '16 at 16:51
tripu
566
add a comment |
up vote
3
down vote
favorite
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?
worksheet-function openoffice libreoffice spreadsheet openoffice-calc
asked Sep 23 '16 at 16:51
tripu
566
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.
– kwutchak
Nov 23 at 19:48
add a comment |
up vote
3
down vote
favorite
up vote
3
down vote
favorite
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?
worksheet-function openoffice libreoffice spreadsheet openoffice-calc
asked Sep 23 '16 at 16:51
tripu
566
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?
worksheet-function openoffice libreoffice spreadsheet openoffice-calc
worksheet-function openoffice libreoffice spreadsheet openoffice-calc
asked Sep 23 '16 at 16:51
tripu
566
asked Sep 23 '16 at 16:51
tripu
566
asked Sep 23 '16 at 16:51
tripu
566
asked Sep 23 '16 at 16:51
tripu
566
asked Sep 23 '16 at 16:51
tripu
566
566
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.
– kwutchak
Nov 23 at 19:48
add a comment |
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.
– kwutchak
Nov 23 at 19:48
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.
– kwutchak
Nov 23 at 19:48
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.
– kwutchak
Nov 23 at 19:48
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
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.
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
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
Jul 7 '17 at 13:45
add a comment |
up vote
1
down vote
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...
edited Nov 15 at 20:23
zx485
632513
answered Nov 15 at 20:08
kwutchak
267412
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.
– tripu
Nov 20 at 23:22
add a comment |
up vote
0
down vote
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.
answered Nov 23 at 20:03
kwutchak
267412
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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.
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
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
Jul 7 '17 at 13:45
add a comment |
up vote
2
down vote
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.
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
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
Jul 7 '17 at 13:45
add a comment |
up vote
2
down vote
up vote
2
down vote
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.
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
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.
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
answered Apr 25 '17 at 20:33
Jim DeLaHunt
22519
22519
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
Jul 7 '17 at 13:45
add a comment |
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
Jul 7 '17 at 13:45
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
Jul 7 '17 at 13:45
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
Jul 7 '17 at 13:45
add a comment |
up vote
1
down vote
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...
edited Nov 15 at 20:23
zx485
632513
answered Nov 15 at 20:08
kwutchak
267412
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.
– tripu
Nov 20 at 23:22
add a comment |
up vote
1
down vote
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...
edited Nov 15 at 20:23
zx485
632513
answered Nov 15 at 20:08
kwutchak
267412
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.
– tripu
Nov 20 at 23:22
add a comment |
up vote
1
down vote
up vote
1
down vote
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...
edited Nov 15 at 20:23
zx485
632513
answered Nov 15 at 20:08
kwutchak
267412
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...
edited Nov 15 at 20:23
zx485
632513
answered Nov 15 at 20:08
kwutchak
267412
edited Nov 15 at 20:23
zx485
632513
edited Nov 15 at 20:23
zx485
632513
edited Nov 15 at 20:23
zx485
632513
632513
answered Nov 15 at 20:08
kwutchak
267412
answered Nov 15 at 20:08
kwutchak
267412
answered Nov 15 at 20:08
kwutchak
267412
267412
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.
– tripu
Nov 20 at 23:22
add a comment |
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.
– tripu
Nov 20 at 23:22
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.
– tripu
Nov 20 at 23:22
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.
– tripu
Nov 20 at 23:22
add a comment |
up vote
0
down vote
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.
answered Nov 23 at 20:03
kwutchak
267412
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
add a comment |
up vote
0
down vote
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.
answered Nov 23 at 20:03
kwutchak
267412
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
add a comment |
up vote
0
down vote
up vote
0
down vote
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.
answered Nov 23 at 20:03
kwutchak
267412
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.
answered Nov 23 at 20:03
kwutchak
267412
answered Nov 23 at 20:03
kwutchak
267412
answered Nov 23 at 20:03
kwutchak
267412
answered Nov 23 at 20:03
kwutchak
267412
267412
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
add a comment |
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
@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
Nov 23 at 20:05
@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
Nov 23 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!
– kwutchak
Nov 23 at 20:07
... personally if somebody else is interested in filling in the implementation details for Calc in this solution I'd love to see them!
– kwutchak
Nov 23 at 20:07
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1127609%2fhow-to-create-an-array-of-values-in-libreoffice-calc%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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.
– kwutchak
Nov 23 at 19:48