01-18-2019 05:48 AM
01-18-2019 05:48 AM
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):
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.
01-18-2019 06:44 AM
You shall use these formulas as array one. Other words, to enter the formula instead of Enter press combination Ctrl+Shift+Enter
01-18-2019 06:48 AM
01-18-2019 06:52 AM
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
01-18-2019 10:17 AM
And one more variant without array formulas. For Start Date, Project B task BI (other words cell I5)
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.