using the MIN command (excel 2000)

Topic

New Reply

Is there a way to make the MIN command cover a growing list rather than having to retype a new range everytime a number is added. I am using the MIN (minimum) command to display the minimum number from a set of numbers in a column. I also have a macro set up that allows one to enter a new number at the beginning of the set of numbers by shifting the whole spreadsheet down one row. But after the macro runs and a new number is entered at the top the MIN command does not pick it up. How can I get around this?

Reply | Quote

Replies

frogmand,

Can you name the range of cells, including the header? Then use the range name in the MIN formula. As rows get inserted between the header and the first data cell, the named range grows automatically.

Chuck

Reply | Quote

Nice Ideal!!! Works like a charm!

Thanks

Reply | Quote

you can create a dynamic range
supposing that the data are in column B
you creates the range:
Insert | Name | Define
Names in wrokbook : Data <= example
then, you write the next formula:
=OFFSET(Sheet1!$B$1,,,MATCH(9.99999999999999E+307,Sheet1!$B:$,1)
Press [OK]

Then write in your cell this formula:
=MAX(IF(ISERROR(Data),0,Data))
or
=MIN(IF(ISERROR(Data),0,Data))

I hope this help you

Reply | Quote

I have always used Rodney Powell’s Dynamic Named Ranges.

Also there is a Dynamic Range Wizard available free, Dynamic Range Wizard

Reply | Quote