Aide formule % en fonction d'un mini et maxi variables

Bonsoir à tous,

je reviens vers vous dans le but de pouvoir trouver une formule mathématique afin d'obtenir le % d'une valeur en fonction d'une cellule maxi et d'une cellule mini.

je mets un fichier en exemple.

Ce que je souhaite, c'est obtenir le résultats de la colonne G. J'explique le calcul pour trouver la valeur de la cellule G14.

J'ai une valeur en B14 (5.0848)

Une valeur nominale en C14 (5.1000)

Un interval maxi en D14 (0.1000)

Un interval mini en E14 (-0.1000)

Une déviation en F14 (-0.0152)

En fait je peux avoir un maxi de 5.2000 (C14+D14) et un mini de 5.0000 (C14-E14) ce qui me donne une valeur moyenne de 5.1000. Je souhaite obtenir le pourcentage de déviation de la cellule B14 par rapport à la Moyenne de mes 2 résultats précédents. En gros, 5.1000 correspond à 0% , 5.2000 correspond à 100% et 5.0000 corespond à -100% . Actuellement à 5.0848, d'après mes calculs, je dois obtenir en G14 une valeur de -15%. Le but étant de rester entre 100% et -100%

L'explication n'est pas simple, j'espère être assez clair.

Merci d'avance

Bonjour,

Pas totalement clair... mais en suivant le fil :

=(B14-MOYENNE(C14+D14;C14+E14))/((D14-E14)/2)

Cordialement.

Bonsoir et merci pour la rapidité!! cela fonctionne super. En plus il faut me comprendre donc c'était pas gagné. lol

Du coup je me demande s'il n'est pas possible d'optimiser un peu..

en fait il y a 3 formules possibles. je m'explique,

La formule à appliquer dépends des colonnes D et E. La méthode que vous m'avez trouvé s'applique aux lignes 14,15,16 et 18 car les valeurs de D sont toutes positives et celles de E toutes négatives.

Par contre, pour les lignes 17 et 19 la colonne D est bien positive mais la E a des valeurs à 0 ce qui revient en fait à ce que la déviation parte de 0 et non de la valeur moyenne. La valeur de la cellule G17 devrait être de 34.8% et celle de la cellule G19 devrait etre de 0.75%

La troisième formule serait que des valeurs de la colonne D soit à 0 et que des valeurs de E soit négatives à ce moment la il faut toujours partir de 0 pour aller à un pourcentage négatif.

J'espère être encore à peu près clair.

Merci en tout cas

EDIT: Bien évidemment les lignes ne sont pas figées et 1 des 3 possibilités de calcul peut s'appliquer à n'importe quelle ligne

La valeur de la cellule G17 devrait être de 34.8% et celle de la cellule G19 devrait etre de 0.75%

Alors ça !

Reprends donc le fichier que tu as communiqué. Tu indiquais en G les valeurs à atteindre : et pour ces deux valeurs respectivement -30% et -99% !!!

Il faudrait donc te mettre d'accord avec toi-même...

Les valeurs que tu indiques s'obtiennent en considérant que l'évaluation de l'écart en % se fait dans l'intervalle max-0 ou 0-min selon le cas, et le 0 correspond à un écart nul.

C'est la première formule que j'ai spontanément émise !

Evidemment, pour ces deux valeurs je n'obtenais pas ton évaluation, j'ai donc analysé pour trouver la raison qui s'avérait être que si le max et le min n'étaient pas symétrique l'écart nul s'appréciait non pas sur 0 mais sur la valeur médiane entre max et min (valeur qui est naturellement 0 en cas de symétrie, et ne change donc pas les autres résultats).

J'ai donc modifié la première formule pour qu'elle donne ce résultat correspondant à ta demande !

Cordialement.

bonjour

salut MFerrand, j'adore ton humour

la question est d'abord une question de métrologie avant de devenir une question Excel

quel est l'objectif métrologique ?

conformité ?

capabilité ?

autre ?

Bonjour à tous,

Désolé MFerrand, je pensais que la formule serait complexe et que mes explications ne seraient pas très claires mais je vois qu'il y a des personnes expertes sur le sujet

Je reprends depuis le début, je me suis planté complètement.

L'idée de base était de partir sur la valeur moyenne définit par le mini/maxi SI la colonne C est différente de 0

SI C =0 alors l'écart se fera en partant de 0 et non de la valeur moyenne (ce cas s'applique aux lignes 17 et 19), à ce moment la, l'écart sera toujours positif et jamais négatif.

