# Thread: Who has MAX/MIN sale (Excel 2003)

1. ## 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

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

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

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

An aggregate over the entire period.
Tx

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

Should the 0 values be included in the MIN value?

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

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

8. ## 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. ## 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

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

Grand...Awesome!

Thanks Hans and Steve....
BIG Cheers

#### Posting Permissions

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