Cure my array headache!

[grime]

I have a column with a list of stores.
On another sheet, I have a master list of stores, with their corresponding latitudes and longitudes.

Given 2 stores, I have a formula that computes the distance between them using each location's latitude and longitude.

I need an array that will find the maximum distance in the list of given stores.

For example, I input 10 stores. The array would look up the locations (lat & long) of each one of the 10 stores, find the distance for each one to each of the other 9 locations in the list, and find the maximum distance from the 100 possible scenarios (the 10 x 10 matrix).

The formula for computing distance is:
=(ACOS((COS((lat1/180)*PI())*COS((lat2/180)*PI())*COS(((long2-long1)/180)*PI()))+(SIN((lat1/180)*PI())*SIN((lat2/180)*PI()))))*3963.19

So if my list of stores was in E3 to E13:
long1 = vlookup(E3,'Master Store List'!$C:$J,8,FALSE)
lat1 = vlookup(E3,'Master Store List'!$C:$J,7,FALSE)
long2 = vlookup({E$3:E$13},'Master Store List'!$C:$J,8,FALSE)
lat2 = vlookup({E$3:E$13},'Master Store List'!$C:$J,7,FALSE)

I know that array within the LOOKUP function isn't corrent syntax, but that's what I'm after.

It would be nice to know which of the 2 locations provide the maximum distance as well, but thats a minor issue compared to the max distance.

Thanks in advance for any assitance you can give...

[Dave Peterson]

I think I would lay out an array 10 rows by 10 columns that would show the
distance between any two stores--much like the matrix you see in an atlas to
show distances between cities.

Then fill up the bottom half of the cells in that 10x10 matrix with the formula
between the two stores.

Then you could use format|conditional formatting to highlight the cell with the
greatest distance.



grime wrote:
>
> I have a column with a list of stores.
> On another sheet, I have a master list of stores, with their
> corresponding latitudes and longitudes.
>
> Given 2 stores, I have a formula that computes the distance between
> them using each location's latitude and longitude.
>
> I need an array that will find the maximum distance in the list of
> given stores.
>
> For example, I input 10 stores. The array would look up the locations
> (lat & long) of each one of the 10 stores, find the distance for each
> one to each of the other 9 locations in the list, and find the maximum
> distance from the 100 possible scenarios (the 10 x 10 matrix).
>
> The formula for computing distance is:
> =(ACOS((COS((lat1/180)*PI())*COS((lat2/180)*PI())*COS(((long2-long1)/180)*PI()))+(SIN((lat1/180)*PI())*SIN((lat2/180)*PI()))))*3963.19
>
> It would be nice to know which of the 2 locations provide the maximum
> distance as well, but thats a minor issue compared to the max
> distance.
>
> Thanks in advance for any assitance you can give...
>
> --
> grime
> ------------------------------------------------------------------------
> grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
> View this thread: http://www.excelforum.com/showthread...hreadid=390228

--

Dave Peterson

[grime]

Thanks Dave, but 2 problems with that...

First, the number of stores isn't always the same, so it wouldn't always be 10x10.

Secondly, some times the number of stores is more than 255, so the matrix would be too large to lay out in a grid.



I would like the output (in a single cell) at each row to be similar to:

LARGE(distance between store1 and store1, distance between store 1 and store2, distance between store 1 and store 3...)

next rown down would be:
LARGE(distance between store2 and store1, distance between store2 and store2, distance between store2 and store3...)

and so on...

then i can sum up with LARGE of each one of those outputs...

[aaron.kempf@gmail.com]

dude screw excel in the mouth

store your DATA in a database; and then it wont matter if your named
range is 100 rows or 1000.

welcome to math on steroids.. DATABASES

[Dave Peterson]

I didn't try it, but when you made that formula an array formua, did it work?

Lat1 would change to B2:B100
Long1 would change to c2:C100
and ctrl-shift-enter to array enter it.



