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 [email protected]>
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 [email protected]>
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk

"Red 61" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
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" <[email protected]> wrote in message
news:[email protected]...
> 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
[email protected]
Replace@mailinator with @tiscali.co.uk
MIN((B1:B6="Bob")*(C1:C6)))

LOL!!

On Mon, 19 Sep 2005 22:45:38 +0100, "Sandy Mann" <[email protected]>
wrote:

>"Myrna Larson" <[email protected]> wrote in message
>news:[email protected]...
>> 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" <[email protected]>
> 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)))
>

Originally Posted by 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?

=MINIFS(G4:G33(this is where I have my commissioned sales figures), A4:A33 (where my sales agents' names are located), I4)
=MINIFS(G4:G33, A4:A33,I4)
=MAXIFS(G4:G33, A4:A33,I4)