XL 2016 Encadrer des valeurs par des courbes de tendance

[pingu]

Bonjour à tous,

Nouveau sur le forum mais ayant déja une bonne experience excel/vba, je cherche une petite aide au problème suivant (la solution étant probablement plus mathématique qu'un outil excel lui-même.

J'ai un nuage de points classique auquel je demande l'ajout d'une courbe de tendance linéaire comme ci dessous :

J'obtiens une équation avec le coefficient de corrélation qui me convient.

Je cherche maintenant à encadrer un pourcentage de la population de points (par exemple 90% des points) dans un intevalle de deux droites parallèles tracées en orange à la main ci dessus.
Mes deux droites sont donc similaires, seule l'ordonnée à l'origine change, mais comment la déterminer ?

Je joins un classeur simplifié si cela peut servir

Merci d'avance

 

[sylvanu]

Bonjour Pingu, et bienvenu sur XLD.
En PJ un exemple de ce qui peut être fait :
1- On trace la droite typique des données en utilisant Droitereg pour obtenir a et b :

=A2*DROITEREG($B$2:$B$15;$A$2:$A$15)+INDEX(DROITEREG($B$2:$B$15;$A$2:$A$15);2)

2- On calcule l'écart type des écarts entre typique et données ( cellule H1 ) avec :

=ECARTYPE(C2:C15-B2:B15)

Formule matricielle, validez par Maj+Ctrl+Entrée
3- On calcule les courbes min et max avec :

Min=Typique - N écartype
Max=Typique + N écartype

N étant choisi en H2, avec un intervalle de confiance de 68%, 95% ou 99%

Il y a peut être plus simple, mais je ne vois pas comment.

 

[Sylvain]

Bonjour,

Je propose de faire prix réel-valeur prévue.
puis je fais le calcul de l'écart type de cette nouvelle information.
Enfin, on a 87% de la population entre +- 1.5 * cet écart type.

Cela n'est valable que si les écarts restent comparables quand on part de la gauche vers la droite et suivent une loi normale

 

[Sylvain]

Autre possibilité, si l'objectif est un pourcentage sans hypothèse sur la normalité, vous pouvez prendre le classeur ci-dessous et utiliser l'outil valeur cible (données/analyse scénarios).
Si vous voulez 70% à l'intérieur vous indiquez
valeur à définir G18
Valeur à atteindre 0,7
Cellule à modifier F16

Et excel va essayer de trouver une valeur d'intervalle répondant à votre souhait d'avoir x% entre les deux droites.

 

[pingu]

Excellent, merci à vous deux !

La dernière solution de Sylvain correspond effectivement le plus à ma demande initiale mais la 1e solution de Sylvanu me donne aussi des idées quand à la pertinence de mon choix d'analyse (analyse 6 sigma sur la régression initiale).
Et en plus cela me fait découvrir les fonctions prévision et droitereg qui pourront toujours resservir

Bonne soirée