#NUM Error Help Needed

[BrettOlbrys1]

I have the following formula:

=INDEX(SORT(FILTER(Activations,(Activations[Qty]>=(LARGE(IF(Activations[Product]=D34,Activations[Qty]),5)))*(Activations[Product]=D34)),3,-1),SEQUENCE(5),{1})

The result of this formula is a list of the 5 largest values from a dataset. The two numbers in red font tell the formula to find and list only the top 5 values. But if there are LESS THAN 5 values that meet the criteria within the formula, I get a #NUM error because it is looking for 5 values.

What I want to happen is to find and list no more than the 5 largest values, but if there are only 4, 3, 2, or 1 value that meets the criteria in the formula, I want it to be listed, not a #NUM error.

How can I resolve this issue?

Thanks

Brett

 

[Fluff]

Do you have the TAKE function yet?

 

[BrettOlbrys1]

Do you have the TAKE function yet?

No, I don't even know what that is?

 

[Fluff]

Ok, how about

=LET(f,SORT(FILTER(Activations,Activations[Product]=D34),3,-1),INDEX(f,SEQUENCE(MIN(ROWS(f),5)),1))

 

[BrettOlbrys1]

Ok, how about

=LET(f,SORT(FILTER(Activations,Activations[Product]=D34),3,-1),INDEX(f,SEQUENCE(MIN(ROWS(f),5)),1))

Sorry, I didn't realize you responded for some reason. Thanks for the formula, what does "f" represent?

 

[Fluff]

It's just a variable that stores the result of the sort/filter formula, so it's only calculated once rather than twice.