Need some assistance on Reporting and Calculations
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?
Best Answer
-
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
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!!
-
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.
-
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
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This is PERFECT!! THANK YOU SO MUCH! This forum is so helpful!
-
Happy to help! 👍️
If you end up needing to account for duplicates, feel free to revisit the thread and @mention me to get my attention.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives