Using Collect function within Large function
Hello All,
I am trying to nest a criteria within the LARGE function. I have 2 levels of children within my sheet and I have placed a hierarchy column so I can select just the children with a hierarchy ranking of "2" for the LARGE function. My formula below, but it is throwing errors. Any help would be appreciated!
=LARGE(COLLECT((Hierarchy)@row, =2), [Total Sales Dollars]:[Total Sales Dollars]), (Rank@row))
Comments
-
Try it this way:
=LARGE(COLLECT([Total Sales Dollars]:[Total Sales Dollars], Hierarchy:Hierarchy, 2), Rank@row)
-
Brian, this worked perfectly! THANK YOU! I am now trying to return information corresponding with the top ten dollar amounts, but it is sporadically working (see attached image). It's returning some values, while others are left blank. Here is my formula, any suggestions?
=INDEX(CHILDREN(ASIN:ASIN), MATCH([Top Ten $]@row, [Total Sales Dollars]:[Total Sales Dollars], 0))
-
So, CHILDREN is functioning this way in the formula:
MATCH is returning a row number (N) and INDEX is returning the Nth child row in the ASIN column
which I'm sure is not returning your expected result.
Without seeing the whole sheet, it's hard to come up with a solution. Can you share more information about how you wanted CHILDREN to functioning within the formula?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives