Formula in Excel or Numbers to generate a series of numbers
0
In my spreadsheet I have two numbers, start_number and nr_iterations. I'm looking for an formula that returns a series of numbers starting with start_number - 1 and nr_iterations long (each following item with the value of one lower).
So like this:
(nr_iterations, start_number) => [my_number_serie]
(0, 1) =>
(1, 3) => [2]
(2, 5) => [4, 3]
(3, 7) => [6, 5, 4]
(4, 9) => [8, 7, 6, 5]
Basically, start_number is also calculated, but probably is not too important:
start_number = 1 + nr_iterations*2
I'm actually interested in the SUM of this serie numbers, if that's of any help.
microsoft-excel spreadsheet iwork-numbers
asked Jan 5 at 15:54
doekmandoekman
1961719
add a comment |
0
In my spreadsheet I have two numbers, start_number and nr_iterations. I'm looking for an formula that returns a series of numbers starting with start_number - 1 and nr_iterations long (each following item with the value of one lower).
So like this:
(nr_iterations, start_number) => [my_number_serie]
(0, 1) =>
(1, 3) => [2]
(2, 5) => [4, 3]
(3, 7) => [6, 5, 4]
(4, 9) => [8, 7, 6, 5]
Basically, start_number is also calculated, but probably is not too important:
start_number = 1 + nr_iterations*2
I'm actually interested in the SUM of this serie numbers, if that's of any help.
microsoft-excel spreadsheet iwork-numbers
asked Jan 5 at 15:54
doekmandoekman
1961719
add a comment |
0
0
0
In my spreadsheet I have two numbers, start_number and nr_iterations. I'm looking for an formula that returns a series of numbers starting with start_number - 1 and nr_iterations long (each following item with the value of one lower).
So like this:
(nr_iterations, start_number) => [my_number_serie]
(0, 1) =>
(1, 3) => [2]
(2, 5) => [4, 3]
(3, 7) => [6, 5, 4]
(4, 9) => [8, 7, 6, 5]
Basically, start_number is also calculated, but probably is not too important:
start_number = 1 + nr_iterations*2
I'm actually interested in the SUM of this serie numbers, if that's of any help.
microsoft-excel spreadsheet iwork-numbers
asked Jan 5 at 15:54
doekmandoekman
1961719
In my spreadsheet I have two numbers, start_number and nr_iterations. I'm looking for an formula that returns a series of numbers starting with start_number - 1 and nr_iterations long (each following item with the value of one lower).
So like this:
(nr_iterations, start_number) => [my_number_serie]
(0, 1) =>
(1, 3) => [2]
(2, 5) => [4, 3]
(3, 7) => [6, 5, 4]
(4, 9) => [8, 7, 6, 5]
Basically, start_number is also calculated, but probably is not too important:
start_number = 1 + nr_iterations*2
I'm actually interested in the SUM of this serie numbers, if that's of any help.
microsoft-excel spreadsheet iwork-numbers
microsoft-excel spreadsheet iwork-numbers
asked Jan 5 at 15:54
doekmandoekman
1961719
asked Jan 5 at 15:54
doekmandoekman
1961719
asked Jan 5 at 15:54
doekmandoekman
1961719
asked Jan 5 at 15:54
doekmandoekman
1961719
asked Jan 5 at 15:54
doekmandoekman
1961719
1961719
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Enter the following formulae in the cells indicated:-
[A1] =ROW(A1)-1
[B1] =2*A1+1
[C1] =B1*(B1-1)/2-A1*(A1+1)/2
Then copy A1:C1 down as many rows as you need:
Notes:-
- If you want column headers and you need your data to start at a different row, then change the offset in the first cell (eg to start from row 3,
A3should be=ROW(A3)-3). - The sum in column
Cis derived using the formula that the sum of the first n integers is n*(n+1)/2. - I tested using LibreOffice, but Excel will be compatible.
answered Jan 5 at 16:38
AFHAFH
14.2k31938
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to[C1] =(3*A1*A1+A1)/2so you don't need[B1], but your example is more clear.
– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into[D1] = B1*(B1-1)/2and[E1] =A1*(A1+1)/2, so we can redefine[C1] = D1-E1. In this,E1isSUM(1..A1), but what isD1?
– doekman
Jan 6 at 17:37
D1isSUM(1..B1)-B1, ieSUM(1..(B1-1)). Another way to see the formula is to substituteB1-1for n in n*(n+1)/2.
– AFH
Jan 6 at 17:56
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
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%2f1390926%2fformula-in-excel-or-numbers-to-generate-a-series-of-numbers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Enter the following formulae in the cells indicated:-
[A1] =ROW(A1)-1
[B1] =2*A1+1
[C1] =B1*(B1-1)/2-A1*(A1+1)/2
Then copy A1:C1 down as many rows as you need:
Notes:-
- If you want column headers and you need your data to start at a different row, then change the offset in the first cell (eg to start from row 3,
A3should be=ROW(A3)-3). - The sum in column
Cis derived using the formula that the sum of the first n integers is n*(n+1)/2. - I tested using LibreOffice, but Excel will be compatible.
answered Jan 5 at 16:38
AFHAFH
14.2k31938
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to[C1] =(3*A1*A1+A1)/2so you don't need[B1], but your example is more clear.
– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into[D1] = B1*(B1-1)/2and[E1] =A1*(A1+1)/2, so we can redefine[C1] = D1-E1. In this,E1isSUM(1..A1), but what isD1?
– doekman
Jan 6 at 17:37
D1isSUM(1..B1)-B1, ieSUM(1..(B1-1)). Another way to see the formula is to substituteB1-1for n in n*(n+1)/2.
– AFH
Jan 6 at 17:56
add a comment |
1
Enter the following formulae in the cells indicated:-
[A1] =ROW(A1)-1
[B1] =2*A1+1
[C1] =B1*(B1-1)/2-A1*(A1+1)/2
Then copy A1:C1 down as many rows as you need:
Notes:-
- If you want column headers and you need your data to start at a different row, then change the offset in the first cell (eg to start from row 3,
A3should be=ROW(A3)-3). - The sum in column
Cis derived using the formula that the sum of the first n integers is n*(n+1)/2. - I tested using LibreOffice, but Excel will be compatible.
answered Jan 5 at 16:38
AFHAFH
14.2k31938
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to[C1] =(3*A1*A1+A1)/2so you don't need[B1], but your example is more clear.
– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into[D1] = B1*(B1-1)/2and[E1] =A1*(A1+1)/2, so we can redefine[C1] = D1-E1. In this,E1isSUM(1..A1), but what isD1?
– doekman
Jan 6 at 17:37
D1isSUM(1..B1)-B1, ieSUM(1..(B1-1)). Another way to see the formula is to substituteB1-1for n in n*(n+1)/2.
– AFH
Jan 6 at 17:56
add a comment |
1
1
1
Enter the following formulae in the cells indicated:-
[A1] =ROW(A1)-1
[B1] =2*A1+1
[C1] =B1*(B1-1)/2-A1*(A1+1)/2
Then copy A1:C1 down as many rows as you need:
Notes:-
- If you want column headers and you need your data to start at a different row, then change the offset in the first cell (eg to start from row 3,
A3should be=ROW(A3)-3). - The sum in column
Cis derived using the formula that the sum of the first n integers is n*(n+1)/2. - I tested using LibreOffice, but Excel will be compatible.
answered Jan 5 at 16:38
AFHAFH
14.2k31938
Enter the following formulae in the cells indicated:-
[A1] =ROW(A1)-1
[B1] =2*A1+1
[C1] =B1*(B1-1)/2-A1*(A1+1)/2
Then copy A1:C1 down as many rows as you need:
Notes:-
- If you want column headers and you need your data to start at a different row, then change the offset in the first cell (eg to start from row 3,
A3should be=ROW(A3)-3). - The sum in column
Cis derived using the formula that the sum of the first n integers is n*(n+1)/2. - I tested using LibreOffice, but Excel will be compatible.
answered Jan 5 at 16:38
AFHAFH
14.2k31938
answered Jan 5 at 16:38
AFHAFH
14.2k31938
answered Jan 5 at 16:38
AFHAFH
14.2k31938
answered Jan 5 at 16:38
AFHAFH
14.2k31938
14.2k31938
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to[C1] =(3*A1*A1+A1)/2so you don't need[B1], but your example is more clear.
– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into[D1] = B1*(B1-1)/2and[E1] =A1*(A1+1)/2, so we can redefine[C1] = D1-E1. In this,E1isSUM(1..A1), but what isD1?
– doekman
Jan 6 at 17:37
D1isSUM(1..B1)-B1, ieSUM(1..(B1-1)). Another way to see the formula is to substituteB1-1for n in n*(n+1)/2.
– AFH
Jan 6 at 17:56
add a comment |
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to[C1] =(3*A1*A1+A1)/2so you don't need[B1], but your example is more clear.
– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into[D1] = B1*(B1-1)/2and[E1] =A1*(A1+1)/2, so we can redefine[C1] = D1-E1. In this,E1isSUM(1..A1), but what isD1?
– doekman
Jan 6 at 17:37
D1isSUM(1..B1)-B1, ieSUM(1..(B1-1)). Another way to see the formula is to substituteB1-1for n in n*(n+1)/2.
– AFH
Jan 6 at 17:56
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to
[C1] =(3*A1*A1+A1)/2 so you don't need [B1], but your example is more clear.– doekman
Jan 5 at 20:02
That's it. And thanks for the explanation how to sum up the first n integer. It can actually be rewritten to
[C1] =(3*A1*A1+A1)/2 so you don't need [B1], but your example is more clear.– doekman
Jan 5 at 20:02
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
I did realise that, but I left it as I did so as to show the logic of the calculation, as well as allowing for different upper bounds to the range.
– AFH
Jan 5 at 20:35
Hmm, I don't get it completely. If we break the formula from C1 into
[D1] = B1*(B1-1)/2 and [E1] =A1*(A1+1)/2, so we can redefine [C1] = D1-E1. In this, E1 is SUM(1..A1), but what is D1?– doekman
Jan 6 at 17:37
Hmm, I don't get it completely. If we break the formula from C1 into
[D1] = B1*(B1-1)/2 and [E1] =A1*(A1+1)/2, so we can redefine [C1] = D1-E1. In this, E1 is SUM(1..A1), but what is D1?– doekman
Jan 6 at 17:37
D1 is SUM(1..B1)-B1, ie SUM(1..(B1-1)). Another way to see the formula is to substitute B1-1 for n in n*(n+1)/2.– AFH
Jan 6 at 17:56
D1 is SUM(1..B1)-B1, ie SUM(1..(B1-1)). Another way to see the formula is to substitute B1-1 for n in n*(n+1)/2.– AFH
Jan 6 at 17:56
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f1390926%2fformula-in-excel-or-numbers-to-generate-a-series-of-numbers%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
