Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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.
    Thanks in advance.

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

    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Max & Min (2000)

    Thanks again, Hans; works great!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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
  •