Min and Max values for different salespeople?
 

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?

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.


--
Regards,
Dave
<!--


"Red 61" wrote:

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?

On Mon, 19 Sep 2005 07:41:05 -0700, Red 61 <Red
wrote:

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

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" wrote:

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.


--
Regards,
Dave
<!--


"Red 61" wrote:

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?

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

--
Regards,
Dave
<!--


"Red 61" wrote:

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" wrote:

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.


--
Regards,
Dave
<!--


"Red 61" wrote:

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?

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" wrote:

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?

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



"David Billigmeier" wrote:

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

--
Regards,
Dave
<!--


"Red 61" wrote:

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" wrote:

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.


--
Regards,
Dave
<!--


"Red 61" wrote:

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?

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

"Ron Rosenfeld" wrote:

On Mon, 19 Sep 2005 07:41:05 -0700, Red 61 <Red
wrote:

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

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


"Eric" wrote:

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" wrote:

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?

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

Replace@mailinator with @tiscali.co.uk

"Red 61" wrote in message
...
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


"Eric" wrote:

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" wrote:

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?

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.


On Mon, 19 Sep 2005 21:00:16 +0100, "Sandy Mann"
wrote:

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)))

"Myrna Larson" wrote in message
...
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

Replace@mailinator with @tiscali.co.uk
MIN((B1:B6="Bob")*(C1:C6)))

LOL!!

On Mon, 19 Sep 2005 22:45:38 +0100, "Sandy Mann"
wrote:

"Myrna Larson" wrote in message
.. .
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!

That did it, Myrna! Thank you so much! :)

"Myrna Larson" wrote:

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.


On Mon, 19 Sep 2005 21:00:16 +0100, "Sandy Mann"
wrote:

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)))