Formule MAX sous forme matricielle incluant plusieurs variables

[ador_matt]

Bonjour à tous!

Je fais une fois de plus appel à votre expertise et vos conseils avisés!
Je m'attaque à une formule matricielle que je ne maîtrise pas vraiment... J'ai bien fait un essai avant de solliciter votre aide (Cellule K6 et K15) sans grand résultat...

Voilà ce que je cherche à faire:

Appliquer un coefficient de -25% sur un Max tout en prenant en compte plusieurs exceptions/variables.

Voici ci-dessous et en fichier ci-joint un cas explicatif :

CAS idéal :

Le pays concerné est celui qui a le plus fort potentiel client, a les meilleures ventes au prix MAX tout en dégageant une marge minimum de 15%.

Variables identifiées à prendre en compte :

• Variable N°1 : Volumes
Le prix MAX n’a pas un poids en volume significatif.
Critère à prendre en compte : Poids situé entre 15% et Max 80%*(Au-delà de 80%, un coefficient de +15% devra s’appliquer par défaut au marché concerné).

• Variable N°2 Prix MIN et MARGE:
Le pays ayant le plus fort potentiel clients est aussi celui ayant le prix le plus bas.
 Appliquer un coef réduit de +15% sur ce prix (Prix MIN) en prenant garde que la MARGE soit à minima 15%

J’espère avoir été suffisamment explicite, n'hésitez pas à me demander plus de précisions!

Merci!

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonjour,
L'explication est claire mais je ne saisis pas ce que tu veux afficher. Un prix par ligne ? un seul prix pour le pavé ? quel résultat doit s'afficher ?
J'ai corrigé ta formule matricielle mais j'obtiens le même résultat de 12.38
Je pense qu'il manque un élément dans la formule mais il faudrait que tu précises ce qu'on recherche
voir pj

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonjour et avant tout merci pour ta réponse!

Je cherche à obtenir un seul prix pour le pavé.

La logique derrière cela, c'est la mise en place d'un "price corridor" d'une amplitude de 25% par défaut qui se réduit à 15% lorsque le pays concerné pèse lourd en volumes.

J'espere que c'est un peu plus clair pour toi...merci pour ton aide!

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

et quel est le bon résultat pour tes deux exemples ?

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Dans l'exemple 1, le résultat devrait être 10,35 EUR soit Prix Mini*1.15 car le pays est important en volumes..

Après avoir revu la formule, il faudrait l'adapter ainsi =IF((G5:G8>50%)*(I5:I8>25%)*(F5:F8>15%);MIN(E5:E8)*1,15;MAX(E5:E8)*0,85)

et ensuite l'imbriquer avec les autres conditions et terminer par le "cas idéal" en valeur si faux soit: IF((G27:G30>15%)*(G27:G30<80%)*(I27:I30<=50%)*(F27:F30>15%);MAX(E27:E30)*0,75

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Désolé, je n'ai plus le temps aujourd'hui, mais je pense qu'il ne faut pas faire le si de cette façon et que le Min ou max doit se faire aprés

du genre : max(si(condition1*condition2*condition3; plage;0)*.85

je regarde ça lundi

Edit : j'ai fait ceci lundi matin, mais manifestement j'ai du oublier de le poster !!

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

ok, merci et bon w.end!

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonsoir,

Après quelques tentatives, je croyais toucher au but et obtenir le bon résultat, mais je me rends compte que dans mon exemple ci-joint mon résultat (en K24) n'évolue pas supprime les volumes de l'Italie...en revanche si le poids de ces volumes depasse un certain seuil alors la formule ne fonctionne plus et j'obtiens 14,03EUR.

Quelque chose m'échappe!

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonjour,
Je pense qu'il y a deux problemes :
1) les conditions à appliquer : j'ai essayé de faire un tableau des conditions et il y a des cas non traités
2) la façon d'écrire la formule : avec MIN ou Max (e26:e29) tu ne prend pas forcément le prix du pays considéré

