Need some assistance on Reporting and Calculations

Pam Ferguson · 01/09/20

Would love the communities advise on how to best solve this. I have an Aging Report that I need to provide the totals of the top 10 based on the $ amount (top 10 largest $ amount) and then the top 10 oldest items. How should I best get this information which will change from week to week?

Paul Newcome · 01/10/20

Ah. Ok. This is starting to make more sense.

Total dollar amount for 10 largest combined would be...

=SUMIFS([Dollar Amount]:[Dollar Amount], [Dollar Amount]:[Dollar Amount], @cell >= LARGE([Dollar Amount]:[Dollar Amount], 10))

Breakdown: We use the LARGE function to pull the 10th largest dollar amount. Then we use that as our criteria in a SUMIFS that says to add everything up that is greater than or equal to that amount.

The only time this will not accurately reflect a total of all top 10 combined is if you have duplicates in any of those 10 positions. Example...

100

95

90

85

If we run the above formula looking for the top two, we would get a total of 195 which is the top two added together. However, if we have...

100

95

90

85

We will actually get a total of 295 because there are actually 3 values greater than or equal to the 2nd largest.

If duplicate numbers could cause an issue like this, please let me know, and I will be happy to work towards a different solution.

We would use the same concept for pulling the total sum for the 10 oldest (this also comes with the same drawback as above where duplicate dates within the ranking could skew the data) (also let me know if you need something different than this).

We pull the 10th oldest date.

SMALL([Date Column]:[Date Column], 10)

Then we use a SUMIFS to give a total dollar amount for the 10th oldest and older.

=SUMIFS([Dollar Amount]:[Dollar Amount], [Date Column]:[Date Column], @cell <= SMALL([Date Column]:[Date Column], 10))

Paul Newcome · 01/09/20

Take a look at the LARGE function (link included).

The way it basically works is it allows you to look across a range and specify whether you want the 1st largest, 2nd largest, etc.

=LARGE(range, n)

where "n" is where you put the number for the ranking that you want to pull. 1 for 1st largest, 2 for second largest, etc.

Then for the oldest item, you would want to look at the SMALL function (link included).

It operates in much the same way except you would designate whether you want to see the first smallest, second smallest, etc.

=SMALL(range, n)

Pam Ferguson · 01/10/20

Paul,

Thanks so much! The LARGE function definitely gives me what I need, but I can't seem to use the SMALL function to find the top 10 oldest dated items. Since items changes on our list constantly, and it's reporting the $ amount of those top 10 oldest dated items, I am just wondering what the best solution would be in order for me to be able to provide this total cost to the C-Suite on a Dashboard. Any suggestions would be greatly appreciated.

Paul Newcome · 01/10/20

Maybe I am confused by exactly what you are looking for.

If you use something along the lines of

=SMALL([Date Column]:[Date Column], 1)

It will pull the oldest date on the sheet. Is that not exactly what you were wanting?

Pam Ferguson · 01/10/20

OMG, not enough coffee this morning. It worked (it helps when you change your summary field properties to a date type). Thank you so much!!

Pam Ferguson · 01/10/20

No problem.

I need to pull and display at any time the total amount of invoices on our aging report for the top 10 oldest invoices. Since the aging report can change during the day, invoices get paid and new "aged" invoices get placed on the report. Our leadership wants visibility into the Total amount of $'s for the top 10 largest invoices by $ amount and then the Total amount of $'s for the top 10 oldest invoices. Just trying to figure out the best formulas, reports, and construct to get them what they want without the support team doing a lot of additional work.

Paul Newcome · 01/10/20