Max function

[Tuppie11]

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?

 

[T. Valko]

Try this array formula** :

=MAX(IF((A2:A7="Tom")*(MONTH(B2:B7)=10)*(YEAR(B2:B7)=2007),D27))

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

 

[Tuppie11]

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.

 

[David Biddulph]

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?

 

[Tuppie11]

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?

 

[T. Valko]

I "hate" ambiguous dates. <g>

sept-07
sept -07
oct-07

Assume your data is in the range A27

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),D27)),IF((A2:A7=F2)*(B2:B7=G2),D27),0))

 

[Tuppie11]

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]

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?

 

[Tuppie11]

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),D27),2),IF((A2:A7=F2)*(B2:B7=G2),D27),0))

 

[T. Valko]

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$7-ROW(D$2$7)/10^10),ROWS(A$1:A1)),IF(B$2:B$7=G$2,D$2$7-ROW(D$2$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)

 

[Tuppie11]

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]

Here's a small sample file that demonstrates this:

xtop-bottom.xls 14 kb

http://cjoint.com/?jcumIxCwpj

 

[Tuppie11]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!