BrettOlbrys1
Board Regular
- Joined
- May 1, 2018
- Messages
- 128
- Office Version
- 365
- Platform
- Windows
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
=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