Using Collect function within Large function

Allison_S

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))

Brian W

Try it this way:

=LARGE(COLLECT([Total Sales Dollars]:[Total Sales Dollars], Hierarchy:Hierarchy, 2), Rank@row)

Allison_S

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))

 

Brian W

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?