Forum Discussion
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...
Hi Sergei,
Many thanks for the reply and suggestion. This solved the problem. Is this the only alternative? Thanks in advance.
Leo
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