Jan 18 2019 05:48 AM
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):
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.
Jan 18 2019 06:44 AM
Hi Leo,
You shall use these formulas as array one. Other words, to enter the formula instead of Enter press combination Ctrl+Shift+Enter
Jan 18 2019 06:48 AM
Jan 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
Jan 18 2019 10:17 AM
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.
Jan 18 2019 10:43 AM