How to do a “great circle” calculation in MS Excel or LibreOffice?












3















I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:



haversine calculation



That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.



The data looks like that:



  |        A        B    C    D    E   F    G
--|-------------------------------------------
0 | LAT LON rLAT dLAT dLON a DIST
1 | 52.39964 13.04729 ... ... ... ...
2 | 52.39985 13.04802 ... ... ... ... ???
3 | 52.40116 13.04744 ... ... ... ... ???
4 | 52.40147 13.04722 ... ... ... ... ???
5 | 52.40163 13.04685 ... ... ... ... ???
6 | ... ... ... ... ... ... ...


Now, what I have tried is the following for the field G2 in LibreOffice:



C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371


The result for G2 is20015 which is quite... wrong.



How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?










share|improve this question


















  • 3





    Quick google search : bluemm.blogspot.com/2007/01/…

    – Brian Adkins
    Jun 2 '13 at 15:42
















3















I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:



haversine calculation



That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.



The data looks like that:



  |        A        B    C    D    E   F    G
--|-------------------------------------------
0 | LAT LON rLAT dLAT dLON a DIST
1 | 52.39964 13.04729 ... ... ... ...
2 | 52.39985 13.04802 ... ... ... ... ???
3 | 52.40116 13.04744 ... ... ... ... ???
4 | 52.40147 13.04722 ... ... ... ... ???
5 | 52.40163 13.04685 ... ... ... ... ???
6 | ... ... ... ... ... ... ...


Now, what I have tried is the following for the field G2 in LibreOffice:



C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371


The result for G2 is20015 which is quite... wrong.



How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?










share|improve this question


















  • 3





    Quick google search : bluemm.blogspot.com/2007/01/…

    – Brian Adkins
    Jun 2 '13 at 15:42














3












3








3


3






I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:



haversine calculation



That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.



The data looks like that:



  |        A        B    C    D    E   F    G
--|-------------------------------------------
0 | LAT LON rLAT dLAT dLON a DIST
1 | 52.39964 13.04729 ... ... ... ...
2 | 52.39985 13.04802 ... ... ... ... ???
3 | 52.40116 13.04744 ... ... ... ... ???
4 | 52.40147 13.04722 ... ... ... ... ???
5 | 52.40163 13.04685 ... ... ... ... ???
6 | ... ... ... ... ... ... ...


Now, what I have tried is the following for the field G2 in LibreOffice:



C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371


The result for G2 is20015 which is quite... wrong.



How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?










share|improve this question














I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:



haversine calculation



That's the so called "great circle" calculation. I need perform this calculation on a huge amount of coordinates.



The data looks like that:



  |        A        B    C    D    E   F    G
--|-------------------------------------------
0 | LAT LON rLAT dLAT dLON a DIST
1 | 52.39964 13.04729 ... ... ... ...
2 | 52.39985 13.04802 ... ... ... ... ???
3 | 52.40116 13.04744 ... ... ... ... ???
4 | 52.40147 13.04722 ... ... ... ... ???
5 | 52.40163 13.04685 ... ... ... ... ???
6 | ... ... ... ... ... ... ...


Now, what I have tried is the following for the field G2 in LibreOffice:



C2=RADIANS(A2)
D2=RADIANS(A2-A1)
E2=RADIANS(B2-B1)
F2=SIN(D2/2)*SIN(D2/2)+SIN(E2/2)*SIN(E2/2)*COS(C1)*COS(C2)
G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371


The result for G2 is20015 which is quite... wrong.



How do I calculate the distance between two points specified by latitude and longitude in Microsoft Excel or LibreOffice Calc? What's wrong with my formula?







microsoft-excel worksheet-function libreoffice-calc calculator






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jun 2 '13 at 15:26









AfriAfri

62241327




62241327








  • 3





    Quick google search : bluemm.blogspot.com/2007/01/…

    – Brian Adkins
    Jun 2 '13 at 15:42














  • 3





    Quick google search : bluemm.blogspot.com/2007/01/…

    – Brian Adkins
    Jun 2 '13 at 15:42








3




3





Quick google search : bluemm.blogspot.com/2007/01/…

– Brian Adkins
Jun 2 '13 at 15:42





Quick google search : bluemm.blogspot.com/2007/01/…

– Brian Adkins
Jun 2 '13 at 15:42










4 Answers
4






active

oldest

votes


















5














As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:



=ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371


The result is in kilometers, for small distances I used *1000 to display the distance in meters.



  |        A        B      C
--|--------------------------
0 | LAT LON DIST
1 | 52.39964 13.04729
2 | 52.39985 13.04802 54.8
3 | 52.40116 13.04744 150.9
4 | 52.40147 13.04722 37.6
5 | 52.40163 13.04685 30.8
6 | ... ... ...


To display distances in miles, substitute *6371 with *3958.






