Sorting latitudes and longitudes

G

Guest

I have a large dataset (~5000 rows) that is tied to latitdue and longitude
(small portion below). For each latitude/longitude reading I have between 8
and 10 columns of other data (not shown). I would like to sort the data into
latitude/longitude bins and then analyze it. The bin size I'm looking for is
2 degrees latitude (from -90 to 90) and 4 degrees longitude (-180 to 180).
This will produce 8100 bins. Only about 1/5 of these bins will have data (I'm
interested in data over continents only). In each bin I plan to average
numbers in the other columns to come up with standard values for each bin.

latitude longitude
-38.35 141.6
-38.27 144.67
-25.63 118.72
49.05 51.87
28.63 121.42
25.85 -81.38
29.75 -81.53
29.92 -90.13
34.42 -119.68
38.57 -76.07
43.42 -124.25

Thank you!
Ian
 
G

Guest

If you want to average the other columns individually


assume sheet1 has your data with latitude in column A and longitude in
column B
and columns C through L containing the data you wish to average

set up a list of cells
column M column N
-90 -180
-90 -176
....
-90 178
-88 -180
....
88 178
in O1
enter
=sumproduct(--($A$1:$A$64000>M1),--($A$1:$A$64000<=M1+2),--($B$1:$B$64000>N1),--($B$1:$B64000<=N1+4),$C$1:$C$64000)/sumproduct(--($A$1:$A$64000>M1),--($A$1:$A$64000<=M1+2),--($B$1:$B$64000>N1),--($B$1:$B$64000<=N1+4))
copy down and across to get all of cells you need averages for. Then go out
and have supper. It will take a long time to do the calculations.

A macro or macro fuction might be better if you have to do this very often.
 
M

MyVeryOwnSelf

I have a large dataset (~5000 rows) that is tied to latitdue and
longitude (small portion below). For each latitude/longitude reading I
have between 8 and 10 columns of other data (not shown). I would like
to sort the data into latitude/longitude bins and then analyze it. The
bin size I'm looking for is 2 degrees latitude (from -90 to 90) and 4
degrees longitude (-180 to 180). This will produce 8100 bins. Only
about 1/5 of these bins will have data (I'm interested in data over
continents only). In each bin I plan to average numbers in the other
columns to come up with standard values for each bin.

latitude longitude
-38.35 141.6
-38.27 144.67
-25.63 118.72
49.05 51.87
28.63 121.42
25.85 -81.38
29.75 -81.53
29.92 -90.13
34.42 -119.68
38.57 -76.07
43.42 -124.25
Click to expand...

One way to get started is to add a helper column that assigns each row to a
bin. For example, you might insert a new row C, put
=100*INT((INT(A2)+90)/2)+INT((INT(B2)+180)/4)
in C2 and copy down. With this formula, the first two digits split up the
latitudes; the second two, the longitudes.

What to do next depends on what kind of averaging you want. Maybe a pivot
table report would be useful.
 
G

Guest

Thank you both. The second post is more along the lines of what I want to do
as I just want to bin the data by latitude and longitude.

I do have some clarifying questions:

With the following data I produce the first 3 columns, using the suggested
formula,

Lat Long Bin
-77.85 166.67 947 -90 1
-70.77 11.83 1064 -88 2
-68.58 77.97 1156 -86 3
-67.67 45.85 1160 -84 4
-67.6 62.87 1127 -82 5
-67.57 -68.13 1168 -80 6
-66.55 93.02 1228 -78 7
-65.25 -64.27 1330 -76 8
-62.5 -59.68 1330 -74 9
-62.2 -58.93 1433 -72 10

The last two colums are the bin and the bin values for latitude that I would
expect the formula to produce for the latitude portion of the bin. For
instance, I would expect that the first row would fall in bin 747 rather than
947. Could you explain the formula a bit?

Thank you!
Ian
 
G

Guest

the 947 is appropriate for the -70.77 11.83
check that your equations are referring to the same row
 
D

David Biddulph

First point.
Your column C data is slipped by a row compared with columns A and B,
947 comes from -70.77 11.83, not from the row above.

Second point.
Your bin labelling in your 4th and 5th columns is also one row adrift.
From -90.0 to -88.0 is bin zero. From -88.0 to -86.0 is bin 1. From -72.0
to -70.0 is bin 9, so that's what you get for a latitude of -70.77, hence
947, not 747 (which would be latitudes from -76.0 to -74.0). Just plug
appropriate numbers into the formula and you'll see how it works.
 
G

Guest

Thank you! Very helpful.

Cheers,
Ian

David Biddulph said:
First point.
Your column C data is slipped by a row compared with columns A and B,
947 comes from -70.77 11.83, not from the row above.

Second point.
Your bin labelling in your 4th and 5th columns is also one row adrift.
From -90.0 to -88.0 is bin zero. From -88.0 to -86.0 is bin 1. From -72.0
to -70.0 is bin 9, so that's what you get for a latitude of -70.77, hence
947, not 747 (which would be latitudes from -76.0 to -74.0). Just plug
appropriate numbers into the formula and you'll see how it works.
Click to expand...
 
Top