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;
}







0















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



enter image description here



Any help would be appreciated, thanks!






excel excel-formula







share|improve this question


















  • 1





    And what if you have only single occurences of the temperature, going above 6.5?

    – Dominique
    Nov 29 '18 at 10:47


















0















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



enter image description here



Any help would be appreciated, thanks!






excel excel-formula







share|improve this question


















  • 1





    And what if you have only single occurences of the temperature, going above 6.5?

    – Dominique
    Nov 29 '18 at 10:47














0












0








0








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



enter image description here



Any help would be appreciated, thanks!






excel excel-formula







share|improve this question














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



enter image description here



Any help would be appreciated, thanks!






excel excel-formula




excel excel-formula






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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














  • 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












2 Answers
2






active

oldest

votes


















0














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
enter image description here



just fiddle with the range as needed






share|improve this answer































    0














    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.



    enter image description here






    share|improve this answer


























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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
      enter image description here



      just fiddle with the range as needed






      share|improve this answer




























        0














        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
        enter image description here



        just fiddle with the range as needed






        share|improve this answer


























          0












          0








          0







          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
          enter image description here



          just fiddle with the range as needed






          share|improve this answer













          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
          enter image description here



          just fiddle with the range as needed







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 29 '18 at 10:38









          MikaelMikael

          403




          403

























              0














              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.



              enter image description here






              share|improve this answer






























                0














                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.



                enter image description here






                share|improve this answer




























                  0












                  0








                  0







                  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.



                  enter image description here






                  share|improve this answer















                  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.



                  enter image description here







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 29 '18 at 13:12

























                  answered Nov 29 '18 at 12:57









                  Tom SharpeTom Sharpe

                  13.3k31227




                  13.3k31227






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      -

                      Popular posts from this blog

                      A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                      Image
                      0 I found a lot of questions abount appendices and ToC. Many users want appendices to be grouped in an Appendix part, however some problems arise with ToC, hyperref, PDF viewer bookmarks, and so on. There are different solutions which require extra packages, command patching and other extra code, however none of them satisfies me. I almost found an easy way to accomplish a good result, where appendices are added to bookmarks in the right way and hyperref links point to the right page. However, the number of the "Appendix" part page is wrong (it's the number of appendix A). Is there any EASY way to fix that? This is a MWE: documentclass{book} usepackage[nottoc,notlot,notlof]{tocbibind} usepackage{hyperref} begin{document} frontmatter tableofcontents mainmatter part{First} chapter{...
                      Read more

                      Calculate evaluation metrics using cross_val_predict sklearn

                      Image
                      1 In the sklearn.model_selection.cross_val_predict page it is stated: Generate cross-validated estimates for each input data point. It is not appropriate to pass these predictions into an evaluation metric. Can someone explain what does it mean? If this gives estimate of Y (y prediction) for every Y (true Y), why can't I calculate metrics such as RMSE or coefficient of determination using these results? python scikit-learn cross-validation share | improve this question edited Nov 28 '18 at 17:52 desertnaut 20.3k 7 43 79 ...
                      Read more

                      Insert data from modal to MySQL (multiple modal on website)

                      Image
                      0 I have problem with inserting data to MySQL from modal. My modal: <a href="#" class="badge badge-pill badge-success">6 komentarzy</a> <a href="#" class="badge badge-pill badge-danger">brak komentarzy</a> <a data-toggle="modal" href="#add_desk_comm_{$desk_['desk_id']}" data-target="#add_desk_comm_{$desk_['desk_id']}" class="ediiit">(dodaj)</a> <div class="modal fade" id="add_desk_comm_{$desk_['desk_id']}" tabindex="-1" role="dialog" aria-labelledby="edit_printer" aria-hidden="true"> <div class="modal-dialog" role="document"> ...
                      Read more