Return the oldest date that matched two criteria











up vote
0
down vote

favorite












I am trying to have a cell return the value of the oldest date given that it matches the Store name (A5 referenced in the formula), as well as checks to see if column one is overdue. The date is stored in Column E on the Open POs sheet. Any advice?



enter image description here



enter image description here



enter image description here










share|improve this question









New contributor




Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
























    up vote
    0
    down vote

    favorite












    I am trying to have a cell return the value of the oldest date given that it matches the Store name (A5 referenced in the formula), as well as checks to see if column one is overdue. The date is stored in Column E on the Open POs sheet. Any advice?



    enter image description here



    enter image description here



    enter image description here










    share|improve this question









    New contributor




    Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.






















      up vote
      0
      down vote

      favorite









      up vote
      0
      down vote

      favorite











      I am trying to have a cell return the value of the oldest date given that it matches the Store name (A5 referenced in the formula), as well as checks to see if column one is overdue. The date is stored in Column E on the Open POs sheet. Any advice?



      enter image description here



      enter image description here



      enter image description here










      share|improve this question









      New contributor




      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      I am trying to have a cell return the value of the oldest date given that it matches the Store name (A5 referenced in the formula), as well as checks to see if column one is overdue. The date is stored in Column E on the Open POs sheet. Any advice?



      enter image description here



      enter image description here



      enter image description here







      microsoft-excel worksheet-function






      share|improve this question









      New contributor




      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.











      share|improve this question









      New contributor




      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      share|improve this question




      share|improve this question








      edited Nov 17 at 1:54









      Ron Rosenfeld

      1,9002610




      1,9002610






      New contributor




      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.









      asked Nov 17 at 0:39









      Preston

      1




      1




      New contributor




      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.





      New contributor





      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






      Preston is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.






















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          1
          down vote













          AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.



          Lets look at the AGGREGATE function and what its parts are



          AGGREGATE(function #, What to include #, Calculation, parameter)



          • Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction

          • What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.

          • Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.

          • Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.


          SO for your formula we would do something like:



          =AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)


          IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.



          Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.






          share|improve this answer























          • Good tutorial answer (+1)
            – Gary's Student
            Nov 18 at 14:11











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


          }
          });






          Preston is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376135%2freturn-the-oldest-date-that-matched-two-criteria%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








          up vote
          1
          down vote













          AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.



          Lets look at the AGGREGATE function and what its parts are



          AGGREGATE(function #, What to include #, Calculation, parameter)



          • Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction

          • What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.

          • Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.

          • Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.


          SO for your formula we would do something like:



          =AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)


          IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.



          Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.






          share|improve this answer























          • Good tutorial answer (+1)
            – Gary's Student
            Nov 18 at 14:11















          up vote
          1
          down vote













          AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.



          Lets look at the AGGREGATE function and what its parts are



          AGGREGATE(function #, What to include #, Calculation, parameter)



          • Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction

          • What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.

          • Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.

          • Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.


          SO for your formula we would do something like:



          =AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)


          IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.



          Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.






          share|improve this answer























          • Good tutorial answer (+1)
            – Gary's Student
            Nov 18 at 14:11













          up vote
          1
          down vote










          up vote
          1
          down vote









          AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.



          Lets look at the AGGREGATE function and what its parts are



          AGGREGATE(function #, What to include #, Calculation, parameter)



          • Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction

          • What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.

          • Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.

          • Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.


          SO for your formula we would do something like:



          =AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)


          IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.



          Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.






          share|improve this answer














          AGGREGATE might be a good option. It can be a formula that will perform array like calculations without being an actual array. The reason I say might, is that only certain functions of AGGREGATE perform the array calculation...14 and 15 are two of them. And the other reason I say might be a good option, is that when performing array calculation, you want to avoid using full column references like F:F within the array. Doing so may cause a lot of excess calculations to be made and can bog down your system. Array calculation should reasonably be limited to the data you are dealing with.



          Lets look at the AGGREGATE function and what its parts are



          AGGREGATE(function #, What to include #, Calculation, parameter)



          • Function 14 and 15 will sort the results from largest to smallest while the other will sort the other direction

          • What to include #, has multiple options for ignoring hidden rows, or results that give errors. I tend to use 6 which ignores errors but includes the rest. You may need to adjust to suit your needs.

          • Calculation is the area you want to put you the information your a looking for and your condition checks. Your condition checks are really boolean functions that return TRUE or FALSE. If we send those through a math operation, excel will convert TRUE=1 and FALSE=0. So if aggregate is set to ignore error, and you are only interested in information that meets your condition, divide your information by your conditions. False conditions will generate a div by 0 error and the aggregate function will then ignore that corresponding information.

          • Parameter is an optional value for some functions but for 14 and 15 it tells AGGREGATE which position in the sorted results of values that you are interested in. placing a 1 here will return either the smallest or largest number depending on if you are using function 14 or 15.


          SO for your formula we would do something like:



          =AGGREGATE(14,6,'Open POs'!E2:E100/(('Open POs'!A2:A100="Overdue")*('Open POs'!F2:F100=$A5),1)


          IF that gives you the earliest date then change 14 to 15. Adjust your ranges to suit your data instead of using full column references.



          Caveat: Your dates need to be stored as excel date time serial and not as strings. You can test this by ISNUMBER(F4) where F4 is one of your date cells. If you get a result of false your dates will need to be converted.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Nov 18 at 17:50

























          answered Nov 17 at 4:02









          Forward Ed

          413213




          413213












          • Good tutorial answer (+1)
            – Gary's Student
            Nov 18 at 14:11


















          • Good tutorial answer (+1)
            – Gary's Student
            Nov 18 at 14:11
















          Good tutorial answer (+1)
          – Gary's Student
          Nov 18 at 14:11




          Good tutorial answer (+1)
          – Gary's Student
          Nov 18 at 14:11










          Preston is a new contributor. Be nice, and check out our Code of Conduct.










           

          draft saved


          draft discarded


















          Preston is a new contributor. Be nice, and check out our Code of Conduct.













          Preston is a new contributor. Be nice, and check out our Code of Conduct.












          Preston is a new contributor. Be nice, and check out our Code of Conduct.















           


          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1376135%2freturn-the-oldest-date-that-matched-two-criteria%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

          Probability when a professor distributes a quiz and homework assignment to a class of n students.

          Aardman Animations

          Are they similar matrix