Automatically create a numbered list with more combinations

[Eliasen]

I'll try to explain my "problem", but bare over with me if it doesn't come out right. I'm Danish, but can't be eaten. ;-)

I need a list which has all the possible combinations of the geographic coordinates ending with a zero after the decimal point (with five decimals). Example:

Latitude Longitude
5.00000 67.00000
...
...
10.00000 20.00000
...
...
10.00000 21.00000
...
...
19.00000 11.00000
...
...
71.00000 36.00000

I simply can't figure out, how I can get all the combinations in a sheet?

Additional info:
The latitude range goes from -90.00000 to 90.00000
The longitude range goes from -180.00000 to 180.00000

Thanks in advance.

[tigeravatar]

Eliasen,

To warn you:
From -90 to 90 (including 0) is 181 numbers
From -180 to 180 (including zero) 361

181*361 = 65341 rows (65342 rows if you use a header)

So the file is going to be decently sized, probably in the 4+ MB region. However, to address your question, you can use the following two formulas (assuming no headers):

In cell B1 put in -180, in cell A1, put in -90. column B will be longitude, column A will be latitude. Then in cell B2, use this formula:
=IF(B1=180,-180,B1+1)

In cell A2, use this formula:
=IF(B2=-180,A1+1,A1)

Then copy down to row 65341. If you need the .00000, just format the columns to Number with 5 decimal spaces.

Hope that helps,
~tigeravatar

[Eliasen]

Tiger,

Thanks a lot. Your suggestion pointed me in the right direction. I did however limit it to every tenth instead, just for testing purposes.

Thanks again.