# Thread: Max & Min (2000)

1. ## Max & Min (2000)

On Worksheet A I have listed, by month, various suppliers (A-J) from whom I purchase products. In addition to the "Base" price of the product I pay set out in column B (the suppliers name in column A), in column C I have itemized a "Premium/Discount" I pay as well. What I would like to do on Worksheet B is list the highest and lowest Premium/Discount I pay each supplier, and the month during which I paid it. Please see attached.

2. ## Re: Max & Min (2000)

You can use this array formula in I1 for the maximum:

=MAX(IF('Worksheet A'!\$A\$1:\$A\$100=B2,'Worksheet A'!\$C\$1:\$C\$100))

and similar in K1 for the minimum, using MIN instead of MAX. Fill down as far as needed. Finding the month is less easy because of the layout of worksheet A. Perhaps you need a VBA function.

3. ## Re: Max & Min (2000)

Hans,
How about I insert a column before the current column A on Worksheet A and set out the month/year beside each supplier each month. Would that help?
Thanks.

4. ## Re: Max & Min (2000)

Yes. In J2, enter this array formula:

=INDEX('Worksheet A'!\$A\$1:\$A\$100,MAX(ROW(\$1:\$100)*('Worksheet A'!\$B\$1:\$B\$100=B2)*('Worksheet A'!\$D\$1:\$D\$100=I2)))

and in L2, the array formula

=INDEX('Worksheet A'!\$A\$1:\$A\$100,MAX(ROW(\$1:\$100)*('Worksheet A'!\$B\$1:\$B\$100=B2)*('Worksheet A'!\$D\$1:\$D\$100=K2)))

and fill down as far as needed. Format the result as mmm-yy or something similar. (Remember, since you inserted a new column A, the suppliers are now in column B and the premium/discounts in column D)

If there is more than one month with the min or max, the most recent one will be returned.

5. ## Re: Max & Min (2000)

Thanks again, Hans; works great!

6. ## Re: Max & Min (2000)

If you put Hans' formula in I2 for the max and the modification to it in K2 for the min you can use this formula in J2:

=INDEX('Worksheet A'!\$A\$1:\$A\$100,MIN(IF(('Worksheet A'!\$A\$1:\$A\$100=\$B2)*('Worksheet A'!\$C\$1:\$C\$100=I2),ROW('Worksheet A'!\$C\$1:\$C\$100)))-MIN(IF(MIN(IF(('Worksheet A'!\$A\$1:\$A\$100=\$B2)*('Worksheet A'!\$C\$1:\$C\$100=I2),ROW('Worksheet A'!\$C\$1:\$C\$100)))-IF(ISNUMBER('Worksheet A'!\$A\$1:\$A\$100),ROW('Worksheet A'!\$A\$1:\$A\$100),0)>0,MIN(IF(('Worksheet A'!\$A\$1:\$A\$100=\$B2)*('Worksheet A'!\$C\$1:\$C\$100=I2),ROW('Worksheet A'!\$C\$1:\$C\$100)))-IF(ISNUMBER('Worksheet A'!\$A\$1:\$A\$100),ROW('Worksheet A'!\$A\$1:\$A\$100),0))))

It can be copied from J2 to J3:J11 and then copy J2:J11 to L2:L11. This will work with your original setup.

It keys on finding the row with a date in it that is closest to the min/max row but is not larger than it.

[Though personally I would find it easier to work with the dates in each row... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>]

Steve

7. ## Re: Max & Min (2000)

Thanks, Steve.

#### Posting Permissions

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