SOLVED

Iron Contributor

# FILTER and LARGE function.

Hi,

I try to extract 10 largest values from a Pivot Table.

I write a formula:

=INDEX(SORT(FILTER(A:I,(A:A=K2)*(G:G>=LARGE(G:G,10))),7,-1),SEQUENCE(10),{3,7})

The result shows  just 3 largest values:

Thank you.

5 Replies

# Re: FILTER and LARGE function.

Change > into <.

But you can also use the Top10 filter of the pivot table.

best response confirmed by littlevillage (Iron Contributor)
Solution

# Re: FILTER and LARGE function.

@littlevillage Use TAKE() function.

``=TAKE(CHOOSECOLS(SORT(FILTER(A:I,A:A=K2),7,-1),3,7),10)``

# Re: FILTER and LARGE function.

My office version: 2021, it has no TAKE and CHOOSECOLS functions.

I don't know exactly why FILTER and LARGE working incorectly with many dates. If the Pivot Table filter just only date 10, that functions will return the expected result.

# Re: FILTER and LARGE function.

Then just use =INDEX(SORT(FILTER(A:I,(A:A=\$K\$2)),7,-1),SEQUENCE(10),{3,7}). Omit LARGE() function because SEQUENCE(10) will only show 10 rows.

# Re: FILTER and LARGE function.

I actually got it.
Thank you again for help.

Regards,

Tuan.

1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

# Re: FILTER and LARGE function.

@littlevillage Use TAKE() function.

``=TAKE(CHOOSECOLS(SORT(FILTER(A:I,A:A=K2),7,-1),3,7),10)``