# Thread: Min Formula range (2002)

1. ## 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. ## 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. ## Re: Min Formula range (2002)

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

4. ## Re: Min Formula range (2002)

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

5. ## 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?

6. ## 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. ## 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
•