Need help with producing an item-similarity table / relationship mapping

Hi all,

I'm still pretty new to Excel, but have been growing really fond of the capabilities. New to the forums too, and I'm glad to be here.

I have an excel problem that is just way beyond my skill level. Probably more of a project than most questions that get posted here too. I would literally pay someone to help me with this.

So, I'm trying to produce a similarity value of one thing ("items", henceforth) to another, given that there are different values of standard characteristics of each one. (Values below are just examples, not what you'll find in the actual Excel docs. And things like A1 or C1 aren't cell references either. They just give definition to things. A's are items and C's are "characteristics")

So, say that there are hundreds of unique items (A1, A2, A3, etc.), and they all have the same standard characteristics (C1, C2, C3, etc), with different values assigned to each of those characteristics (ex: Item A1 has characteristic values of C1=40%, C2=85%, C3=25%, etc; Item A2 has characteristic values of C1=25%, C2=5%, C3=100%, etc.) This just means that an item like A2 has the full amount of "Characteristic 3".

Each of these characteristics has a degree of similarity to the others (or correlation) (ex: C1 and C5 might have a similarity of 80%, C2 and C9 might have a similarity of -15%, etc.)

Now, say that some user-input provides a rating for a few of these items. The user-rating of each item (and subsequent ratings of any future items) needs to produce a table of values with the most-similar items, taking into account the value of each item's characteristics and the relationships between each characteristic.

How can I use excel to produce a list of the most-similar items to each individual item in the user-rated list (while taking in to account the other user-rated items), along with a value of their similarity to the original item?

EDIT:

Latest Excel doc attached below. Mac Office '11 version. Please let me know if there is any trouble opening it.
ItemRelationMap.xlsx

Your description and the values in the link don't appear to match.

Also, why is your c-relationships matrix not symmetrical?

The values above are just examples, not what's actually in the document. Those are just =rand() numbers. This is completely hypothetical, btw. I just don't know how to build out the structure of formulas so this can be an updatable workbook. Sorry for the confusion.

I'm not sure what you mean by symmetrical, but I realized there's redundancies in the C-matrix I provided, considering both row and column labels consist of the same characteristics and one relationship would have already been established in a previous row/column. So I changed it to remove those redundancies.

I'm not sure if that's what you meant. And I'm sure it's not even the best matrix style for any forthcoming functions. Please let me know how I can improve it.

I've attached it again here with that change. Sorry, I didn't realize we could directly attach things in posts.

ItemRelationMap.xlsx

You just want a cross-correlation matrix?

	M	N	O	P	Q	R	S	T	U	V	W
11		C1	C2	C3	C4	C5	C6	C7	C8	C9	C10
12	C1	1.00
13	C2	-0.08	1.00
14	C3	0.21	-0.16	1.00
15	C4	0.37	0.22	0.13	1.00
16	C5	0.15	-0.38	-0.05	-0.34	1.00
17	C6	-0.13	0.07	-0.32	0.07	-0.04	1.00
18	C7	-0.23	-0.49	0.04	-0.21	-0.07	-0.55	1.00
19	C8	0.19	-0.42	0.64	0.46	0.10	-0.04	0.17	1.00
20	C9	0.08	0.26	0.32	-0.15	-0.32	0.38	-0.60	-0.26	1.00
21	C10	-0.23	0.52	-0.36	-0.35	-0.42	0.31	-0.34	-0.84	0.65	1.00

The formula in N12 and copied across and down is

=IF(ROWS($M$11:M11) < COLUMNS($M$11:M11), "", CORREL(B$12:B$21, INDEX($B$12:$K$21, 0, ROWS($M$11:M11))))

Oh, awesome, shg. Thank you. Yeah, my hand-made cross-correlation matrix was horrible.

Is there a way to eliminate the negative numbers, though?

It might still work for this purpose, but defining a lowest possible C-to-C relationship as 0% might be better.

Changing the numbers < 0 to 0 is very misleading. If you don't want to see them, format the cells as, for example, 0.00;-0.00;

Originally Posted by tmcalister

I formatted the cells like that as a custom format, but the negatives are still showing.

Sorry, the format should be 0.00;;0.00

What did you mean by misleading?

Because any two datasets have a correlation value between -1 and 1, and a negative correlation is not the same as no (0) correlation.

at least so as not to confuse anybody

It would only confuse people that know what correlation means.

Right, so is it more appropriate in this case to use a negative correlation?

I just figured a no (0) correlation would make it easier for people to understand the concept and problem.

EDIT (after shg's edit above):

Gotcha, that format works. It doesn't really make any more sense to hide the negatives, though, so I'll just leave them as is.

If they understand what correlation means, they will understand negative values.

But I have no idea what you're trying to do.

I'm sure there is published methodology for that, but you could instead calculate simple Euclidean distance between vectors, which is always non-negative.