Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Min Formula range (2002)

    Hi,
    =MIN(F4:F18,I4:I18,L4:L18) O,R,U .....etc....etc.....
    I need this to repeat every third column, there must be an easier way?

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

    Re: Min Formula range (2002)

    You can use the following array formula (confirm with Ctrl+Shift+Enter):

    =MIN(IF(ISNUMBER(F4:IV18)*(MOD(COLUMN(F4:IV18),3)= 0),F4:IV18))

    IV is the last column in Excel 2003 and before.
    ISNUMBER(F4:IV18) checks whether the cell contains a valid number.
    MOD(COLUMN(F4:IV18),3)=0 checks whether the column number is divisible by 3.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min Formula range (2002)

    Sorry Hans, i just get a zero result. Please see attached.
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min Formula range (2002)

    Sorry, please ignore, I missed the array bit!! sorted now. Thanks

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min Formula range (2002)

    Sorry, me again.... I have tweaked the formula for other scenario's but my logic seems to be out of sync with excel. Where am i going wrong?
    Attached Files Attached Files

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

    Re: Min Formula range (2002)

    The column numbers for columns F, I, L etc. are 6, 9, 12, ...
    They are divisible by 3, i.e. the remainder after division by 3 is 0.

    The column numbers for columns E, H, K etc. are 5, 8, 11, ...
    Their remainder after division by 3 is 2. So you need to use

    =MIN(IF(ISNUMBER(E4:IV18)*(MOD(COLUMN(E4:IV18),3)= 2),E4:IV18))

    and

    =MAX(IF(ISNUMBER(E4:IV18)*(MOD(COLUMN(E4:IV18),3)= 2),E4:IV18))

    Again, these are array formulas i.e. confirm with Ctrl+Shift+Enter.

    If you had been looking at columns G, J, M etc., you'd have used MOD(...,3)=1.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Min Formula range (2002)

    Works perfect, and i <think> I understand. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

Posting Permissions

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