PB Formule sur Excel

Résolu
paradox0706 -  
Bonjour,

Quelqu'un pourrait-il me dire pour quoi cette formule ne marche pas?
Il s'agit d'une fonction qui calcule la distance entre 2 coordonnées géographiques.
L'algorithme que j'ai trouvé sur http://www.movable-type.co.uk/scripts/latlong.html est le sivant :

R = rayon de la terre (= 6371km)
Δlat = lat2− lat1
Δlong = long2− long1
a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
c = 2.atan2(√a, √(1−a))
distance = R*c

Sous Excel, j'ai implémenté ceci:

=2*ATAN2(RACINE(1-(SIN(((45,18555556-45,09611111)/2)*PI()/180)
*SIN(((45,18555556-45,09611111)/2)*PI()/180))+(COS(45,09611111*PI()/180)
*COS(45,18555556*PI()/180)*(SIN(((3,139166667-2,963611111)/2)*PI()/180))
*SIN(((3,139166667-2,963611111)/2)*PI()/180)));
RACINE(SIN(((45,18555556-45,09611111)/2)*PI()/180)*SIN(((45,18555556-45,09611111)/2)*PI()/180))
+(COS(45,09611111*PI()/180)*COS(45,18555556*PI()/180)*(SIN(((3,139166667-2,963611111)/2)*PI()/180))
*SIN(((3,139166667-2,963611111)/2)*PI()/180)))*6371

NB : J'ai interverti les argument de ATAN2 parce que Excel les interprete dans ce sens contrairement à la convention.
J'ai également converti les latitudes et longitudes des deux coordonnées en système décimal pour pouvoir les soustraires.
J'ai également converti les angles en radians. Sinon, le calcul serait implicitement faux.

Quelqu'un saurait-il pourquoi le résultat que j'obtient, et je le sais avec certitude, n'est pas juste?
je devrais avoir 17 km au lieu des 9,9 que j'obtiens
Configuration: Mac OS X
Firefox 2.0.0.8

7 réponses

  1. paradox0706
    OUFFF
    ça y est. j'ai recontruit la formule
    =2*ATAN2(RACINE(1-(SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2))*SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2)))+(COS(RADIANS('Coordonnées géographiques'!C4))*COS(RADIANS('Coordonnées géographiques'!C5))*(SIN(RADIANS(('Coordonnées géographiques'!C23-2,963611111)/2)))*SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2))));RACINE((SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2))*SIN(RADIANS(('Coordonnées géographiques'!C5-'Coordonnées géographiques'!C4)/2)))+(COS(RADIANS('Coordonnées géographiques'!C4))*COS(RADIANS('Coordonnées géographiques'!C5))*(SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2)))*SIN(RADIANS(('Coordonnées géographiques'!C23-'Coordonnées géographiques'!C22)/2)))))*6371

    Marche très bien C4,C22 et C5,C23 étant respectivement les latitudes,longitudes des points 1 et 2

    Merci Eric.
    3
    1. eriiic Contributeur
      Je pense que l'erreur d'arrondi serait également apparu ici...
      Reconstruis ta formule en étant attentif sur les ( ). Mets-en plutot plus que pas assez, quitte à supprimer celles qui sont inutiles ensuite si tu arrives au bon résultat.
      2
      1. nvaros
        Cette formule te sert réellement ou c'est juste pour comprendre
        0
        1. eriiic Contributeur
          cette question te sert réellement ?
          0
      2. eriiic Contributeur
        Bonjour,

        Tu as bien contrôlé que ce n'était pas dû à une erreur d'arrondi ?
        Sinon il faudra tester ta formule morceau par morceau...
        Pour tester un bout de ta formule tu sélectionnes ce bout dans la barre dédition et tu fais F9 dessus, cette partie sera évaluée.
        eric
        0
        2. paradox0706
          Merci Eric. Je l'ai testée morceau par morceau. A priori, il y a rien qui cloche.
          0
          1. paradox0706
            En fait, lorsque je teste
            R = rayon de la terre (= 6371km)
            Δlat = lat2− lat1
            Δlong = long2− long1
            a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
            c = 2.atan2(√a, √(1−a))
            distance = R*c

            en calculant étape par étape dans des cellules différentes, je trouve 16,9km qui est le bon résultat. La question c'est prkoi ça marche étape par étape et pas d'un seul coup.
            Je soupçonne l'erreur d'arrondi dont tu parlais Eriiic.
            0
            1. eriiic Contributeur
              Si rien ne cloche sûrement une erreur d'arrondi.
              Teste en calculant avec la dernière décimale supprimée puis avec +ou-1 sur la décimale pour avoir les valeurs mini-maxi des 2 points plus éloignés et plus rapprochés.
              Si 17 n'est pas inclu dans cette fourchette je pense que tu peux recontrôler ta formule en la découpant dans plusieurs cellules pour faciliter le contrôle.
              ex 49.15678 => 49.1567 et 49.1568
              -1.35463 => -1.3546 et -1.3547
              eric

              edit: nos messages se sont croisés
              0