je peux aussi avoir une tolérance (0/-min ou 0/+max) avec une valeur en C différente de 0, à ce moment la, l'écart se fait bien en partant de la moyenne.

En fait, le fichier excel généré par ma machine de mesure n'est pas assez complet, je souhaite voir tout simplement ou se situe la valeur relevée par rapport à l'intervale de tolérance afin d'avoir un visuel plus rapide si une dérive est constatée.

C'est simplement de la conformité.

On reste dans l'interval de tolérance entre -100% / 0 / +100% la case passe VERT , on sort de cet intervale, la case passe ROUGE

Merci et désolé pour l'explication c'est pas facile

re

la pièce à mesurer est définie par une valeur nominale et surtout un mini et un maxi

pour que ton fichier soit adapté à TOUS les cas de figures, il ne faut tenir compte QUE du mini et du maxi. Car on cote parfois 24 +0 - 0,08

La cote "nominale" semble 124 mais en réalité elle est à 23,96 (c'est la moyenne de mini et maxi)

donc ton fichier doit contenir :

• mini selon le plan, à saisir au départ
• maxi selon le plan, à saisir au départ
• valeur lue, à saisir à chaque mesure

• moyenneselonplan = moyenne(mini;maxi)
• tolérance = max-moyenneselonplan (ou bien = moyenneselonplan-mini)
• écart = (valeur lue - moyenneselonplan) / tolérance

Re

Le fichier excel est généré automatiquement, les tolérances ainsi que les valeurs nominales sont rentrées au préalable.

Il y a 2 cas de figures, la valeur nominale est différente de 0 on applique l'écart à partir de la moyenne (ex : 12 +0.1/-0.03 moyenne à 12.035 )

Second cas de figure , la valeur nominale est à 0, on applique l'écart à partir de 0 ( ex : 0 avec un maxi à 0.2 et pas de mini ) . à 0, 0% d'écart, et à 0.2, 100% d'écart..

Je le vois comme ça

re

ceci est donc ta base de départ

pour la suite je t'ai indiqué la marche à suivre

s'il faut un peu transformer les cotes et tolérances des plans pour faire la saisie de mini et maxi, à toi de jouer (avec une calculette ! )

Bonjour....

Merci jmd de ton apport technique...

Il est sûr qu'on a la solution !!! Il suffit de savoir laquelle doit être appliquée !

Ce que l'on dégage par un calcul n'est rien d'autre qu'un indicateur :

• soit cet indicateur relève d'une norme, sa signification est prédéfinie, et l'interprétation qu'on pourra en faire en découle, et son mode de calcul est alors nécessairement prédéfini lui aussi ;
• soit il s'agit d'un indicateur personnalisé, destiné à offrir une appréciation complémentaire à son utilisateur, et à lui alors d'établir de façon claire signification de cet indicateur et son mode de calcul.

Ce qu'on cherche à évaluer c'est l'écart d'une mesure par rapport à un étalon et si cet écart est ou non admissible en fonctions de normes de tolérance prédéfinies. Je suppose que la norme de tolérance tient compte d'une part des incertitudes susceptibles d'affecter la mesure elle-même (liées à l'instrument de mesure) et d'autre part des écarts tolérables eut égard à l'usage ultérieur de l'objet de la mesure.

Le modèle montrait deux types d'écart dans les mesures : écarts susceptibles de se répartir de part et d'autre de la valeur cible de référence (mesure d'une longueur par exemple) et écarts toujours positifs (exemple : distance d'un point central).

J'ai trouvé normal que les écarts fixés en plus et en moins tolérable soient symétriques par rapport à 0 (en référence à la définition habituelle des incertitudes et à une distribution statistique Normale...), et pas de problème particulier si les écarts ne sont que dans un sens, on n'a qu'une moitié de la courbe, mais l'évaluation ne change pas de nature...

D'où la première formule (que je n'ai pas proposée pour les raisons précédemment indiquées) :

=(B14-C14)/D14

soit l'écart avec la cible (positif ou négatif) sur la norme de tolérance positive (valeur absolue de l'écart toléré, le sens de l'écart fourni par le numérateur de la division).

Et dans le cas où l'écart n'est que dans un sens, le résultat sera donc toujours positif...

Il faut noter que l'indicateur ainsi établi est un nombre sans dimension (ne référant pas à une unité de mesure), établissant une fourchette d'acceptabilité comprises entre 1 et -1 et évaluant à quel niveau l'on se situe dans cette fourchette.

Bien que je n'ai pas d'exemple immédiatement sous la main, on ne peut exclure la possibilité que la distribution des écarts soit dissymétrique, et que les écarts tolérables positivement ou négativement soient différents en valeur absolue...

Ce qui conduirait à modifier légèrement la formule précédente pour en tenir compte :

=(B14-C14)/SI(B14-C14<0;ABS(E14);D14)

Je dois dire que jusque là tout me paraît logique. L'écart nul réfère à la valeur cible attendue...

Mais cette formule ne fournissait pas le résultat supposé attendu dans les cas où il ne pouvait y avoir d'écart négatif. Parce qu'alors on faisait glisser la valeur cible vers la moyenne des valeurs maximale et minimale pouvant être acceptées. Dans le cas où des écarts positifs et négatifs de même valeur (absolue) définissent l'acceptabilité, le résultat ne change pas, la valeur cible demeure. Dans les autres cas, il y a un glissement...

Je me suis contenté de formuler le calcul pour parvenir à ce résultat (dont il reste à établir si c'est bien le résultat attendu).

Mais je ne m'avancerai pas sur le bien-fondé de l'opération ainsi menée, ni sur la signification que prend alors l'indicateur en déplaçant de fait la valeur cible...

Mais je suis intéressé à connaître des avis éclairés sur le fond de la question !

Bonne journée.

RE.

Alors je viens de vous relire et d'appliquer la formule donnée par MFerrand. Je crois être un peu largué.

J'ai 2 cas de figure:

1) Je cherche à interpréter ou je me situe dans l'intervale de tolérance. Les tolérance peuvent être positives ou négatives, et assymétriques. (ex : 4±0.1 ___ 6 +0.1/-0.02 ____ 9 -0.02/-0.08). Dans tous ces cas , la valeur moyenne doit être considérée comme valeur visée et ensuite, le maxi correspond à 100% et le mini à -100%

Dans le cas ou la tolérance se trouve décalé c'est à dire un maxi à +0.1 et un mini a -0.02, cela ne réparti pas les écarts correctement. J'ai fais des essais, la valeur nominale est toujours considérée.

J'ai fais des essais, dans le cas ou la valeur nominale se trouve être à 0, tout fonctionne.

Je crois avoir fourni (presque) toutes les formules... La seule question est que tu précises sur quelle base tu veux calculer !

la je suis perdu !! afin d'obtenir le pourcentage en tant qu'indicateur, je dois saisir les valeurs théoriques à chaque mesure ? avec l'intervale de tolérance ? si c'est le cas c'est ingérable..

Je pense que le mieux est de me faire un exemple concret , je verrai par la suite si les résultats correspondent en fonction des variations des intervales mini et maxi

merci de votre patience

champi87 a écrit :

RE.

Alors je viens de vous relire et d'appliquer la formule donnée par MFerrand. Je crois être un peu largué.

J'ai 2 cas de figure:

1) Je cherche à interpréter ou je me situe dans l'intervale de tolérance. Les tolérance peuvent être positives ou négatives, et assymétriques. (ex : 4±0.1 ___ 6 +0.1/-0.02 ____ 9 -0.02/-0.08). Dans tous ces cas , la valeur moyenne doit être considérée comme valeur visée et ensuite, le maxi correspond à 100% et le mini à -100%

Dans le cas ou la tolérance se trouve décalé c'est à dire un maxi à +0.1 et un mini a -0.02, cela ne réparti pas les écarts correctement. J'ai fais des essais, la valeur nominale est toujours considérée.

J'ai fais des essais, dans le cas ou la valeur nominale se trouve être à 0, tout fonctionne.

je pense que tu fais erreur

en cas de répartition asymétrique des tolérances, la cote nominale n'est pas la cote qu'on doit "viser" lors de la fabrication, il faut recalculer la cote milieu et viser ce milieu

d'où mon calcul ci-dessus

cotevisée = moyenne(maxi;mini)

note que cette formule peut être appliquée pour les toélrances symétriques, donc tu peux l'appliquer en standard et te sortir de tous les cas de figure

ensuite tu recalcules la tolérance = maxi - cotevisée

et tout redevient facile pour calculer tes %

Bonjour,

Salut jmd !

Tu es donc partisan de la première formule que j'ai donnée au début !

Je dois dire que je l'ai faite uniquement pour arriver au résultat mentionné !

Car je ne trouve pas ça très logique... Si les écarts tolérés sont dissymétriques, c'est que la distribution est asymétrique, et que donc la valeur moyenne des mesures doit bien tomber sur la valeur nominale voulue

D'où ma dernière formule me paraît nettement plus rationnelle en produisant un résultat dont la signification ne prête pas à confusion.

Cordialement.

salut MFerrand

