trying to figure out Min and Max in a sheet

[carolruth11]

Hi, I am a new user. I am trying to write a report and trying to figure out
who is the highest salesperson and who is the lowest salesperson within this
report. I believe that using the functions MIN and MAX would be the best way
to do this, but I am unsure! Could someone help me PLEASE? I cannot figure
it out. It is for a homework assignment and it is due by Sunday night.

I really appreciate any and all help!
Carol

 

[Joe User]

I believe that using the functions MIN and MAX would be the best way
to do this, but I am unsure!

Probably correct.

There is a small chance that you might need to use SUBTOTAL, for example if
you use a filter. If you don't know what that is, you probably do not need
to worry about using SUBTOTAL.

If you post some details about the design of the worksheet, we might be able
to provide more specific guidance.

It is for a homework assignment and it is due by Sunday night.

That's 3 days from now. I think you have time to read the Help pages for
MIN and MAX. Just click on Help.


----- original message -----

 

[Luke M]

You are on the right track. Have you tried reading the XL help file for
instruction on how to use the MIN/MAX functions?

Formula setup is like this:
=MAX(A)

This would tell you the highest number in columns A though D.

A lot depends on how your workbook is setup. Guessing at what your layout
is, you might want to do some quick reading (again, in the help file) on
conditional formatting, or perhaps the VLOOKUP function if you are wanting
the name of the person to be the output of your formula.

 

[Lars-Åke Aspelin]

Hi, I am a new user. I am trying to write a report and trying to figure out
who is the highest salesperson and who is the lowest salesperson within this
report. I believe that using the functions MIN and MAX would be the best way
to do this, but I am unsure! Could someone help me PLEASE? I cannot figure
it out. It is for a homework assignment and it is due by Sunday night.

I really appreciate any and all help!
Carol

Here are a few hints on functions that you could do a little
experimenting with.

Assuming you have the names of the sales persons in one column and
their respective (different) sales amounts in a second column

A. Use the MAX() function to find the highest sales amounts.
Don't proceed until you get the correct result.

B. Use the MATCH() function to find the position of that highest sales
amount from step A in the second column. As your second column is
probably not sorted, be aware of the third argument of the MATCH()
function. It should be set to 0 corresponding to an exact match.
Don't proceed until you get the correct result.

C. Finally, use the INDEX() function to get the name from the first
column that corresponds to the position you got from step B.

Once you understand these three step you can combine them all into one
formula.
For the lowest sales amount just use MIN() instead of MAX()

Hope this helps. / Lars-Åke

 

[Dave Peterson]

Say your names are in A2:A99 and your amounts are in B2:B99.

=max(b2:b99)
will show the largest amount in b2:b99

=min(b2:b99)
will show the smallest amount in b2:b99

To get the name of the first matching amount (lowest number row) with the
highest amount:

=index(a2:a99,match(max(b2:b99),b2:b99,0))

Change max to min to get the name of the first matching amount with the lowest
amount:

=index(a2:a99,match(min(b2:b99),b2:b99,0))

But the bad news is that if there are ties for either the min or max, these
formulas will always return the name in the lower numbered row (closest to the
top of the data).


ps.

=large(b2:b99,7)
will get the 7th largest amount.

=small(b2:b99,12)
will get the 12 smallest amount