Min with a changing range

londolozi

New Member
Joined
Nov 7, 2008
Messages
25
I am trying to use a worksheet command to find the minimum of a range.
However my issue is the range changes (as it is looking at a web query xml sheet). The range only varies a few cells up or down however it is important I check for the min in a set range.
eg. min(A30:A40)
How can I change the range within the min formula.
I tried match, index and creating a cell with text value (using concatenate) which was =min(A34:A44), but no joy.

I didn't want to use VB for this task.
Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Perhaps:

<title>Excel Jeanie HTML</title><table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="height: 18px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td style="text-align: right;">8</td> <td style="text-align: right;">2</td> <td style="text-align: right;">0</td> <td style="text-align: right;">16</td> <td style="text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td> </td> <td> </td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">13</td> <td> </td> <td style="text-align: right;">9</td> <td> </td> <td style="text-align: right;">7</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">17</td> <td style="text-align: right;">9</td> <td style="text-align: right;">5</td> <td> </td> <td style="text-align: right;">15</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">13</td> <td style="text-align: right;">20</td> <td style="text-align: right;">0</td> <td style="text-align: right;">19</td> <td style="text-align: right;">9</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td style="text-align: right;">13</td> <td style="text-align: right;">2</td> <td style="text-align: right;">14</td> <td style="text-align: right;">20</td> <td style="text-align: right;">17</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td style="text-align: right;">9</td> <td style="text-align: right;">20</td> <td style="text-align: right;">15</td> <td style="text-align: right;">16</td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td style="text-align: right;">8</td> <td style="text-align: right;">5</td> <td> </td> <td> </td> <td> </td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td style="text-align: right;">13</td> <td style="text-align: right;">16</td> <td> </td> <td> </td> <td> </td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>A1</td> <td>{=MIN(OFFSET(A1,MIN(IF(A2:A12<>"",ROW(A2:A12)))-1,,MAX(IF(A2:A12<>"",ROW(A2:A12)))-MIN(IF(A2:A12<>"",ROW(A2:A12)))+1,))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</td></tr></tbody></table>
Is this what you want?
 
Upvote 0
I am trying to use a worksheet command to find the minimum of a range.
However my issue is the range changes (as it is looking at a web query xml sheet). The range only varies a few cells up or down however it is important I check for the min in a set range.
eg. min(A30:A40)
How can I change the range within the min formula.
I tried match, index and creating a cell with text value (using concatenate) which was =min(A34:A44), but no joy.

I didn't want to use VB for this task.
Thanks
Perhaps we need more information about how the range is determined. That might demonstrate why something like this wouldn't work for you:

=MIN(A30:A100)
 
Upvote 0
Thought I would post the answer seen I nutted it out. May help someone else.

I used =MIN(OFFSET(E1:E1,MATCH("Win",$E:$E)+2,0,24,1))

where E1:E1 was the column where the range would reside.
MATCH found the row of the header of the range and I added to rows.
0 was number of columns left or right
24 was the fixed range size (ie always 24 rows)
1 was the column width

Enjoy.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top