Manipulate a Range

  • Thread starter Thread starter DaveM
  • Start date Start date
D

DaveM

I have a formula that looks for the min value in a range say Min(A2:A5) and a
marco that runs each minute expands the range by one row. So after one
minute the new range is A2:A6, then one min later A2:A7, etc.

What I really need is for the min function to look at a range of one row
less than the range in the formula. I know...sounds wierd but trust me.....

So when the range is A2:A5 If really only want the Min value between A2:A4.
Another words each time the macro runs i want the min value in the range NOT
including the last row.

How do i manipulate the fromula for say A2:A5 to consider only cells A2:A4?

Thx
DaveM
 
DaveM,

Where are you inserting the new rows at?

If you have values in A2:A5 and your formula is =Min(A2:A4), if you insert a
new row between rows 2 & 4, the formula will adjust automatically. Can you
insert the new row somewhere between the 1st and 2nd to last rows?

Here is another option...create a dynamic named range. This will work as
long as data is entered at the same time a new row is inserted (or it will
work after data is entered).
1. Insert > Name > Define...
2. Put a meaningful name in "Names in workbook:" (I'll use "MinRange")
(there are restrictions: no spaces, no special characters, can use . or _
inplace of a space if you need to...look up named ranges in Help)
3. In "Refers to:", enter the following (be sure to include sheet name):
=OFFSET(Sheet1!$A$2,0,0,count(Sheet1!$A$2:$A$65536)-1,1)

Then enter this as your formula:
=Min(MinRange)

As long as your formula is not in column A below you data, this should get
you what you want. If your Min formula is in the same column, then try
changing...

count(Sheet1!$A$2:$A$65536)-1

....to...

count(Sheet1!$A$2:$A$65536)-2

Now this will only work correctly if there is some sort of numerical data
(including dates & times) in column A from row 2 to the last row of your
data. If there are any blank cells or text in column A, it will shorten
MinRange by that many cells.

If neither of these 2 options will work for you, then yes, there is code
that can be added to your macro that will adjust the formula. Please write
back.

HTH,

Conan
 
Back
Top