LARGE function adjusted for filtered subtotals

[Hieronymus5]

Excel Problem 1.xlsx

Dear All,

I am having some issues with the LARGE function in conjunction with filters. In the Excel file provided, I want to rank the company past year returns from largest to smallest. However, while doing this I want to filter on Fiscal Year so I can check the ranking of returns per Fiscal Year.

I already got a formula from some forum (Mr. Excel?) but clearly, it doesn't work for me. Since I do not fully grasp (if, at all) the ranking formula in the workbook, I do not know why it returns an error and what to correct.

If anyone could help me with this I would be eternally grateful! (Well, probably for a few weeks anyway)

Regards,

Hieronymus5

[Fotis1991]

Formula in F3 and down is an ARRAY formula. Read how to use these formulaw in the link that i provide.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[martindwilson]

1, that is an aray formula and needs to be confirmed with ctrl shift +enter
2. once filter is applied it will hide the rows with your results in
eg filtering by 1993 shows 4 results rows 6,33,78,104 but large 1,2,3,4 are in cells f3:f6 which are now hidden from view
i havent 2010 but aggregate() could help here
otherwise id add a column that numbers filtered rows and reference that
eg in g3 filled down
=IF(SUBTOTAL(3,$A3),1+MAX($G$2:G2),"")
then change your formula to
{=LARGE(IF(SUBTOTAL(2, OFFSET($D$3,ROW($D$3:$D$122)-ROW($D$3),,1)),$D$3:$D$122),G3)}
see attached
you can wrap that in iferror

[micope21]

Hi

You can use this without array formula

=AGGREGATE(14,3,$D$3:$D$122,ROWS($1:1))

copy down this go with filter row

Yeah Martin didn't see till read it clear, Thank for point it out.

[martindwilson]

micope21 it is required to give large for filtered rows

[martindwilson]

had a rethink you can include it in one formula without a helper column assuming there will always be something in column a
{=LARGE(IF(SUBTOTAL(2, OFFSET($D$3,ROW($D$3:$D$122)-ROW($D$3),,1)),$D$3:$D$122),SUBTOTAL(3,$A$3:A3))}

[Hieronymus5]

Thank you all for responding as quickly as y'all did! Your solutions and feedback have helped me a great deal and saved ALOT of time.

Thanks, Hieronymus5

[kikoOo]

Amazing Amzing

[AliGW]

@kikoOo - I am watching you.