2nd & 3rd Max Date !
 

hello
i am using following formula for max date from my cash book, but how can i
get 2nd & 3rd max date ? my this formula is in sale list.

=IF($C5="","",SUMPRODUCT(MAX((CashBook!$L$11:$L$10 000=$C5)*(CashBook!$I$11:$I$10000))))

01/01/2009
02/01/2009
03/01/2009*
10/01/2009*
15/02/2009* <--- now getting with MAX

2nd & 3rd Max Date !
 

Try this array formula** :

=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),n))

Where n = nth largest value you want. n = 1 is the equivalent of MAX. n = 2
for the 2nd largest, n = 3 for the 3rd largest, etc.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Tufail" wrote in message
...
hello
i am using following formula for max date from my cash book, but how can i
get 2nd & 3rd max date ? my this formula is in sale list.

=IF($C5="","",SUMPRODUCT(MAX((CashBook!$L$11:$L$10 000=$C5)*(CashBook!$I$11:$I$10000))))

01/01/2009
02/01/2009
03/01/2009*
10/01/2009*
15/02/2009* <--- now getting with MAX

2nd & 3rd Max Date !
 

Thank you very much !


"T. Valko" wrote:

Try this array formula** :

=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),n))

Where n = nth largest value you want. n = 1 is the equivalent of MAX. n = 2
for the 2nd largest, n = 3 for the 3rd largest, etc.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Tufail" wrote in message
...
hello
i am using following formula for max date from my cash book, but how can i
get 2nd & 3rd max date ? my this formula is in sale list.

=IF($C5="","",SUMPRODUCT(MAX((CashBook!$L$11:$L$10 000=$C5)*(CashBook!$I$11:$I$10000))))

01/01/2009
02/01/2009
03/01/2009*
10/01/2009*
15/02/2009* <--- now getting with MAX

2nd & 3rd Max Date !
 

dear biff,
error is coming out of $NUM! what should i do now ?
=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),N1))


"T. Valko" wrote:

Try this array formula** :

=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),n))

Where n = nth largest value you want. n = 1 is the equivalent of MAX. n = 2
for the 2nd largest, n = 3 for the 3rd largest, etc.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Tufail" wrote in message
...
hello
i am using following formula for max date from my cash book, but how can i
get 2nd & 3rd max date ? my this formula is in sale list.

=IF($C5="","",SUMPRODUCT(MAX((CashBook!$L$11:$L$10 000=$C5)*(CashBook!$I$11:$I$10000))))

01/01/2009
02/01/2009
03/01/2009*
10/01/2009*
15/02/2009* <--- now getting with MAX

2nd & 3rd Max Date !
 

Is N1 a cell reference?

You'll get an error if there isn't a nth largest number. The nth argument
has to be a number that is not greater than the number of times
CashBook!$L$11:$L$10000=$C5.

Try this (array entered):

=IF($C5="","",IF(COUNTIF(CashBook!$L$11:$L$10000,$ C5)N1,"",LARGE(IF(CashBook!$L$11:$L$10000=$C5,Cas hBook!$I$11:$I$10000),N1)))

--
Biff
Microsoft Excel MVP


"Tufail" wrote in message
...
dear biff,
error is coming out of $NUM! what should i do now ?
=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),N1))


"T. Valko" wrote:

Try this array formula** :

=IF($C5="","",LARGE(IF(CashBook!$L$11:$L$10000=$C5 ,CashBook!$I$11:$I$10000),n))

Where n = nth largest value you want. n = 1 is the equivalent of MAX. n =
2
for the 2nd largest, n = 3 for the 3rd largest, etc.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Tufail" wrote in message
...
hello
i am using following formula for max date from my cash book, but how
can i
get 2nd & 3rd max date ? my this formula is in sale list.

=IF($C5="","",SUMPRODUCT(MAX((CashBook!$L$11:$L$10 000=$C5)*(CashBook!$I$11:$I$10000))))

01/01/2009
02/01/2009
03/01/2009*
10/01/2009*
15/02/2009* <--- now getting with MAX