Forum Discussion
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
Many thanks for the reply and suggestion. This solved the problem. Is this the only alternative? Thanks in advance.
Leo
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.
- Hi Sergei,
Many thanks for the option, I highly appreciate it. Will try both ways.
Rgds,
Leo
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