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






"DaveM" wrote in message
...
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