Min and Max values for different salespeople?

[Red 61]

Hi there,

I am trying to find the minimum and maximum sales for each salesperson. I
can not sort by salesperson -- I must sort by sales date. How do I do this?

1/2/1999 Bob $7
1/5/1999 Rick $20
1/7/1999 Sue $75
1/10/1999 Bob $5
1/15/1999 Sue $3
1/27/1999 Rick $53

So, I need to know Bob's minimum sale was $5, and his maximum sale was $7.

I know how to use the MIN and MAX functions, but how do I tie in the sales
person name?

 

[David Billigmeier]

Assume your salesperson names are in column B and their corresponding sale
amount is in column C:

=MAX(IF(B1:B10="Bob",C1:C10,""))
=MIN(IF(B1:B10="Bob",C1:C10,""))

These are array formulas so confirm with CTRL+SHIFT+ENTER. Also change the
row/column numbers to fit your data.

 

[Ron Rosenfeld]

Hi there,

I am trying to find the minimum and maximum sales for each salesperson. I
can not sort by salesperson -- I must sort by sales date. How do I do this?

1/2/1999 Bob $7
1/5/1999 Rick $20
1/7/1999 Sue $75
1/10/1999 Bob $5
1/15/1999 Sue $3
1/27/1999 Rick $53

So, I need to know Bob's minimum sale was $5, and his maximum sale was $7.

I know how to use the MIN and MAX functions, but how do I tie in the sales
person name?

One solution would be to use a pivot table:

Name your columns, for example, Date Name Sales

Select some cell in the table, then:

Data/Pivot Table/Next/Next/Finish

Drag Names to Columns
Drag Sales to Data twice

RightClick on Sum of Sales
Field Settings -- Max
RightClick on Sum of Sales2
Field Settings -- Min

(you can change the name of the Min and Max fields as you wish).

There are a number of formatting options to present this table.

You could also drag the dates to the Rows area; then group by weeks or months
to get the Max/Min values for each sales person for some period of time.


--ron

 

[Red 61]

Thanks David -- however, I'm getting an error in the formula which says, "A
value used in the formula is of the wrong data type." The error is in the
B1:B10 section of the formula, below.

 

[David Billigmeier]

Are you entering the formula with CTRL+SHIFT+ENTER (NOT by just pressing
ENTER)?

 

[Eric]

Red,

You could also use:

=SUMPRODUCT( MAX( ((B1:B6)="Bob") * (C1:C6)))

Adjust the ranges, the name, and use either MIN or MAX as necessary. This
doesn't require the CTRL+SHIFT+ENTER to enter.

Eric

 

[Red 61]

After entering the formula with CTRL+SHIFT+ENTER, I get "#N/A" in the cell.

 

[Red 61]

Ugh -- Pivot tables... That sounds much more difficult than the original
question I had!

 

[Red 61]

Eric -- this worked GREAT for the MAX values -- but MIN always returns zero.

If we can't figure out a way to do MIN, that's okay -- I'm happy with MAX.

thanks,
Guy

 

[Sandy Mann]

Eric's formula works for me with MIN in it. There is however a surplus set
of brackets:

=SUMPRODUCT(MIN((B1:B6="Bob")*(C1:C6)))

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Myrna Larson]

It doesn't work for me.

With this formula the first part, B1:B6="Bob", returns TRUE or FALSE, but when
you use TRUE/FALSE in multiplication, FALSE is translated to 0 and TRUE to 1.
Therefore if C1:C6 contains only positive numbers, the minimum will always be
0.

This ARRAY formula (entered with CTRL+SHIFT+ENTER) WILL work:

=MIN(IF(B1:B6="Bob",C1:C6))

The reason is, you are not multiplying C1:C6 by TRUE or FALSE (1 or 0), but
rather taking the minimum of a list that contains either a value from C1:C6 or
the Boolean value FALSE. MIN ignores TRUE/FALSE. Multiplication doesn't.

 

[Sandy Mann]

It doesn't work for me.

It doesn't work for me either when I have a column of mixed names instead of
lazily copying "Bob" down the column!

Oops!

--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk
MIN((B1:B6="Bob")*(C1:C6)))

 

[Myrna Larson]

LOL!!

It doesn't work for me either when I have a column of mixed names instead of
lazily copying "Bob" down the column!

Oops!

 

[Red 61]

That did it, Myrna! Thank you so much!

It doesn't work for me.

With this formula the first part, B1:B6="Bob", returns TRUE or FALSE, but when
you use TRUE/FALSE in multiplication, FALSE is translated to 0 and TRUE to 1.
Therefore if C1:C6 contains only positive numbers, the minimum will always be
0.

This ARRAY formula (entered with CTRL+SHIFT+ENTER) WILL work:

=MIN(IF(B1:B6="Bob",C1:C6))

The reason is, you are not multiplying C1:C6 by TRUE or FALSE (1 or 0), but
rather taking the minimum of a list that contains either a value from C1:C6 or
the Boolean value FALSE. MIN ignores TRUE/FALSE. Multiplication doesn't.