Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    MIN not MAX array (2K3)

    Hi

    In a range H3:H100 I have a series of numerical figures which are calculated using an If statement. It has an error handler in the if statement to return #N/A as I am charting the figures. In Column I there is a function:

    {=MAX(IF(NOT(ISERROR($H$3:$H$100)),$H$3:$H$100))}

    to calculate the maximum value in the column working fine. However I am having real problems calculating the MIN value as all the results are 0 if I use

    {=MIN(IF(NOT(ISERROR($H$3:$H$100)),$H$3:$H$100))}

    None of the results will be 0 and I am assuming the formula is picking up the #N/A. Any ideas?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MIN not MAX array (2K3)

    The NOT(ISERROR(...)) excludes the #N/A from the calculations, but are there blank cells in H3:H100? They'd count as 0.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MIN not MAX array (2K3)

    AAAAhhh <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    I hadn't extended the formula down fully to H100 only to H98 <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. My formula does work <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Cheers
    Jerry

Posting Permissions

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