j'ai refait les formules des 3 scénarii en enlevant les parties inutiles et les max,min
la compilation ne marche pas pour l'instant car il faut bien traiter les cas dans l'ordre

Pourrais tu faire 3 tableaux avec les 3 scénarii et noter à la main le prix à obtenir ?

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonjour et merci pour cette réponse matinale!

Voici ci-joint quelques exemples et cas en onglet 2 qui permettrons j'imagine de déterminer les conditions à appliquer.

La correction de la formule du scénario 1 en utilisant un MIN(IF) me semble être une bonne solution, reste maintenant à l’imbriquer avec d'autres conditions pour prendre en compte notamment un scénario où le prix MIN et MAX ne serait pas significatifs en volumes/poids.

En prenant les choses dans l'ordre nous devrions y arriver !

Merci!

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Tu vas me trouver lourdingue, mais à quoi sert l'exemple 2 ? on doit appliquer le même taux quand la marge est > ou < à 15 %
de même pour exemple 3 : on donne le même résultat et le prix min pèse plus de 10% des volumes
pourrais-tu tenter de faire un tableau comme dans mon fichier du post 9 ?
autre interrogation : quel prix prendre en compte quand les conditions ne sont pas respectées ?

Pour l'instant, je voudrais arriver à comprendre quels critères font choisir quel prix du tableau, ensuite on fera des Si pour appliquer le coefficient. Autrement dit : prend-on toujours le prix min auquel on applique des coefficients différents selon volume, marché et marge ou bien y a t il un autre critère de choix du prix avant d'appliquer un coefficient ?

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Pas de problème, j'essaie d'être explicite mais c'est loin d'être évident!

Je viens de remettre à plat l'ensemble de mes critères, je t'ai résumé le tout dans le fichier ci-joint sous la forme que tu as employé dans ton poste #9.
J'ai enlevé le critère (potentiel panier clients) que je renonce à prendre en compte.

J’espère que ça sera assez clair pour toi, si besoin je peux t'ajouter des exemple pour chaque cas.

Merci pour ta patience et de ton aide!

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Merci !
On y est presque je pense.
Une dernière précision parce que ça me semble bizarre : quand tu écris prix max tu veux bien dire le prix max entre tous les pays ? On pourrait avoir un pays qui est > 30% de poids mais qui n'a pas le prix max on va utiliser les prix max d'un autre pays ? Jusque là je pensais qu'il fallait récupérer le prix du pays qui correspond aux critères (à cause de tes premières formules)
Si vraiment on travaille à partir du prix max tous pays et qu'on change le coef pour chaque pays en fonction de son poids, de son panier moyen et de sa marge ça devient beaucoup plus facile, mais on a un prix différent pour chaque pays (dont on pourrait aficher ailleur le min et le max pour avoir un "corridor de prix")
Cordialement

 

[Dugenou]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Voici en pj un essai d'application pays par pays de ton récapitulatif des conditions.

J'ai considéré que dans la colonne critère prix il fallait comprendre : quand le prix du pays est le prix max (/ min) et que dans le cas 4 quand tu écris

PRIX MAX non significatif < 10% / Prix min signi >10%

il fallait comprendre que les 10% concernent le poids.

J'ai donc construit une formule qu'on applique à chaque pays puis on regarde le max et le min de chaque pavé.

Si c'est bien comme cela que tu veux fonctionner on verra ensuite à réduire le nb de cellules intermédiaires
Cordialement

 

[ador_matt]

Re : Formule MAX sous forme matricielle incluant plusieurs variables

Bonjour!

C'est tout à fait ça, il s'agit du prix Max entre les pays.
En revanche, je préfère que nous laissions de côté l'aspect panier client et que nous prenions en compte la marge en critère.

Je te remets le fichier en PJ avec les exemples pour chaque cas et les prix que nous devrions trouver avec la formule magique.

Merci!