Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

Yes, just put in links to the relevant cells instead of the fixed values.

What did you try that didn't work? What result did you get? What did you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed values.

What did you try that didn't work? What result did you get? What did you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed values.

What did you try that didn't work? What result did you get? What did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

I'm also creating a list according to the reps sales in order. I've changed
the max function to the large function to show the 2nd, 3rd, 4th largest
numbers in the list. But some of them are duplicates (ie. sales of 10 show up
more than once). The output is showing one of the dealers multiple times
because this is the first one in the list with the designated sales value. Is
there any way to change the function so that it will list out different sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed values.

What did you try that didn't work? What result did you get? What did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th largest
numbers in the list. But some of them are duplicates (ie. sales of 10 show
up
more than once). The output is showing one of the dealers multiple times
because this is the first one in the list with the designated sales value.
Is
there any way to change the function so that it will list out different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed
values.

What did you try that didn't work? What result did you get? What did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

I would like to find the largest, 2nd largest and 3rd largest sales during
that month and return the dealer that it occured at. The formula will work if
all of the dealers are different, but the 2nd and 3rd largest sales are both
10, then I can't get it to state both of the individual dealers names that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th largest
numbers in the list. But some of them are duplicates (ie. sales of 10 show
up
more than once). The output is showing one of the dealers multiple times
because this is the first one in the list with the designated sales value.
Is
there any way to change the function so that it will list out different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me with.

Using the same data set below, if I wanted to find the dealer that was
associated with the max found in the expression below, how would I do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed
values.

What did you try that didn't work? What result did you get? What did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of sales.

Does anyone know a formula that might work for this?

Ok, so you want the dealers with the n largest sales based on the *date
only* ?

G2 = 10/1/2007

Array entered** :

