Forum Discussion
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.
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.
- 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.
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.
- Thanks a ton Hans Vogelaar for your kind help. It's fulfilling my requirement now. Thanks again for your quick and kind help.