heu... la distribution n'est pas asymétrique, elle est inconnue. Il faut une dizaine de mesures réelles pour l'évaluer (moyenne, écart-type).

la valeur nominale est celle du dessinateur qui conçoit d'abord (cotes "absolues") puis ajoute les tolérances pour que :

• ça fonctionne
• ce soit usinable (ou moulable...)

ex : il dessine un pion diamètre 5mm et un perçage diamètre 5mm

pour que le premier s'insère dans le second, il faut du jeu

pion : 5 +0 - 0,05

perçage : 5 + 0,05 -0

(c'est exagéré pour mieux expliquer. En réalité c'est même normalisé)

Re,

Ton exemple (en fin) définit en fait une norme ! Pour qu'un axe s'insère correctement dans un orifice il doit y avoir une différence du résultat à cibler calculable...

Je suis bien d'accord que la distribution de tes mesures est inconnue, et que tu n'en auras une approche qui commence à être précise qu'à partir de 30... Mais c'est en amont que des normes ont été fixées, et l'on n'a aucune raison d'avoir fixé des normes de tolérance asymétriques si l'on n'a pas préalablement établi que la distribution était asymétrique ! L'on n'a d'ailleurs pas possibilité de fixer des normes avant d'avoir étudié les conditions dans lesquelles le produit obtenu demeurera fonctionnel et répondra au niveau de qualité souhaité...

Au stade des mesures de contrôle, on ne fait que vérifier l'adéquation à un modèle qui a été préalablement défini !

Bonjour messieurs,

Je travaille dans un service contrôle avec des opérateurs qui usinent des pièces. Le but du résultat étant d'informer l'opérateur d'une éventuelle dérive de sa machine etc...

Il faut penser au fonctionnel, un axe rentre dans un alésage que si la tolérance de celle-ci est en adéquation avec celle de l'alésage.

Il y a des côtes nominales 5 (0/+0.02) et des côtes moyennes à viser, donc 5.01. Dans tous les cas de figures d'une valeur nominale différente de 0 , la valeur visée est la valeur moyenne, c'est celle qui doit être la référence dans notre cas.

J'ai donc repris ton exemple Mferrand et recalculer ce que je dois obtenir !

Dans le cas n°1 , 15 ±0.1, les indices sont bons, la tolérance est symétrique

Dans le cas n°2 , 15 (+0.1/-0.2) je dois obtenir un indice de 66.66% pour la mesure à 15.05 et un indice de 0 pour la 14.95 car il se trouve que c'est la côte moyenne

Dans le cas n°3 , 15 (0/+0.1) je dois encore avoir un indice à 0 car il faut considérer 15.05 (cote moyenne)

Il est bien d'accord que si je fais varier manuellement mes tolérances sur le fichier excel pour faire des essais, les écarts doivent se réajuster tout seul ( je vérifie comme ça en fait )

Et dans le cas ou 0 est une valeur nominale avec une tolérance maxi à +0.2, l'écart ne se fais pas de la moyenne mais bien à partir du 0 car on doit rester proche du 0, c'est le seul cas de figure.

Merci

Peut-être y a-t-il un problème d'interprétation niveau langage ! Je considérais jusqu'à présent que la valeur nominale était la valeur cible ! Cela ne l'est que si les marges de tolérance sont symétriques, si je comprends bien, donc la cible est toujours la valeur moyenne entre les maxi et mini tolérés !

La valeur nominale n'est donc qu'une sorte d'étiquette qui est sans incidence... Il serait mieux de faire apparaître systématiquement la valeur cible.

Ce qui me surprend c'est que l'on donne des bornes de tolérances dissymétriques, qui deviennent en fait symétriques par rapport à la valeur cible et on se retrouve donc tout à fait dans une distribution statistiques Normale... Ce qui est logique s'agissant de production industrielle (c'est dans d'autres domaines qu'on trouvera des distributions type Poisson...), mais je trouve qu'on fait un détour pour y parvenir...

Tu reviens donc là en plein accord avec jmd, il me semble, et donc à la première formule fournie, à amender pour le cas où la cible est 0 et où l'on ne rencontre pas d'écart négatif.

En vite fait :

=(B14-SI(C14<>0;MOYENNE(C14+D14;C14+E14);C14))/SI(C14<>0;(D14-E14)/2;D14)

(à voir si on peut la simplifier...)

Cordialement.

On peut l'écrire ainsi :

=SI(C14<>0;(B14-MOYENNE(C14+D14;C14+E14))/((D14-E14)/2);B14/D14)

ce qui raccourcit très légèrement...