grime wrote:
>
> Thanks Dave, but 2 problems with that...
>
> First, the number of stores isn't always the same, so it wouldn't
> always be 10x10.
>
> Secondly, some times the number of stores is more than 255, so the
> matrix would be too large to lay out in a grid.
>
>
>
> I would like the output (in a single cell) at each row to be similar
> to:
>
> MAX(distance between store1 and store1, distance between store 1 and
> store2, distance between store 1 and store 3...)
>
> next rown down would be:
> MAX(distance between store2 and store1, distance between store2 and
> store2, distance between store2 and store3...)
>
> and so on...
>
> then i can sum up with MAX of each one of those outputs...
>
> --
> grime
> ------------------------------------------------------------------------
> grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
> View this thread: http://www.excelforum.com/showthread...hreadid=390228

--

Dave Peterson

[grime]

No, it didn't work. I used vlookup and an array as the lookup value within the formula, within the LARGE function, but it didnt work.

I think you can only use constants within LARGE such as LARGE({1,4,5,6,8,12,14,16},3)

but not formulas: LARGE({1+1,1+2,3+5},2)

Frustrating...

[Alex]

How about this:

Put all your lat and long data in a table on SHEET1. Make sure each as
a unique field to identify it in the list. Put the ID in the first
column (column A), a description in the second, your lat value in the
third, and your long value in the fourth.

On SHEET2 in your workbook, make a big matrix that has as many rows and
columns in it as the most destinations that you will ever need. On row
1, starting with column B, put in all the unique fields in your table.
On column A, starting at row 2, put in the same list.

Now, in cell B2, put your forumula in with the following changes:

Wrap the entire thing with =IF(B$2 = "", "", <insert your formula
here>)
Change all references to lat1 with VLOOKUP(B$1, sheet1!A:D, 3, FALSE)
Change all references to long1 with VLOOKUP(B$1, sheet1!A:D, 4, FALSE)
Change all references to lat2 with VLOOKUP($A2, sheet1!A:D, 3, FALSE)
Change all references to long2 with VLOOKUP($A2, sheet1!A:D, 4, FALSE)

Copy this master formula into all of the fields in your array. This
gives you every combination of cities that you can ever have.

If you want to know the max value, the MAX function takes an array as
it's input! Just give it the full range of the output of the table
(e.g. b2:<whatever the other corner is) and it will find the max value
in the entire table.

If you want to find out which combination was your max, then do this:

At the bottom of each column (outside the range of your matrix data),
find the max value in just that column of data. At the right of each
row (again outside the range...), find the max value in just that row.

Then, do a LOOKUP to find your max value in the row and column max's
you just created!

The lookups may be a little erroneous, though. I believe they will
just give you the first instance if you have several that tie. What
are the odds of that, though?

I hope I explained this well enough. Good luck!

Alex.



grime wrote:
> I have a column with a list of stores.
> On another sheet, I have a master list of stores, with their
> corresponding latitudes and longitudes.
>
> Given 2 stores, I have a formula that computes the distance between
> them using each location's latitude and longitude.
>
> I need an array that will find the maximum distance in the list of
> given stores.
>
> For example, I input 10 stores. The array would look up the locations
> (lat & long) of each one of the 10 stores, find the distance for each
> one to each of the other 9 locations in the list, and find the maximum
> distance from the 100 possible scenarios (the 10 x 10 matrix).
>
> The formula for computing distance is:
> =(ACOS((COS((lat1/180)*PI())*COS((lat2/180)*PI())*COS(((long2-long1)/180)*PI()))+(SIN((lat1/180)*PI())*SIN((lat2/180)*PI()))))*3963.19
>
> It would be nice to know which of the 2 locations provide the maximum
> distance as well, but thats a minor issue compared to the max
> distance.
>
> Thanks in advance for any assitance you can give...
>
>
> --
> grime
> ------------------------------------------------------------------------
> grime's Profile: http://www.excelforum.com/member.php...o&userid=19227
> View this thread: http://www.excelforum.com/showthread...hreadid=390228

[grime]

That is exactly what I ended up doing. I was just looking for a shortcut to speed things up a bit.

Thank you for your response. Great minds think alike I guess!