# Find Closest Matching Data Range

#### Claymationator

##### Well-known Member
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
ABCDEF
5Segment Name001 St. George002 Provo003 Fort Union004 Skyline005 Draper
6A01: American Royalty4113370527554
7A02: Platinum Prosperity214850194015481248
8A03: Kids and Cabernet82110914236283402
9A04: Picture Perfect Families7682222301226581794
10A05: Couples with Clout24892315398992498
11A06: Jet Set Urbanites2242228
12B07: Generational Soup62326233718848274

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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Please try to provide an example of your reasoning.

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.

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

Excel 2010
ABCDEF
5Segment Name001 St. George002 Provo003 Fort Union004 Skyline005 Draper
6A01: American Royalty0.0%0.1%0.3%0.8%0.3%
7A02: Platinum Prosperity0.2%0.6%1.3%2.4%0.8%
8A03: Kids and Cabernet0.1%0.7%1.0%1.0%2.1%
9A04: Picture Perfect Families0.7%1.5%2.1%4.1%1.1%
10A05: Couples with Clout0.2%0.6%1.1%1.4%1.5%
11A06: Jet Set Urbanites0.0%0.0%0.0%0.0%0.0%
12B07: Generational Soup0.1%2.2%2.3%1.4%5.1%

I was asking for an example...

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
ABCDEFGHIJKLMN
1% of Households by SegmentVariance to New Location
2Segment Name001 St. George002 Provo003 Fort Union004 Skyline005 DraperNew Location001 St. George002 Provo003 Fort Union004 Skyline005 DraperNew Location
3A01: American Royalty0.0%0.1%0.3%0.8%0.3%0.0%0.0%0.0%0.2%0.8%0.3%0.0%
4A02: Platinum Prosperity0.2%0.6%1.3%2.4%0.8%1.0%-0.8%-0.4%0.4%1.4%-0.2%0.0%
5A03: Kids and Cabernet0.1%0.7%1.0%1.0%2.1%0.4%-0.3%0.3%0.6%0.6%1.7%0.0%
6A04: Picture Perfect Families0.7%1.5%2.1%4.1%1.1%1.1%-0.4%0.3%0.9%3.0%0.0%0.0%
7A05: Couples with Clout0.2%0.6%1.1%1.4%1.5%2.2%-1.9%-1.6%-1.1%-0.8%-0.6%0.0%
8A06: Jet Set Urbanites0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%0.0%
9B07: Generational Soup0.1%2.2%2.3%1.4%5.1%0.3%-0.2%1.9%2.0%1.1%4.8%0.0%
10B08: Babies and Bliss2.7%6.9%6.0%4.2%14.7%4.1%-1.5%2.8%1.8%0.1%10.5%0.0%
110.01%0.02%0.01%0.01%0.17%0.00%
Sheet1
Cell Formulas
RangeFormula
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)

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

Replies
1
Views
285
Replies
1
Views
380
Replies
0
Views
347
Replies
0
Views
278
Replies
17
Views
1K

1,196,479
Messages
6,015,447
Members
441,895
Latest member
Zululander

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back