FORMULA USING LARGE FUNCTION WITH DUPLICATE VALUES

Question

Hello,

I need to pull the top 10 values but only issue is I have duplicates in the column.  Therefore the top 10 values wont pull in correctly. For example 3 and 4 will be the same number. 

Is there a formula I can add to the LARGE function which will ignore the duplicate values and pull the next largest in the column (it must be the top 10)?

Thank you!

Answer 1

Enter below formula as an array formula (Ctrl+Shift+Enter) in cell D2 and copy down till D11 - this returns the Largest 10 values from column B:

=IFERROR(LARGE(IF(FREQUENCY(IF(B$2:B$20<>"",MATCH(B$2:B$20,B$2:B$20,0)),ROW(B$2:B$20)-ROW(B$2)+1), B$2:B$20), ROW(B1)),"")

Enter below formula as an array formula (Ctrl+Shift+Enter) in cell E2 and copy down till E11 - this returns the Largest 10 values from column B where column A = 45454:

=IFERROR(LARGE(IF(FREQUENCY(IF(A$2:A$20=45454,MATCH(B$2:B$20,B$2:B$20,0)),ROW(B$2:B$20)-ROW(B$2)+1),B$2:B$20),ROW(B1)),"")

Regards,

Amit Tandon

www.excelanytime.com

http://twitter.com/AmitTandonExcel

Answer 2

Use =MAX(IF(rng<larget value with duplicates,rng))

See https://exceljet.net/formula/nth-largest-value-with-duplicates

Answer 3

Hi,I sort data in another column firstly. It seems waste a column space in your workbook.

C2=IF(B2=45454,A2,"not with 45454 account")

D2=MAX(C2:C9)

D3=MAX(IF($C$2:$C$9<D2,$C$2:$C$9))