#INVALID VALUE error with collect/index/match/small function

Brandon Vanlandingham

Hi all,

Feeling like I've got a real head scratcher here- I have been struggling with this formula creation for the past few days and am hoping that the gurus can help!

Within my sheet, I have UniquePart pulling in a list of unique values from a source sheet. The frequency of the part within the source sheet is then recorded in the UniquePart Frequency column using a simple COUNTIF. With this information, I want to rank the part numbers 1-15 based on the frequency of their occurrence. I initially attempted a basic LARGE function to rank the frequencies, and then an INDEX/MATCH to return the part number for the given frequency; however, because multiple parts have the same frequency (3 parts w/ 9 occurrences for example), the formula would repeat the first part number with that frequency for each subsequent rank.

So, I have now come to the following formula set which almost works. It's broken down below...

Part+ Part+ looks at each frequency in the UniquePart Frequency column and checks if it is between the first and fifteenth largest value. Then, if it is the first instance, it records that value. If it is the x-th instance of that frequency, then it returns frequency plus x.

=IF(AND([UniquePart Frequency]@row >= LARGE([UniquePart Frequency]:[UniquePart Frequency], MAX([Top10 Part]:[Top10 Part])), [UniquePart Frequency]@row <= LARGE([UniquePart Frequency]:[UniquePart Frequency], 1)), IF(COUNTIF([UniquePart Frequency]$1:[UniquePart Frequency]@row, [UniquePart Frequency]@row) > 1, [UniquePart Frequency]@row + (COUNTIF([UniquePart Frequency]$1:[UniquePart Frequency]@row, [UniquePart Frequency]@row)), [UniquePart Frequency]@row))

Part Rank Part Rank assigns a rank to the Part+ column in descending order

=IF([Part+]@row <> "", RANKEQ([Part+]@row, [Part+]:[Part+]), "")

Part QN Frequency Collects a range of values from the UniquePart Frequency column with a non-blank Part Rank row. It then matches the rank of these values to the corresponding value in the Top10 part column.

=LARGE(COLLECT([UniquePart Frequency]$1:[UniquePart Frequency]$1000, [Part Rank]$1:[Part Rank]$1000, NOT(ISBLANK(@cell))), [Top10 Part]@row)

Part # Part # first collects UniqueParts with a UniquePart Frequency corresponding to the Part QN Frequency. If there is only one value in this range, it uses an index/match function to return the correct part #. If there is more than one instance of this frequency, then the formula collects a range of UniqueParts with a frequency matching what is in the Part QN Frequency column. It then returns the values with the x-th smallest frequency.

=IF(COUNT(COLLECT(UniquePart:UniquePart, [UniquePart Frequency]:[UniquePart Frequency], [Part QN Frequency]@row)) = 1, INDEX(UniquePart:UniquePart, MATCH([Part QN Frequency]@row, [UniquePart Frequency]:[UniquePart Frequency], 0)), SMALL(COLLECT(UniquePart:UniquePart, [UniquePart Frequency]:[UniquePart Frequency], [Part QN Frequency]@row), 1))

So, here's the issue and where I am receiving my #INVALID VALUE. My logic behind using the small/collect combination in the last formula was hopefully that it would return the 1st, 2nd, 3rd, and so on value within the range collected. I'm receiving mixed results from the same formula though. For example, the formula works perfect for the 1st and 2nd instance of 9 Part QN Frequency, but it breaks on the third instance. I was wondering if maybe it just has to do with the last value in the set, but when looking at the 6 Part QN Frequency, the formula works for the 1st-4th instance, but breaks on the 5th and 6th. The only difference between my formulas is changing the "1" specified within the small/collect function to a 2, 3, or any other value I'm working to pull from the collect.

Any and all help is greatly appreciated!!! Thank you in advance!!!!!

Cheers,

Brandon

Genevieve P.

Hi @Brandon Vanlandingham

I believe you're getting this error because the UniquePart column has cells that contain both text and numerical values; the SMALL function will need only numerical values filtered in the Collect in order to figure out which one is the smallest.

We can filter out anything that has text by adding another column and condition to the Collect formula, but then it would skip values such as the last one in your screen capture which contains letters like BATG.

Ex:

SMALL(COLLECT(UniquePart:UniquePart, UniquePart:UniquePart, ISNUMBER(@cell), [UniquePart Frequency]:[UniquePart Frequency], [Part QN Frequency]@row)

Try:

=IF(COUNT(COLLECT(UniquePart:UniquePart, [UniquePart Frequency]:[UniquePart Frequency], [Part QN Frequency]@row)) = 1, INDEX(UniquePart:UniquePart, MATCH([Part QN Frequency]@row, [UniquePart Frequency]:[UniquePart Frequency], 0)), SMALL(COLLECT(UniquePart:UniquePart, UniquePart:UniquePart, ISNUMBER(@cell), [UniquePart Frequency]:[UniquePart Frequency], [Part QN Frequency]@row), 1))

Let me know if this resolved the issue!

Cheers,

Genevieve