share|improve this answer

































    2














    The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.



    A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at:
    http://lost-species.livejournal.com/38453.html
    The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.



    Regards,
    J.Latham, Microsoft MVP, Excel 2006-2014






    share|improve this answer































      0














      Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at https://github.com/tdjastrzebski/VincentyExcel






      share|improve this answer































        -1














        If you reverse the arguments below:



        Was:
        G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371



        Is:
        G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371



        You should get the correct answer. I hope this helps!






        share|improve this answer
























        • Can you add a brief explanation of why this fixes the problem? Thanks.

          – fixer1234
          Dec 29 '18 at 4:02











        Your Answer








        StackExchange.ready(function() {
        var channelOptions = {
        tags: "".split(" "),
        id: "3"
        };
        initTagRenderer("".split(" "), "".split(" "), channelOptions);

        StackExchange.using("externalEditor", function() {
        // Have to fire editor after snippets, if snippets enabled
        if (StackExchange.settings.snippets.snippetsEnabled) {
        StackExchange.using("snippets", function() {
        createEditor();
        });
        }
        else {
        createEditor();
        }
        });

        function createEditor() {
        StackExchange.prepareEditor({
        heartbeatType: 'answer',
        autoActivateHeartbeat: false,
        convertImagesToLinks: true,
        noModals: true,
        showLowRepImageUploadWarning: true,
        reputationToPostImages: 10,
        bindNavPrevention: true,
        postfix: "",
        imageUploader: {
        brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
        contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
        allowUrls: true
        },
        onDemand: true,
        discardSelector: ".discard-answer"
        ,immediatelyShowMarkdownHelp:true
        });


        }
        });














        draft saved

        draft discarded


















        StackExchange.ready(
        function () {
        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f602798%2fhow-to-do-a-great-circle-calculation-in-ms-excel-or-libreoffice%23new-answer', 'question_page');
        }
        );

        Post as a guest















        Required, but never shown

























        4 Answers
        4






        active

        oldest

        votes








        4 Answers
        4






        active

        oldest

        votes









        active

        oldest

        votes






        active

        oldest

        votes









        5














        As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:



        =ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371


        The result is in kilometers, for small distances I used *1000 to display the distance in meters.



          |        A        B      C
        --|--------------------------
        0 | LAT LON DIST
        1 | 52.39964 13.04729
        2 | 52.39985 13.04802 54.8
        3 | 52.40116 13.04744 150.9
        4 | 52.40147 13.04722 37.6
        5 | 52.40163 13.04685 30.8
        6 | ... ... ...


        To display distances in miles, substitute *6371 with *3958.






        share|improve this answer






























          5














          As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:



          =ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371


          The result is in kilometers, for small distances I used *1000 to display the distance in meters.



            |        A        B      C
          --|--------------------------
          0 | LAT LON DIST
          1 | 52.39964 13.04729
          2 | 52.39985 13.04802 54.8
          3 | 52.40116 13.04744 150.9
          4 | 52.40147 13.04722 37.6
          5 | 52.40163 13.04685 30.8
          6 | ... ... ...


          To display distances in miles, substitute *6371 with *3958.






          share|improve this answer




























            5












            5








            5







            As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:



            =ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371


            The result is in kilometers, for small distances I used *1000 to display the distance in meters.



              |        A        B      C
            --|--------------------------
            0 | LAT LON DIST
            1 | 52.39964 13.04729
            2 | 52.39985 13.04802 54.8
            3 | 52.40116 13.04744 150.9
            4 | 52.40147 13.04722 37.6
            5 | 52.40163 13.04685 30.8
            6 | ... ... ...


            To display distances in miles, substitute *6371 with *3958.






            share|improve this answer















            As found here by BrianAdkins, this Excel formula to calculate distance between two latitude/longitude points works for me both in LibreOffice Calc and Microsoft Excel 2013:



            =ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-A2))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-A2))*COS(RADIANS(B1-B2)))*6371


            The result is in kilometers, for small distances I used *1000 to display the distance in meters.



              |        A        B      C
            --|--------------------------
            0 | LAT LON DIST
            1 | 52.39964 13.04729
            2 | 52.39985 13.04802 54.8
            3 | 52.40116 13.04744 150.9
            4 | 52.40147 13.04722 37.6
            5 | 52.40163 13.04685 30.8
            6 | ... ... ...


            To display distances in miles, substitute *6371 with *3958.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Mar 20 '17 at 10:04









            Community

            1




            1










            answered Jun 2 '13 at 17:11









            AfriAfri

            62241327




            62241327

























                2














                The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.



                A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at:
                http://lost-species.livejournal.com/38453.html
                The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.



                Regards,
                J.Latham, Microsoft MVP, Excel 2006-2014






                share|improve this answer




























                  2














                  The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.



                  A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at:
                  http://lost-species.livejournal.com/38453.html
                  The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.



                  Regards,
                  J.Latham, Microsoft MVP, Excel 2006-2014






                  share|improve this answer


























                    2












                    2








                    2







                    The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.



                    A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at:
                    http://lost-species.livejournal.com/38453.html
                    The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.



                    Regards,
                    J.Latham, Microsoft MVP, Excel 2006-2014






                    share|improve this answer













                    The Haversine method is good as long as you're willing to accept an error factor, or if you must implement with a worksheet formula only. For short distances and non critical situations it will probably be ok. But if you need precision, you need to use something like the algorithm developed by Thaddeus Vincenty. It's results are considered accurate down to millimeters based on the accuracy of the lat/long pairs used. How much difference between the two? Between JFK and Tokyo it's a difference of about 14.9 statute miles (short). Between Los Angeles and Honolulu, you'll only be about 3 miles short of the island.



                    A good implementation of the Vincenty algorithm in Visual Basic (as for Excel) can be found at:
                    http://lost-species.livejournal.com/38453.html
                    The code will run 'as-is' on 32-bit versions of Excel, but will fail with a 'formula too complex' error in the 64-bit version of Excel. Just below the posting of the entire code I have posted a fix to that problem that enables it to run on both 32 and 64 bit versions of the VBA engine.



                    Regards,
                    J.Latham, Microsoft MVP, Excel 2006-2014







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Sep 2 '14 at 5:54









                    J.LathamJ.Latham

                    311




                    311























                        0














                        Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at https://github.com/tdjastrzebski/VincentyExcel






                        share|improve this answer




























                          0














                          Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at https://github.com/tdjastrzebski/VincentyExcel






                          share|improve this answer


























                            0












                            0








                            0







                            Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at https://github.com/tdjastrzebski/VincentyExcel






                            share|improve this answer













                            Complete direct/inverse Vincenty's formulae Excel VBA implementation (32/64 bit add-in) allowing not only for distance calculation but azimuth and reverse azimuth as well is available at https://github.com/tdjastrzebski/VincentyExcel







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jul 13 '18 at 23:19









                            T. JastrzębskiT. Jastrzębski

                            1




                            1























                                -1














                                If you reverse the arguments below:



                                Was:
                                G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371



                                Is:
                                G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371



                                You should get the correct answer. I hope this helps!






                                share|improve this answer
























                                • Can you add a brief explanation of why this fixes the problem? Thanks.

                                  – fixer1234
                                  Dec 29 '18 at 4:02
















                                -1














                                If you reverse the arguments below:



                                Was:
                                G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371



                                Is:
                                G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371



                                You should get the correct answer. I hope this helps!






                                share|improve this answer
























                                • Can you add a brief explanation of why this fixes the problem? Thanks.

                                  – fixer1234
                                  Dec 29 '18 at 4:02














                                -1












                                -1








                                -1







                                If you reverse the arguments below:



                                Was:
                                G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371



                                Is:
                                G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371



                                You should get the correct answer. I hope this helps!






                                share|improve this answer













                                If you reverse the arguments below:



                                Was:
                                G2=2*ATAN2(SQRT(F2), SQRT(1-F2))*6371



                                Is:
                                G2=2*ATAN2(SQRT(1-F2), SQRT(F2))*6371



                                You should get the correct answer. I hope this helps!







                                share|improve this answer












                                share|improve this answer



                                share|improve this answer










                                answered Dec 29 '18 at 3:51









                                abiMathabiMath

                                1




                                1













                                • Can you add a brief explanation of why this fixes the problem? Thanks.

                                  – fixer1234
                                  Dec 29 '18 at 4:02



















                                • Can you add a brief explanation of why this fixes the problem? Thanks.

                                  – fixer1234
                                  Dec 29 '18 at 4:02

















                                Can you add a brief explanation of why this fixes the problem? Thanks.

                                – fixer1234
                                Dec 29 '18 at 4:02





                                Can you add a brief explanation of why this fixes the problem? Thanks.

                                – fixer1234
                                Dec 29 '18 at 4:02


















                                draft saved

                                draft discarded




















































                                Thanks for contributing an answer to Super User!


                                • Please be sure to answer the question. Provide details and share your research!

                                But avoid



                                • Asking for help, clarification, or responding to other answers.

                                • Making statements based on opinion; back them up with references or personal experience.


                                To learn more, see our tips on writing great answers.




                                draft saved


                                draft discarded














                                StackExchange.ready(
                                function () {
                                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f602798%2fhow-to-do-a-great-circle-calculation-in-ms-excel-or-libreoffice%23new-answer', 'question_page');
                                }
                                );

                                Post as a guest















                                Required, but never shown





















































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown

































                                Required, but never shown














                                Required, but never shown












                                Required, but never shown







                                Required, but never shown







                                Popular posts from this blog

                                Plaza Victoria

                                Puebla de Zaragoza

                                Musa