Forum Discussion

Dale Lori's avatar
Dale Lori
Copper Contributor
Oct 03, 2018

Stopping a MAX function after one result.

I'm using a MAX function with 2 ranges =MAX(IF(ActDName=$A3,ActDDate)). 

 

Which I'm using to bring back the most recent date where a development plan is in place.  In essence, it looks up a person's name, looks at a list of dates (of which there may be multiple entries against that name) and brings back the most recent date in the list  -  This worked fine, however it didn't take in to account if a development plan had since been resolved / satisfied e.g. if the last development plan was in 2016, even if it was resolved in 2016, my formula still brings back that date, making it look like that plan is still in force.

 

To try to cover this off I added another column to my base data and used an if statement to confirm whether the development plan was resolved (Yes / No option) e.g. if column F is anything other than yes, bring back the most recent date, otherwise it remains blank.  I then had the original formula look up dates in this new column, not the original one.  This worked because where the plan has been resolved, it remained blank on my destination sheet. 

 

However, if there are multiple entries i.e. if the development plan has been reviewed a few time but remains active, and I then 'resolve' the most recent date, instead of stopping there my formula instead brings back the most recent date which hasn't been resolved i.e. the last date which hasn't got a yes in the F column.

 

I don't want to have to put a yes in every cell in column F to stop it bringing back a date, because we need to be able to track how long the development plan was in place and how many reviews were completed on it before it was moved to resolved.

 

Is there a way to get my formula to identify the most recent date, bring back a date or blank, depending on whether that date is resolved but then stop, and not revert back to the previous (unresolved) date?

  • Your Excel version doesn't support MAXIFS().

    Use AGGREGATE() instead.

    =IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",AGGREGATE(14,6,ActionDevelopment!$D$2:$D$14/(ActDName=A3),1))

     

    • Dale Lori's avatar
      Dale Lori
      Copper Contributor

      Sample file attached.  As you can see, on the ActionDevelopment tab, I've resolved the most recent plan dates for user 1 and 2 (rows 13 and 14).  On the 2018 Team Overview Sheet, I want it to show this as blank but instead it's bringing back the next latest date, which is not resolved e.g. row 11 and 12

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        I think you didn't explained it correctly.

        Try this:

        =IF(COUNTIFS(ActDName,A3,ActionDevelopment!$F$2:$F$14,"Yes"),"",MAXIFS(ActionDevelopment!$D$2:$D$14,ActDName,A3))

         

Resources