2nd & 3rd Max Date !

[Tufail]

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$10000=$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

 

[T. Valko]

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.

 

[Tufail]

Thank you very much !

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.

 

[Tufail]

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]

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,CashBook!$I$11:$I$10000),N1)))