SOLVED

# Iterate and print Min and Max numbers in dynamic range

Hi,

I've set of values in the attached excel (i.e. A to E).  Using the data, i prepared column F to differentiate blank or non blank cells.  Now, i wanted to print the min/max numbers from the given data between the 1x1 in column G.  For ex:, When the loop finds the value 1 at G6, next 1is at G10.  So i need the Min&Max value of all the data between B7:E10.  Similarly across the document.  Here the catch is neither the data nor the range is constant.  Reference file attached for more details, kindly help.  Thanks in advance.

4 Replies

# Re: Iterate and print Min and Max numbers in dynamic range

In G2:

=IF(\$E2="","",MIN(INDEX(\$B:\$B,MAX(IF(\$E\$1:\$E1<>"",ROW(\$E\$1:\$E1)))):INDEX(\$D:\$D,MAX(IF(\$E\$1:\$E2<>"",ROW(\$E\$1:\$E2))))))

In H2:

=IF(\$E2="","",MAX(INDEX(\$B:\$B,MAX(IF(\$E\$1:\$E1<>"",ROW(\$E\$1:\$E1)))):INDEX(\$D:\$D,MAX(IF(\$E\$1:\$E2<>"",ROW(\$E\$1:\$E2))))))

Fill down.

# Re: Iterate and print Min and Max numbers in dynamic range

Thank you for your quick response. But the formula considering the data of only B & D columns. The requirement is to consider A, B, C & D columns. Kindly update.
best response confirmed by NV (Copper Contributor)
Solution

# Re: Iterate and print Min and Max numbers in dynamic range

You mentioned "So i need the Min&Max value of all the data between B7:E10". E contains duplicates of columns to the left, so I omitted it. Now you want A to D...

In G2:

=IF(\$E2="","",MIN(INDEX(\$A:\$A,MAX(IF(\$E\$1:\$E1<>"",ROW(\$E\$1:\$E1)))):INDEX(\$D:\$D,MAX(IF(\$E\$1:\$E2<>"",ROW(\$E\$1:\$E2))))))

In H2:

=IF(\$E2="","",MAX(INDEX(\$A:\$A,MAX(IF(\$E\$1:\$E1<>"",ROW(\$E\$1:\$E1)))):INDEX(\$D:\$D,MAX(IF(\$E\$1:\$E2<>"",ROW(\$E\$1:\$E2))))))

These formulas look at columns A, B, C and D, not just A and D.

# Re: Iterate and print Min and Max numbers in dynamic range

Thanks a ton Hans Vogelaar for your kind help. It's fulfilling my requirement now. Thanks again for your quick and kind help.