Forum Discussion
nth Largest value
Hi Experts, Could you please help me with function for nth Largest value in 'Excel' ? e.g. 2nd largest value is 5 in below mentioned range. Range 2 5 8 8 Regards, Rajiv
Hello Rajiv
You are looking for LARGE().
=LARGE(Range,n)
if I put =Large ( Range,2) this will give ans '8' .
8 appears twice in range whereas 2nd largest value is '5'. Thanks
8 appears twice in range whereas 2nd largest value is '5'. Thanks
OK, I get your point.
Use a helper column with this formula:
=COUNTIFS(A$1:A1,A1)=1
The final result will be:
=AGGREGATE(14,6,1/(B1:B4/A1:A4),2)
Perhaps without helper column that could be
=AGGREGATE(14,6,1/(ROW($A$1:$A$4)=MATCH($A$1:$A$4,$A$1:$A$4,0))*$A$1:$A$4,2)
pls see attached
HTH
- I guess that wont work .
If we change , lets say.. cell 'A3' to 7.5, it should give ans '7.5' but it still shows '5'
