Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using the MIN command (excel 2000)

    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?

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using the MIN command (excel 2000)

    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
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using the MIN command (excel 2000)

    Nice Ideal!!! Works like a charm!

    Thanks

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using the MIN command (excel 2000)

    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:$[img]/forums/images/smilies/cool.gif[/img],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

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using the MIN command (excel 2000)

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •