Issues with index mode if match formula

[StueyMacG]

Hi,
Looking for someone wiser than me to help.
I have a list of unique part numbers in a summary sheet for which I wish to assign a description. In a seperate sheet, I have a data table, consisting of part numbers and descriptions. Not all descriptions per part number are identical, and so, in my summary sheet, I wish to assign to each part number, the description which is most common from the data table.
The formula I have used to do this is as follows:
={IFERROR(INDEX(Table1[Description],MODE(IF(Table1[Part Number]=SUMMARY!A3,MATCH(Table1[Description],Table1[Description],0)))),"")}
It seems to be working well, except for part numbers in my summary sheet, that only have one entry in the data table, where it is returning blank due to the iferror. Why is this? Is the mode of one piece of data, not that piece of data?
Any help anyone can share on this woul dbe much appreciated.
Many thanks.

 

[XOR LX]

Is the mode of one piece of data, not that piece of data?

Unfortunately not, no. There must be at least one value which occurs more than once within the set.

One way to get round this is to artificially double occurrences of each of your values, viz:

=INDEX(Table1[Description],MODE(IF(Table1[Part Number]=SUMMARY!A3,{1,1}*MATCH(Table1[Description],Table1[Description],0))))

Regards

 

[StueyMacG]

artificially double occurrences of each of your values, viz:

=INDEX(Table1[Description],MODE(IF(Table1[Part Number]=SUMMARY!A3,{1,1}*MATCH(Table1[Description],Table1[Description],0))))

Thanks for your help.

What exactly does that section of formula do...I've never come across this syntax.

 

[XOR LX]

In a nutshell, the result of the expression:

MATCH(Table1[Description],Table1[Description],0)

will be a column-vector, e.g.:

{1;2;2;1;2;2;1;8}

When we form the product of this array with an array of a vector-type orthogonal to that here, i.e. with a row-vector (which is what {1,1} is - in Excel, commas and semi-colons represent separators in row- and column-vectors respectively), we have:

{1,1}*{1;2;2;1;2;2;1;8}

which results in:

{1,1;2,2;2,2;1,1;2,2;2,2;1,1;8,8}

Or, in other words, the result of the product of a 1-row-by-2-column array and an 8-row-by-1-column array is an 8-row-by-2-column array, the 16 entries in which being the results of multiplying the two elements in the first array by, in turn, each of the eight elements in the second array.

Regards

 

[StueyMacG]

Aha, thanks for the explanation. I have never seen this type of mathematics used within formula, but will bear in mind in future.

Regards

 

[XOR LX]

You're welcome!

Excel is mathematics!