LARGE function adjusted for filtered subtotals

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

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.

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

Hi

You can use this without array formula

Formula:

Please Login or Register  to view this content.

copy down this go with filter row

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

micope21 it is required to give large for filtered rows

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))}

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 - I am watching you.