=INDEX(C$2:C$7,MATCH(LARGE(IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I would like to find the largest, 2nd largest and 3rd largest sales during
that month and return the dealer that it occured at. The formula will work
if
all of the dealers are different, but the 2nd and 3rd largest sales are
both
10, then I can't get it to state both of the individual dealers names that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th
largest
numbers in the list. But some of them are duplicates (ie. sales of 10
show
up
more than once). The output is showing one of the dealers multiple
times
because this is the first one in the list with the designated sales
value.
Is
there any way to change the function so that it will list out different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and
vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me
with.

Using the same data set below, if I wanted to find the dealer that
was
associated with the max found in the expression below, how would I
do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed
values.

What did you try that didn't work? What result did you get? What
did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link
it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of
sales.

Does anyone know a formula that might work for this?

Ok...I am still unable to get distinct values back from the formula for
values that are similar. I am now getting #NUM for an answer. Here is the
exact formula that I have (the below is a similar example to simplify)

=INDEX(DATA!$G$2:$G$20000,MATCH(SMALL(IF((DATA!$N$ 2:$N$20000=$E$2)*(DATA!$L$2:$L$20000=$L$2),DATA!$B $2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10,ROWS(A$1:A1)),1),IF((DATA!$N$2:$N$20000=$E$2 )*(DATA!$L$2:$L$20000=$L$2),DATA!$B$2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10)),0)

Basically column G is the dealer name
Column N is the rep name with E2 being the name to find
Column L is the date with L2 being the lookup date
Column B is the sales numbers

I would like to make a list of the bottom 10 sales dealers based on the
their numbers. The formula works for anything that doesn't get repeated. But
if the name, date and sales number is the same for 2 dealers then it gives an
error. I am not sure what I am doing wrong with the formula...




"T. Valko" wrote:

Ok, so you want the dealers with the n largest sales based on the *date
only* ?

G2 = 10/1/2007

Array entered** :

=INDEX(C$2:C$7,MATCH(LARGE(IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I would like to find the largest, 2nd largest and 3rd largest sales during
that month and return the dealer that it occured at. The formula will work
if
all of the dealers are different, but the 2nd and 3rd largest sales are
both
10, then I can't get it to state both of the individual dealers names that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th
largest
numbers in the list. But some of them are duplicates (ie. sales of 10
show
up
more than once). The output is showing one of the dealers multiple
times
because this is the first one in the list with the designated sales
value.
Is
there any way to change the function so that it will list out different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and
vice
versa. Your other dates are in mmm-yy format but what DAY are they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me
with.

Using the same data set below, if I wanted to find the dealer that
was
associated with the max found in the expression below, how would I
do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the fixed
values.

What did you try that didn't work? What result did you get? What
did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and link
it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of
sales.

Does anyone know a formula that might work for this?

Here's a small sample file that demonstrates this:

xtop-bottom.xls 14 kb

http://cjoint.com/?jcumIxCwpj

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Ok...I am still unable to get distinct values back from the formula for
values that are similar. I am now getting #NUM for an answer. Here is the
exact formula that I have (the below is a similar example to simplify)

=INDEX(DATA!$G$2:$G$20000,MATCH(SMALL(IF((DATA!$N$ 2:$N$20000=$E$2)*(DATA!$L$2:$L$20000=$L$2),DATA!$B $2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10,ROWS(A$1:A1)),1),IF((DATA!$N$2:$N$20000=$E$2 )*(DATA!$L$2:$L$20000=$L$2),DATA!$B$2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10)),0)

Basically column G is the dealer name
Column N is the rep name with E2 being the name to find
Column L is the date with L2 being the lookup date
Column B is the sales numbers

I would like to make a list of the bottom 10 sales dealers based on the
their numbers. The formula works for anything that doesn't get repeated.
But
if the name, date and sales number is the same for 2 dealers then it gives
an
error. I am not sure what I am doing wrong with the formula...




"T. Valko" wrote:

Ok, so you want the dealers with the n largest sales based on the *date
only* ?

G2 = 10/1/2007

Array entered** :

=INDEX(C$2:C$7,MATCH(LARGE(IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I would like to find the largest, 2nd largest and 3rd largest sales
during
that month and return the dealer that it occured at. The formula will
work
if
all of the dealers are different, but the 2nd and 3rd largest sales are
both
10, then I can't get it to state both of the individual dealers names
that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th
largest
numbers in the list. But some of them are duplicates (ie. sales of
10
show
up
more than once). The output is showing one of the dealers multiple
times
because this is the first one in the list with the designated sales
value.
Is
there any way to change the function so that it will list out
different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and
vice
versa. Your other dates are in mmm-yy format but what DAY are
they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me
with.

Using the same data set below, if I wanted to find the dealer
that
was
associated with the max found in the expression below, how would
I
do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the
fixed
values.

What did you try that didn't work? What result did you get?
What
did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and
link
it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in
message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of
sales.

Does anyone know a formula that might work for this?

Brilliant....thanks so much for your help!!! Works perfectly now...

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xtop-bottom.xls 14 kb

http://cjoint.com/?jcumIxCwpj

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Ok...I am still unable to get distinct values back from the formula for
values that are similar. I am now getting #NUM for an answer. Here is the
exact formula that I have (the below is a similar example to simplify)

=INDEX(DATA!$G$2:$G$20000,MATCH(SMALL(IF((DATA!$N$ 2:$N$20000=$E$2)*(DATA!$L$2:$L$20000=$L$2),DATA!$B $2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10,ROWS(A$1:A1)),1),IF((DATA!$N$2:$N$20000=$E$2 )*(DATA!$L$2:$L$20000=$L$2),DATA!$B$2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10)),0)

Basically column G is the dealer name
Column N is the rep name with E2 being the name to find
Column L is the date with L2 being the lookup date
Column B is the sales numbers

I would like to make a list of the bottom 10 sales dealers based on the
their numbers. The formula works for anything that doesn't get repeated.
But
if the name, date and sales number is the same for 2 dealers then it gives
an
error. I am not sure what I am doing wrong with the formula...




"T. Valko" wrote:

Ok, so you want the dealers with the n largest sales based on the *date
only* ?

G2 = 10/1/2007

Array entered** :

=INDEX(C$2:C$7,MATCH(LARGE(IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I would like to find the largest, 2nd largest and 3rd largest sales
during
that month and return the dealer that it occured at. The formula will
work
if
all of the dealers are different, but the 2nd and 3rd largest sales are
both
10, then I can't get it to state both of the individual dealers names
that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct 07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order. I've
changed
the max function to the large function to show the 2nd, 3rd, 4th
largest
numbers in the list. But some of them are duplicates (ie. sales of
10
show
up
more than once). The output is showing one of the dealers multiple
times
because this is the first one in the list with the designated sales
value.
Is
there any way to change the function so that it will list out
different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1 and
vice
versa. Your other dates are in mmm-yy format but what DAY are
they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help me
with.

Using the same data set below, if I wanted to find the dealer
that
was
associated with the max found in the expression below, how would
I
do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the
fixed
values.

What did you try that didn't work? What result did you get?
What
did
you
expect?
--
David Biddulph

"Tuppie11" wrote in message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic and
link
it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in
message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of
sales.

Does anyone know a formula that might work for this?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Brilliant....thanks so much for your help!!! Works perfectly now...

"T. Valko" wrote:

Here's a small sample file that demonstrates this:

xtop-bottom.xls 14 kb

http://cjoint.com/?jcumIxCwpj

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
Ok...I am still unable to get distinct values back from the formula for
values that are similar. I am now getting #NUM for an answer. Here is
the
exact formula that I have (the below is a similar example to simplify)

=INDEX(DATA!$G$2:$G$20000,MATCH(SMALL(IF((DATA!$N$ 2:$N$20000=$E$2)*(DATA!$L$2:$L$20000=$L$2),DATA!$B $2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10,ROWS(A$1:A1)),1),IF((DATA!$N$2:$N$20000=$E$2 )*(DATA!$L$2:$L$20000=$L$2),DATA!$B$2:$B$20000-ROW(DATA!$B$2:$B$20000)/10^10)),0)

Basically column G is the dealer name
Column N is the rep name with E2 being the name to find
Column L is the date with L2 being the lookup date
Column B is the sales numbers

I would like to make a list of the bottom 10 sales dealers based on the
their numbers. The formula works for anything that doesn't get
repeated.
But
if the name, date and sales number is the same for 2 dealers then it
gives
an
error. I am not sure what I am doing wrong with the formula...




"T. Valko" wrote:

Ok, so you want the dealers with the n largest sales based on the
*date
only* ?

G2 = 10/1/2007

Array entered** :

=INDEX(C$2:C$7,MATCH(LARGE(IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2:D$7-ROW(D$2:D$7)/10^10),0))

Copy down as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I would like to find the largest, 2nd largest and 3rd largest sales
during
that month and return the dealer that it occured at. The formula
will
work
if
all of the dealers are different, but the 2nd and 3rd largest sales
are
both
10, then I can't get it to state both of the individual dealers
names
that
they occur at.

Here is the current formula:
=INDEX(C2:C7,MATCH(LARGE(IF((A2:A7=F2)*(B2:B7=G2), D2:D7),2),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))

"T. Valko" wrote:

I've changed the max function to the large function to
show the 2nd, 3rd, 4th largest numbers in the list.

The 2nd, 3rd, 4th largest numbers for what? For Tom's sales in Oct
07?

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I'm also creating a list according to the reps sales in order.
I've
changed
the max function to the large function to show the 2nd, 3rd, 4th
largest
numbers in the list. But some of them are duplicates (ie. sales
of
10
show
up
more than once). The output is showing one of the dealers
multiple
times
because this is the first one in the list with the designated
sales
value.
Is
there any way to change the function so that it will list out
different
sales
reps with the same sales value?

Thanks.

"T. Valko" wrote:

I "hate" ambiguous dates. <g

sept-07
sept -07
oct-07

Assume your data is in the range A2:D7

To find the dealer associated with Tom's max sales in Oct 2007:

F2 = Tom
G2 = 10/1/2007 (your other dates must match the same date as G1
and
vice
versa. Your other dates are in mmm-yy format but what DAY are
they?)

Array entered:

=INDEX(C2:C7,MATCH(MAX(IF((A2:A7=F2)*(B2:B7=G2),D2 :D7)),IF((A2:A7=F2)*(B2:B7=G2),D2:D7),0))


--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in message
...
I was actually able to get it to work...thanks.

But I do have another question that hopefully you could help
me
with.

Using the same data set below, if I wanted to find the dealer
that
was
associated with the max found in the expression below, how
would
I
do
that?



"David Biddulph" wrote:

Yes, just put in links to the relevant cells instead of the
fixed
values.

What did you try that didn't work? What result did you get?
What
did
you
expect?
--
David Biddulph

"Tuppie11" wrote in
message
...
This is great...thanks!
Is there also any way to make "Tom" and the Date dynamic
and
link
it
to
another cell so it will change?

It doesn't seem to work when I direct it to anywhere else.

Thanks.

"T. Valko" wrote:

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B 7)=2007),D2:D7))

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tuppie11" wrote in
message
...
Here is a subset of a group of data that I have:

Name Date Dealer Sales
Andrea sept-07 A 10
Andrea sept -07 B 14
Tom oct-07 C 7
Claire sept-7 F 20
Andrea oct-07 D 15
Tom oct-07 G 9

Essentially, i need a formula that will say:
If the name = "Tom" and Date = "Oct-07", find the max of
sales.

Does anyone know a formula that might work for this?