Find Closest Matching Data Range

[Claymationator]

I have a set of data ranging from A5:BY76 (below is a sample of the table). Column A contains a segment name and row 5 contains the location name. The data within the table is a household count showing how many households fall into each segment for each location.

Excel 2010
	A	B	C	D	E	F
5	Segment Name	001 St. George	002 Provo	003 Fort Union	004 Skyline	005 Draper
6	A01: American Royalty	4	113	370	527	554
7	A02: Platinum Prosperity	214	850	1940	1548	1248
8	A03: Kids and Cabernet	82	1109	1423	628	3402
9	A04: Picture Perfect Families	768	2222	3012	2658	1794
10	A05: Couples with Clout	248	923	1539	899	2498
11	A06: Jet Set Urbanites	2	24	2	22	8
12	B07: Generational Soup	62	3262	3371	884	8274
AdHocPIVOT

What I want to do is create a formula where I look at the data range for a specific location, and then return the location name that most closely matches. Matching would be done comparing the household counts in each location.

 

[Aladin Akyurek]

Please try to provide an example of your reasoning.

 

[Claymationator]

The data in the table is psycho-graphic and demographic data from Mosaic. The household counts is the number of households that show up within a 9 minute drive time from a specific location. What I am trying to do is to take any location and find out which it is most similar to. It doesn't have so much impact on the existing locations as it does when we are looking for a new location to open up. In this case it would be a restaurant. So, what I do with the data is bring in the same set of data with household counts for a new location that we are considering...and I want to see which existing location it is most similar to. From there I can make some assumptions for the new location for future planning purposes. So, if the type of people in a new location are similar to another successful location that we already have opened, it would rank higher than a potential location that was similar to a location that wasn't performing as well.

In this case, I should have shown the results in the table above as a % of the households instead of a raw number to help account for more densely populated areas.

 

[Claymationator]

Here is the same data showing the % of the column instead of the raw number.

Excel 2010
	A	B	C	D	E	F
5	Segment Name	001 St. George	002 Provo	003 Fort Union	004 Skyline	005 Draper
6	A01: American Royalty	0.0%	0.1%	0.3%	0.8%	0.3%
7	A02: Platinum Prosperity	0.2%	0.6%	1.3%	2.4%	0.8%
8	A03: Kids and Cabernet	0.1%	0.7%	1.0%	1.0%	2.1%
9	A04: Picture Perfect Families	0.7%	1.5%	2.1%	4.1%	1.1%
10	A05: Couples with Clout	0.2%	0.6%	1.1%	1.4%	1.5%
11	A06: Jet Set Urbanites	0.0%	0.0%	0.0%	0.0%	0.0%
12	B07: Generational Soup	0.1%	2.2%	2.3%	1.4%	5.1%
AdHocPIVOT

 

[Aladin Akyurek]

I was asking for an example...

 

[Claymationator]

OK, so here I am just taking a subset of the data to illustrate. The second table starting in column I is showing the variance by category compared to the "New Location", and then at the bottom of the table I am using a variance formula across the selected segments, and the result shows that "001 St. George" has the least amount of variance from the "New Location", so the result for comparing the "New Location" to the 5 existing locations would be "001 St. George".

Part of my question is that I am also making some assumptions that VAR is the most appropriate formula to use when doing this type of comparison. Any feedback is appreciated.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		% of Households by Segment							Variance to New Location
2	Segment Name	001 St. George	002 Provo	003 Fort Union	004 Skyline	005 Draper	New Location		001 St. George	002 Provo	003 Fort Union	004 Skyline	005 Draper	New Location
3	A01: American Royalty	0.0%	0.1%	0.3%	0.8%	0.3%	0.0%		0.0%	0.0%	0.2%	0.8%	0.3%	0.0%
4	A02: Platinum Prosperity	0.2%	0.6%	1.3%	2.4%	0.8%	1.0%		-0.8%	-0.4%	0.4%	1.4%	-0.2%	0.0%
5	A03: Kids and Cabernet	0.1%	0.7%	1.0%	1.0%	2.1%	0.4%		-0.3%	0.3%	0.6%	0.6%	1.7%	0.0%
6	A04: Picture Perfect Families	0.7%	1.5%	2.1%	4.1%	1.1%	1.1%		-0.4%	0.3%	0.9%	3.0%	0.0%	0.0%
7	A05: Couples with Clout	0.2%	0.6%	1.1%	1.4%	1.5%	2.2%		-1.9%	-1.6%	-1.1%	-0.8%	-0.6%	0.0%
8	A06: Jet Set Urbanites	0.0%	0.0%	0.0%	0.0%	0.0%	0.0%		0.0%	0.0%	0.0%	0.0%	0.0%	0.0%
9	B07: Generational Soup	0.1%	2.2%	2.3%	1.4%	5.1%	0.3%		-0.2%	1.9%	2.0%	1.1%	4.8%	0.0%
10	B08: Babies and Bliss	2.7%	6.9%	6.0%	4.2%	14.7%	4.1%		-1.5%	2.8%	1.8%	0.1%	10.5%	0.0%
11									0.01%	0.02%	0.01%	0.01%	0.17%	0.00%
Sheet1

Cell Formulas
Range	Formula
I3	=B3-$G3
I11	=VAR(I4:I10)
J3	=C3-$G3
J11	=VAR(J4:J10)
K3	=D3-$G3
K11	=VAR(K4:K10)
L3	=E3-$G3
L11	=VAR(L4:L10)
M3	=F3-$G3
M11	=VAR(M4:M10)
N3	=G3-$G3
N11	=VAR(N4:N10)

 

[Aladin Akyurek]

Not sure I've got what I asked for...