Changing formula (with consecutive relative references) into an array formula so it will work with google...
How do I change the following formula into an array formula so it will work even when new rows are added by google forms?
=if(SUMPRODUCT($D5:$D7) < 1,"Bad","Good")
Background:
Column D contains either TRUE or FALSE values. The formula simply looks back and if there are 3 Falses in a row, I need the output to be "Bad". This works fine if I copy it down. But, this sheet is linked to a google form and google adds a new row for new responses.
For another part of the sheet I figured out that if I make an array formula, then the array formula will take effect even though google inserts a new row (no need to copy down). Example of what works: =ArrayFormula(DAY($C2:C) = $A$1). This works from $C2 all the way down the rest of column C and so works with rows inserted by google forms.
How can I make the first formula work like the second while still looking back at the last three relative values?
google-sheets array-formulas google-sheets-formula
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
add a comment |
How do I change the following formula into an array formula so it will work even when new rows are added by google forms?
=if(SUMPRODUCT($D5:$D7) < 1,"Bad","Good")
Background:
Column D contains either TRUE or FALSE values. The formula simply looks back and if there are 3 Falses in a row, I need the output to be "Bad". This works fine if I copy it down. But, this sheet is linked to a google form and google adds a new row for new responses.
For another part of the sheet I figured out that if I make an array formula, then the array formula will take effect even though google inserts a new row (no need to copy down). Example of what works: =ArrayFormula(DAY($C2:C) = $A$1). This works from $C2 all the way down the rest of column C and so works with rows inserted by google forms.
How can I make the first formula work like the second while still looking back at the last three relative values?
google-sheets array-formulas google-sheets-formula
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
add a comment |
How do I change the following formula into an array formula so it will work even when new rows are added by google forms?
=if(SUMPRODUCT($D5:$D7) < 1,"Bad","Good")
Background:
Column D contains either TRUE or FALSE values. The formula simply looks back and if there are 3 Falses in a row, I need the output to be "Bad". This works fine if I copy it down. But, this sheet is linked to a google form and google adds a new row for new responses.
For another part of the sheet I figured out that if I make an array formula, then the array formula will take effect even though google inserts a new row (no need to copy down). Example of what works: =ArrayFormula(DAY($C2:C) = $A$1). This works from $C2 all the way down the rest of column C and so works with rows inserted by google forms.
How can I make the first formula work like the second while still looking back at the last three relative values?
google-sheets array-formulas google-sheets-formula
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
How do I change the following formula into an array formula so it will work even when new rows are added by google forms?
=if(SUMPRODUCT($D5:$D7) < 1,"Bad","Good")
Background:
Column D contains either TRUE or FALSE values. The formula simply looks back and if there are 3 Falses in a row, I need the output to be "Bad". This works fine if I copy it down. But, this sheet is linked to a google form and google adds a new row for new responses.
For another part of the sheet I figured out that if I make an array formula, then the array formula will take effect even though google inserts a new row (no need to copy down). Example of what works: =ArrayFormula(DAY($C2:C) = $A$1). This works from $C2 all the way down the rest of column C and so works with rows inserted by google forms.
How can I make the first formula work like the second while still looking back at the last three relative values?
google-sheets array-formulas google-sheets-formula
google-sheets array-formulas google-sheets-formula
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
edited Nov 24 '18 at 1:04
Rubén
10.1k43366
10.1k43366
asked Nov 23 '18 at 12:17
silent_john
123
asked Nov 23 '18 at 12:17
silent_john
123
asked Nov 23 '18 at 12:17
silent_john
123
123
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Well the direct equivalent would be
=ArrayFormula(if(A4:A="","",A4:A+A3:A+A2:A))
but this doesn't work - it will always need more rows to be inserted at the bottom of the sheet.
=ArrayFormula(if(A4:A="","",A4:A+A3:A999+A2:A998))
does work, but only for a certain sheet size.
You could look ahead like this though:
ArrayFormula(if(A2:A="","",A2:A+A3:A+A4:A))
A more complicated way to get round it is to do a vlookup on the row number like this:
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false))))
(you need to add if statements to make it report "Good" or "Bad")
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",IF(A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false),"Good","Bad"))))
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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
});
}
});
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%2fstackoverflow.com%2fquestions%2f53446583%2fchanging-formula-with-consecutive-relative-references-into-an-array-formula-so%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
Well the direct equivalent would be
=ArrayFormula(if(A4:A="","",A4:A+A3:A+A2:A))
but this doesn't work - it will always need more rows to be inserted at the bottom of the sheet.
=ArrayFormula(if(A4:A="","",A4:A+A3:A999+A2:A998))
does work, but only for a certain sheet size.
You could look ahead like this though:
ArrayFormula(if(A2:A="","",A2:A+A3:A+A4:A))
A more complicated way to get round it is to do a vlookup on the row number like this:
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false))))
(you need to add if statements to make it report "Good" or "Bad")
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",IF(A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false),"Good","Bad"))))
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
add a comment |
Well the direct equivalent would be
=ArrayFormula(if(A4:A="","",A4:A+A3:A+A2:A))
but this doesn't work - it will always need more rows to be inserted at the bottom of the sheet.
=ArrayFormula(if(A4:A="","",A4:A+A3:A999+A2:A998))
does work, but only for a certain sheet size.
You could look ahead like this though:
ArrayFormula(if(A2:A="","",A2:A+A3:A+A4:A))
A more complicated way to get round it is to do a vlookup on the row number like this:
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false))))
(you need to add if statements to make it report "Good" or "Bad")
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",IF(A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false),"Good","Bad"))))
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
add a comment |
Well the direct equivalent would be
=ArrayFormula(if(A4:A="","",A4:A+A3:A+A2:A))
but this doesn't work - it will always need more rows to be inserted at the bottom of the sheet.
=ArrayFormula(if(A4:A="","",A4:A+A3:A999+A2:A998))
does work, but only for a certain sheet size.
You could look ahead like this though:
ArrayFormula(if(A2:A="","",A2:A+A3:A+A4:A))
A more complicated way to get round it is to do a vlookup on the row number like this:
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false))))
(you need to add if statements to make it report "Good" or "Bad")
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",IF(A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false),"Good","Bad"))))
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
Well the direct equivalent would be
=ArrayFormula(if(A4:A="","",A4:A+A3:A+A2:A))
but this doesn't work - it will always need more rows to be inserted at the bottom of the sheet.
=ArrayFormula(if(A4:A="","",A4:A+A3:A999+A2:A998))
does work, but only for a certain sheet size.
You could look ahead like this though:
ArrayFormula(if(A2:A="","",A2:A+A3:A+A4:A))
A more complicated way to get round it is to do a vlookup on the row number like this:
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false))))
(you need to add if statements to make it report "Good" or "Bad")
=ArrayFormula(if(row(A2:A)<4,"",if(A2:A="","",IF(A2:A+vlookup(row(A2:A)-2,{row(A2:A),A2:A},2,false)+vlookup(row(A2:A)-1,{row(A2:A),A2:A},2,false),"Good","Bad"))))
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
edited Nov 23 '18 at 17:10
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
answered Nov 23 '18 at 14:12
Tom Sharpe
12.1k31224
12.1k31224
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
add a comment |
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
Actually your first version fixed my problem. I only ever use this with google form responses so google is always inserting a row at the bottom of the sheet for a new response. Your first solution works great for me.
– silent_john
Nov 24 '18 at 7:50
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53446583%2fchanging-formula-with-consecutive-relative-references-into-an-array-formula-so%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
