Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Who has MAX/MIN sale (Excel 2003)

    Hi,

    Please see the attached sample file for the query. It involves collecting the name of the person who has the highest and lowest sale by use of a formula, (if this is possible?)

    Tx
    Regards,
    Rudi

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

    Re: Who has MAX/MIN sale (Excel 2003)

    Do you want this for each month separately, or aggregated over the entire period?

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who has MAX/MIN sale (Excel 2003)

    An aggregate over the entire period.
    Tx
    Regards,
    Rudi

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

    Re: Who has MAX/MIN sale (Excel 2003)

    Should the 0 values be included in the MIN value?

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who has MAX/MIN sale (Excel 2003)

    Sorry, No. They could be blank cells too. I was just illustrating that there was no data for those months yet.
    Regards,
    Rudi

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

    Re: Who has MAX/MIN sale (Excel 2003)

    See the attached workbook. It uses some intermediate formulas that can be hidden if you prefer.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who has MAX/MIN sale (Excel 2003)

    Awesome! Your solution is very usable Hans. TX

    Question:
    I see you used 1 billion (1000 000 000) as a lookup value in Match. Is this just to be on the safe side to ensure a match? I doubt my sales values will be that high! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    PS: I know I may be pushing it, but before I try it, do you think it is possible to nest the whole lot into one function so there is no need for the extra hidden columns?
    Regards,
    Rudi

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

    Re: Who has MAX/MIN sale (Excel 2003)

    The 1000000000 is an arbitrary value higher than the highest possible value.

    There is probably a very clever trick to avoid the intermediate formulas, but it'd take too much time to investigate. Perhaps someone else will come up with something.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who has MAX/MIN sale (Excel 2003)

    Shall we see if Steve will come up with something??

    No pressure Steve, I just thought of you and the apparent pleasure you get from solving problems with nested functions... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Tx Hans
    Regards,
    Rudi

  10. #10
    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: Who has MAX/MIN sale (Excel 2003)

    How about these array formulas (confirm with ctrl-shift -enter):
    Max is:
    =OFFSET(A1,MIN(IF($B$2:$G$7=MAX($B$2:$G$7),ROW($B$ 2:$G$7)))-1,0)

    Min is:
    =OFFSET(A1,MIN(IF($B$2:$G$7=MIN(IF(B2:G7>0,B2:G7)) ,ROW($B$2:$G$7)))-1,0)

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who has MAX/MIN sale (Excel 2003)

    Grand...Awesome!

    Thanks Hans and Steve....
    BIG Cheers
    Regards,
    Rudi

Posting Permissions

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