2nd & 3rd Max Date !

  • Thread starter Thread starter Tufail
  • Start date Start date
T

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
 
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.
 
Thank you very much !


T. Valko said:
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.
Click to expand...
 
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))
 
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)))
 
Back
Top