Calculate total time of a value being bigger than X
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a table that contains dates, time and temperature. I'm trying to find a way to easily get a short version of the table that will give me the date and total time if the temperature was higher than 6.5 (for example).
Any help would be appreciated, thanks!
excel excel-formula
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
add a comment |
I have a table that contains dates, time and temperature. I'm trying to find a way to easily get a short version of the table that will give me the date and total time if the temperature was higher than 6.5 (for example).
Any help would be appreciated, thanks!
excel excel-formula
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
1
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47
add a comment |
I have a table that contains dates, time and temperature. I'm trying to find a way to easily get a short version of the table that will give me the date and total time if the temperature was higher than 6.5 (for example).
Any help would be appreciated, thanks!
excel excel-formula
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
I have a table that contains dates, time and temperature. I'm trying to find a way to easily get a short version of the table that will give me the date and total time if the temperature was higher than 6.5 (for example).
Any help would be appreciated, thanks!
excel excel-formula
excel excel-formula
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
asked Nov 29 '18 at 6:38
Firefox17Firefox17
123
123
1
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47
add a comment |
1
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47
1
1
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47
add a comment |
2 Answers
2
active
oldest
votes
i got it somewhat right, but without the total time it was over the temperature.
i did helper cell in C1 (over what temperature you want to show on table)
then on A2 i putted
=IFERROR(INDEX(E$2:E12;SMALL(IF(G$2:G12>C$1;ROW(E$2:E12)-MIN(ROW(E$2:E12))+1);ROWS($A$2:A2)));"")
Insert as Array formula CTRL+SHIFT+ENTER, then you can drag it downwards as far as you would like
On column B (B2)
=IFERROR(INDEX(F$2:F12;SMALL(IF(G$2:G12>C$1;ROW(F$2:F12)-MIN(ROW(F$2:F12))+1);ROWS($B$2:B2)));"")
also inster as array CTRL+SHIFT+ENTER
should look something like this
just fiddle with the range as needed
answered Nov 29 '18 at 10:38
MikaelMikael
403
add a comment |
You can do it with offset arrays, to find the time where the temperature in the original array (G2:G14) is <=6.5 and the temperature in the shifted array (G3:G15) is >6.5, then subtract it from the time when the temperature in the original array is >6.5 and the temperature in the shifted array is <=6.5.
=INDEX(F2:F14,MATCH(1,(G2:G14>6.5)*(G3:G15<=6.5),0))-INDEX(F3:F15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
Must be entered as an array formula using CtrlShiftEnter
Non-array version:
=INDEX(F2:F14,MATCH(1,INDEX((G2:G14>6.5)*(G3:G15<=6.5),0),0))-INDEX(F3:F15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
But @Dominique is right, if there was only one point above 6.5 degrees you could argue that the time spent above 6.5 rather than being zero was at least 10 and possibly 20 minutes, because of the 10 minute spacing of the time points and the fact that the temperature seems to be uniformly increasing then decreasing.
=INDEX(E3:E15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
or
=INDEX(E3:E15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
for the date.
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
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%2f53533178%2fcalculate-total-time-of-a-value-being-bigger-than-x%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
i got it somewhat right, but without the total time it was over the temperature.
i did helper cell in C1 (over what temperature you want to show on table)
then on A2 i putted
=IFERROR(INDEX(E$2:E12;SMALL(IF(G$2:G12>C$1;ROW(E$2:E12)-MIN(ROW(E$2:E12))+1);ROWS($A$2:A2)));"")
Insert as Array formula CTRL+SHIFT+ENTER, then you can drag it downwards as far as you would like
On column B (B2)
=IFERROR(INDEX(F$2:F12;SMALL(IF(G$2:G12>C$1;ROW(F$2:F12)-MIN(ROW(F$2:F12))+1);ROWS($B$2:B2)));"")
also inster as array CTRL+SHIFT+ENTER
should look something like this
just fiddle with the range as needed
answered Nov 29 '18 at 10:38
MikaelMikael
403
add a comment |
i got it somewhat right, but without the total time it was over the temperature.
i did helper cell in C1 (over what temperature you want to show on table)
then on A2 i putted
=IFERROR(INDEX(E$2:E12;SMALL(IF(G$2:G12>C$1;ROW(E$2:E12)-MIN(ROW(E$2:E12))+1);ROWS($A$2:A2)));"")
Insert as Array formula CTRL+SHIFT+ENTER, then you can drag it downwards as far as you would like
On column B (B2)
=IFERROR(INDEX(F$2:F12;SMALL(IF(G$2:G12>C$1;ROW(F$2:F12)-MIN(ROW(F$2:F12))+1);ROWS($B$2:B2)));"")
also inster as array CTRL+SHIFT+ENTER
should look something like this
just fiddle with the range as needed
answered Nov 29 '18 at 10:38
MikaelMikael
403
add a comment |
i got it somewhat right, but without the total time it was over the temperature.
i did helper cell in C1 (over what temperature you want to show on table)
then on A2 i putted
=IFERROR(INDEX(E$2:E12;SMALL(IF(G$2:G12>C$1;ROW(E$2:E12)-MIN(ROW(E$2:E12))+1);ROWS($A$2:A2)));"")
Insert as Array formula CTRL+SHIFT+ENTER, then you can drag it downwards as far as you would like
On column B (B2)
=IFERROR(INDEX(F$2:F12;SMALL(IF(G$2:G12>C$1;ROW(F$2:F12)-MIN(ROW(F$2:F12))+1);ROWS($B$2:B2)));"")
also inster as array CTRL+SHIFT+ENTER
should look something like this
just fiddle with the range as needed
answered Nov 29 '18 at 10:38
MikaelMikael
403
i got it somewhat right, but without the total time it was over the temperature.
i did helper cell in C1 (over what temperature you want to show on table)
then on A2 i putted
=IFERROR(INDEX(E$2:E12;SMALL(IF(G$2:G12>C$1;ROW(E$2:E12)-MIN(ROW(E$2:E12))+1);ROWS($A$2:A2)));"")
Insert as Array formula CTRL+SHIFT+ENTER, then you can drag it downwards as far as you would like
On column B (B2)
=IFERROR(INDEX(F$2:F12;SMALL(IF(G$2:G12>C$1;ROW(F$2:F12)-MIN(ROW(F$2:F12))+1);ROWS($B$2:B2)));"")
also inster as array CTRL+SHIFT+ENTER
should look something like this
just fiddle with the range as needed
answered Nov 29 '18 at 10:38
MikaelMikael
403
answered Nov 29 '18 at 10:38
MikaelMikael
403
answered Nov 29 '18 at 10:38
MikaelMikael
403
answered Nov 29 '18 at 10:38
MikaelMikael
403
403
add a comment |
add a comment |
You can do it with offset arrays, to find the time where the temperature in the original array (G2:G14) is <=6.5 and the temperature in the shifted array (G3:G15) is >6.5, then subtract it from the time when the temperature in the original array is >6.5 and the temperature in the shifted array is <=6.5.
=INDEX(F2:F14,MATCH(1,(G2:G14>6.5)*(G3:G15<=6.5),0))-INDEX(F3:F15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
Must be entered as an array formula using CtrlShiftEnter
Non-array version:
=INDEX(F2:F14,MATCH(1,INDEX((G2:G14>6.5)*(G3:G15<=6.5),0),0))-INDEX(F3:F15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
But @Dominique is right, if there was only one point above 6.5 degrees you could argue that the time spent above 6.5 rather than being zero was at least 10 and possibly 20 minutes, because of the 10 minute spacing of the time points and the fact that the temperature seems to be uniformly increasing then decreasing.
=INDEX(E3:E15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
or
=INDEX(E3:E15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
for the date.
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
add a comment |
You can do it with offset arrays, to find the time where the temperature in the original array (G2:G14) is <=6.5 and the temperature in the shifted array (G3:G15) is >6.5, then subtract it from the time when the temperature in the original array is >6.5 and the temperature in the shifted array is <=6.5.
=INDEX(F2:F14,MATCH(1,(G2:G14>6.5)*(G3:G15<=6.5),0))-INDEX(F3:F15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
Must be entered as an array formula using CtrlShiftEnter
Non-array version:
=INDEX(F2:F14,MATCH(1,INDEX((G2:G14>6.5)*(G3:G15<=6.5),0),0))-INDEX(F3:F15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
But @Dominique is right, if there was only one point above 6.5 degrees you could argue that the time spent above 6.5 rather than being zero was at least 10 and possibly 20 minutes, because of the 10 minute spacing of the time points and the fact that the temperature seems to be uniformly increasing then decreasing.
=INDEX(E3:E15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
or
=INDEX(E3:E15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
for the date.
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
add a comment |
You can do it with offset arrays, to find the time where the temperature in the original array (G2:G14) is <=6.5 and the temperature in the shifted array (G3:G15) is >6.5, then subtract it from the time when the temperature in the original array is >6.5 and the temperature in the shifted array is <=6.5.
=INDEX(F2:F14,MATCH(1,(G2:G14>6.5)*(G3:G15<=6.5),0))-INDEX(F3:F15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
Must be entered as an array formula using CtrlShiftEnter
Non-array version:
=INDEX(F2:F14,MATCH(1,INDEX((G2:G14>6.5)*(G3:G15<=6.5),0),0))-INDEX(F3:F15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
But @Dominique is right, if there was only one point above 6.5 degrees you could argue that the time spent above 6.5 rather than being zero was at least 10 and possibly 20 minutes, because of the 10 minute spacing of the time points and the fact that the temperature seems to be uniformly increasing then decreasing.
=INDEX(E3:E15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
or
=INDEX(E3:E15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
for the date.
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
You can do it with offset arrays, to find the time where the temperature in the original array (G2:G14) is <=6.5 and the temperature in the shifted array (G3:G15) is >6.5, then subtract it from the time when the temperature in the original array is >6.5 and the temperature in the shifted array is <=6.5.
=INDEX(F2:F14,MATCH(1,(G2:G14>6.5)*(G3:G15<=6.5),0))-INDEX(F3:F15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
Must be entered as an array formula using CtrlShiftEnter
Non-array version:
=INDEX(F2:F14,MATCH(1,INDEX((G2:G14>6.5)*(G3:G15<=6.5),0),0))-INDEX(F3:F15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
But @Dominique is right, if there was only one point above 6.5 degrees you could argue that the time spent above 6.5 rather than being zero was at least 10 and possibly 20 minutes, because of the 10 minute spacing of the time points and the fact that the temperature seems to be uniformly increasing then decreasing.
=INDEX(E3:E15,MATCH(1,(G2:G14<=6.5)*(G3:G15>6.5),0))
or
=INDEX(E3:E15,MATCH(1,INDEX((G2:G14<=6.5)*(G3:G15>6.5),0),0))
for the date.
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
edited Nov 29 '18 at 13:12
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
answered Nov 29 '18 at 12:57
Tom SharpeTom Sharpe
13.3k31227
13.3k31227
add a comment |
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.
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%2f53533178%2fcalculate-total-time-of-a-value-being-bigger-than-x%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
1
And what if you have only single occurences of the temperature, going above 6.5?
– Dominique
Nov 29 '18 at 10:47