• 541K Members
• 5,809 Online
• 644K Conversations

Highlighted
New Contributor

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

5 Replies
Highlighted

# Re: MIN and MAX functions dynamic range

Hi Leo,

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

Highlighted

# Re: MIN and MAX functions dynamic range

Hi Sergei,

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

Leo
Highlighted

# Re: MIN and MAX functions dynamic range

If you are Office 365 subscriber or on Office 2019 you may use MAXIFS and MINIFS https://support.office.com/en-gb/article/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883

Highlighted

# Re: MIN and MAX functions dynamic range

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.

Highlighted

# Re: MIN and MAX functions dynamic range

Hi Sergei,

Many thanks for the option, I highly appreciate it. Will try both ways.

Rgds,
Leo
Related Conversations