Forum Discussion

leonardogilp7's avatar
leonardogilp7
Copper Contributor
Jan 18, 2019

MIN and MAX functions dynamic range

Good afternoon,

 

My problem is related to MIN and MAX functions and how to make them as dynamic as possible. 

 

I work as a project manager and am trying to create a doc as automated as possible. Every project could have High Level tasks and sub-tasks (indent to high level tasks). Every sub-task has a start and an end date. High level tasks also have start and end dates and these should be MIN of all sub-tasks for start date and MAX of all sub-tasks for end date.

 

How to automate the MIN and MAX functions in excel so users don't have to manually drag (up or down) the array/list of start or end dates depending on the amount of sub-tasks a high level task has?

 

In attached example you have project A and B both with 1 high level task (AI, AII and BI, BII):

  • High level Task AI (Project A) has 3 sub-tasks: 1, 2, 3. High Level task AII has two sub tasks: 4, 5
  • High level Task BI (Project B) has 4 sub tasks: 1, 2, 3, 4. High level task BII has two sub tasks: 5, 6.
  • As stated above high level tasks start date and end date will be MIN and MAX of sub-tasks respectively.
  • If I do MAX(1,2,3) for AI and then copy formula for BI, it will only max 3 tasks and not 4 sub tasks. What function can be used for MIN and MAX functions to automatically stop/continue counting when no more sub-tasks for that high level task?

 I originally made the question to an excel expert who's youtube channel I follow. The answer was to use the following functions for MIN and MAX respectively: =MIN(I6:INDEX(I6:I24,MATCH(TRUE,H6:H24="",0))) and =MAX(J6:INDEX(J6:J24,MATCH(TRUE,H6:H24="",0))) to make sure  ranges were dynamic. 

 

I tried the formulas but am obtaining an #VALUE error back and not sure the reason why.

 

I truly appreciate any support you can give in solving the problem.

 

Many thanks for any reply and support you can offer to this request, I highly appreciate it.

 

Kind regards,

Leo.

excel
Formulas and Functions

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 18, 2019

    Hi Leo,

     

    You shall use these formulas as array one. Other words, to enter the formula instead of Enter press combination Ctrl+Shift+Enter

    • leonardogilp7's avatar
      leonardogilp7
      Copper Contributor
      Jan 18, 2019
      Hi Sergei,

      Many thanks for the reply and suggestion. This solved the problem. Is this the only alternative? Thanks in advance.

      Leo
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 18, 2019

        And one more variant without array formulas. For Start Date, Project B task BI (other words cell I5)

        =MIN(OFFSET(I6,0,0,AGGREGATE(15,6,1/(H6:H$20="")*(ROW(H6:H$20)-ROW(H5)),1)-1))

        AGGREGATE calculates position of next blank cell starting from H6 in your entire range H6:H$20. In that case it will be 5. Minus 1 gives number of subtasks = 4. OFFSET returns column with this 4 days starting from cell I6. Apply MIN to it.

        Same for MAX. After that you may copy your cells to find another Start/End dates.  Just be careful with absolute and relative references in your formulas.

        See in attached file.

Resources