I have a list of customer accounts with various past due balances. I would like a formula to rank the accounts based on past due values while excluding specific accounts that have been placed on a separate "Do Not Include" list.
I have used LARGE, which works great until I need to exclude certain accounts from the Top 5 List. I am having difficulty finding/constructing a formula that can achieve the exampled Top 5 Highest Past Due Accounts below.
Please see the example below and please do let me know if there are questions I may clarify.
I have the report below...
Aging Report:
<tbody>
</tbody>
...that needs to evaluate the exclusion list below...
Do Not Include:
<tbody>
</tbody>
...in order to produce the results below:
Top 5 Highest Past Due Accounts:
<tbody>
</tbody>
Any assistance is greatly appreciated! Please let me know where I can provide further clarification.
Thank you,
RGH
I have used LARGE, which works great until I need to exclude certain accounts from the Top 5 List. I am having difficulty finding/constructing a formula that can achieve the exampled Top 5 Highest Past Due Accounts below.
Please see the example below and please do let me know if there are questions I may clarify.
I have the report below...
Aging Report:
A | B | C | ||
1 | Account # | Customer Name | Past Due Amount | |
2 | 1A | Alpha | 1,000 | |
3 | 2B | Bravo | 15,000 | |
4 | 3C | Charlie | 20,000 | |
5 | 4D | Delta | 5,000 | |
6 | 5E | Echo | 2,500 | |
7 | 6F | Foxtrot | 15,000 | |
8 | 7G | Golf | 17,000 | |
<tbody>
</tbody>
...that needs to evaluate the exclusion list below...
Do Not Include:
A | B | |||
Excluded Account # | Excluded Customer Name | |||
10 | 2B | Bravo | ||
11 | 3C | Charlie |
<tbody>
</tbody>
...in order to produce the results below:
Top 5 Highest Past Due Accounts:
A | B | C | ||
20 | Account # | Customer Name | Past Due Amount | |
21 | 7G | Golf | 17,000 | |
22 | 6F | Foxtrot | 15,000 | |
23 | 4D | Delta | 5,000 | |
24 | 5E | Echo | 2,500 | |
25 | 1A | Alpha | 1,000 | |
<tbody>
</tbody>
Any assistance is greatly appreciated! Please let me know where I can provide further clarification.
Thank you,
RGH