Permutation of two sets of 15 numbers combined groups of 3

    Hello,
    I realize other people have asked about this, but I can't see how to adjust the answers given for this case. I have a list of 15 coordinates, i.e. 15 latitudes and 15 longitudes. I can place them in the first 2 columns A1 and B1. I need to have all the combinations listed in columns C1, D, and E1 for latitudes and F1, G, and H1 for longitudes. That's the major problem for me, that gives me groups of 3 coordinates which would represent spherical triangles on a globe. Then I want to use the Haversine formula to calculate the lengths of each of the three sides, these might end up in column I1, J1, and K1. Then I think I need to resort the lengths from the largest to the smallest, these might be in L1, M1, and N1. Then I need to work out the angles in each triangle, I have the formulas for that. The main sticking point is the combinations.

    Re: Permutation of two sets of 15 numbers combined groups of 3


    You need to break the problem down. Firstly it is a permutation of your lattitude and longitude. It is a permutation because order matters - Lat,Lng <> Lng,Lat. This gives you 15^2 (225) co-ordinates. You then take combination of those 225 co-ordinates taken 3 at a time. It is a combination because order does not matter - triangle A, B, C = B, C, A.

    You should find everything you need in:
    Repeat Values of Column A gainst Column B
    Number combination calculation

    You will need to concatenate your permutations before doing your combinations, but if you use <semi-colon> as your separator you can then use Data > Text to columns to separate everything back out again after.

    If you are still stuck post back

    Re: Permutation of two sets of 15 numbers combined groups of 3


    shg that looks good, I assume there are 455 combinations then? I notice though the results stop after 10, how can you make it give results for all 455?

    Re: Permutation of two sets of 15 numbers combined groups of 3


    Rob unfortunately I don't have the expertise to adapt your links to my problem.

    Re: Permutation of two sets of 15 numbers combined groups of 3


    Quote

    Drag the formulas down to see all 455 combinations.


    As stated.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

    Re: Permutation of two sets of 15 numbers combined groups of 3


    I think I worked it out. I copied the last row that had numbers in it, row 29, and special pasted the formulas down to row 455. That looks correct, thanks a lot. One last question, if I wanted to adjust this so there were more or less than 15 coordinates, say for example 12 or 20 how can I adjust this?

    Re: Permutation of two sets of 15 numbers combined groups of 3


    To ADD additional points, insert rows somewhere BELOW row 2 and ABOVE row 16 and add the points.

    Then adjust the formula in (what is now) B21:

    =NextCombo(B20:D20, N) where N is the new total number of points.

    After you change the formula in the Formula bar, DON'T press Enter; instead, press and hold the Ctrl and Shift keys, then press Enter. Then copy the formula down.

    To DELETE points, delete the appropriate number of rows between rows 2 and 16, and correct the formula in (what is now) B21 as above.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login