I have a large data set that contains details about objects that are currently on an extension. The extensions are given a specific due date. Some of the extensions are past their due date.
I'm struggling to work out how to create a column in PowerPivot for O365 Excel that will return a yes/no value depending on if the object is one of the 5 most overdue extensions. So far nothing I've tried has worked at all.
Example with fake data:
+-----------+---------+--------------------+------------+
| ID | Urgency | Bus Days Remaining | Due Date |
+-----------+---------+--------------------+------------+
| 118017544 | Overdue | -487 | 1/04/2017 |
| 34960939 | Overdue | -97 | 30/09/2018 |
| 10695082 | Overdue | -364 | 20/09/2017 |
| 166236826 | Overdue | -86 | 15/10/2018 |
| 166236826 | Overdue | -86 | 15/10/2018 |
| 34944450 | Overdue | -437 | 9/06/2017 |
| 69427293 | Overdue | -446 | 29/05/2017 |
| 56280961 | Overdue | -437 | 9/06/2017 |
| 12535364 | Overdue | -176 | 11/06/2018 |
| 46296100 | Overdue | -163 | 28/06/2018 |
| 171666963 | Overdue | -122 | 24/08/2018 |
+-----------+---------+--------------------+------------+
The calculated column should be able to put a "Yes" next to 5 rows in this data that are the oldest.
Factors that might be important:
Multiple extensions can share a due date but be separate extensions. This makes me think that the formula needs to be based off of the "Bus Days Remaining" column value
Excel has a function in Pivot Tables where you only show the Top 10 values. This isn't an option for me because using that filter means you cannot drill into the Pivot Tables data.
Any help you could provide would be great :)
Thanks in advance
Please try this formula.
=C2<=SMALL(C$2:C$12,5)
If the 5th and 6th smallest are equal the formula will return TRUE for more than 5 items.