je ne trouve pas la formule exact

[paul45260]

bonjour a tous
je suis nouveau sur le forum
désole si je fait des erreurs sur mon premier message
je cherche une formule pour calculer mes commissions
elle se compose en plusieurs tranche ex:
tranche 1 :si CA <ou =à 20000€ commission= CA*7%
tranche 2 :si CA > ou = 20000€ et< 30000€
commission si CA< 30000€= CA-20001€*9%
commission si CA >30000€ = 30000€ -20001€ *9%
tranche 3:
si CA > ou = 30000€ et< 40000€
commission si CA< 40000€= CA-30001€*11%
commission si CA >40000€ = 40000€ -30001€ *11%
tranche 4:
si CA > ou = 40000€ et< 50000€
commission si CA< 50000€= CA-40001€*13%
commission si CA >50000€ = 50000€ -40001€ *13%
tranche 5:
si CA > 50000€ =CA - 50001€*15%
merci pour vos réponses
a bientôt

 

[mapomme]

Re : je ne trouve pas la formule exact

Bonsoir paul45260 et bienvenu

Une tentative avec une formule matricielle. La formule matricielle est dans la cellule H7.

Elle utilise un tableau qui décrit les différentes tranches et les taux à appliquer à chaque tranche. Ce tableau est en A2:C6.

Le CA est à saisir dans la cellule E1. Cette cellule a été nommée CAFF.

En E2:F6 (et uniquement pour l'exemple) se trouve une décomposition du calcul pour aboutir au résultat en F7.

La formule matricielle en H7 n'a pas besoin de cette décomposition en E2:F7. Elle se suffit à elle-même.

La formule matricielle en H7:

=SOMME(C2:C6 * SI(CAFF>A2:A6;SI(CAFF-A2:A6<=B2:B6-A2:A6;CAFF-A2:A6;B2:B6-A2:A6);0))

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.
(Sur Mac, la touche Ctrl pourrait être nommée Ctrl ou CMD ou Pomme, me semble-t-il)

NB: il vaut mieux indiquer un titre plus parlant que celui choisi du type Calcul d'une commission par tranche du chiffre d'affaire (par exemple). Cela incitera davantage les forumeurs à s'interesser à la question.

 

[Misange]

Re : je ne trouve pas la formule exact

Bonjour
A tester :
avec le CA en A1 :
=SI(A1<=20000;A1*0.07;SI(A1<=30000;700+(A1-20001)*0.09;SI(A1<=40000;1600+(A1-30001)*0.11;SI(A1<=50000;2700+(A1-40001)*0.13;4000+(A1-50001)*0.15))))

 

[paul45260]

Re : je ne trouve pas la formule exact

merci mapomme sa marche très bien
je n'y arrivais pas tout seul
encore merci super se forum

 

[MichD]

Re : je ne trouve pas la formule exact

Bonjour,

Une façon de faire :

Suppose que tu as les chiffres d'affaires en C1,

Tu peux utiliser cette formule dans une autre cellule pour calculer la commission :

=CHOISIR(EQUIV(RECHERCHEV(C1;{0;20000;30000,40000;50000;1000000};1;VRAI);{0;20000;30000,40000;50000;1000000};0);C1*0,07;C1*0,09;C1*0,011)

Cette section de la formule C1*0,07;C1*0,09;C1*0,011 est incomplète.
C1*0.07 représente la tranche entre 0 et 20000
C1*0,09 représente la tranche entre 20000 et 30000
C1*0,011 représente la tranche entre 30000 et 40000

Comme je n'ai pas vraiment comment tu dois calculer le truc, remplace les petites formules de cette section
par un bout de formule qui représente pour chaque catégorie la méthode pour calculer la commission. Chaque
petit calcul pour chaque tranche est séparé par un point-virgule.

Le 1000000 représente un chiffre d'affaires impossible à atteindre par un vendeur! Tu en choisis un qui convient.

 

[paul45260]

Re : je ne trouve pas la formule exact

Bonjour michd
Je suis pas très doué avec les formules peut tu envoye un tableau merci d'avance

 

[MichD]

Re : je ne trouve pas la formule exact

Ce n'est pas un tableau, en regardant de plus près comment tu calcules les commissions,
la formule est celle-ci. Tu saisis le tout dans une cellule et tu inscris le montant du chiffre d'affaire en C1


=CHOISIR(EQUIV(RECHERCHEV(C1;{0;20000;30000;40000;50000;1000000};1;VRAI);{0;20000;30000;40000;50000;1000000};0);((20000-C1)*0,07);(20000*0,07)+((C1-20000)*0,09);(20000*0,07)+(10000*0,09)+((C1-30000)*0,11);(20000*0,07)+(10000*0,09)+(10000*0,11)+((C1-40000)*0,13);(20000*0,07)+(10000*0,09)+(10000*0,11)+(10000*0,13)+((C1-50000)*0,15))

Cela se voulait une autre manière d'effectuer le calcul sans faire référence à un tableau dans Excel.

Je pensais que la manière que tu calculais les commissions étaient plus simple, cela aurait simplifier la formule!!!

;-)

 

[Misange]

Re : je ne trouve pas la formule exact

Elle ne convient pas ma formule ? Elle me semble plus simple pourtant et répondre à la question en tenant compte des tranches.

 

[MichD]

Re : je ne trouve pas la formule exact

Le demandeur voulait une grosse formule pour sa grosse application...
Ma formule est toute désignée. ;-)))

Relis ta formule, tu as inscrit à une occasion 4000, je ne l'ai pas testée,
mais es-tu sûr qu'il ne manque pas un zéro?

 

[Misange]

Re : je ne trouve pas la formule exact

hello
non je ne pense pas qu'il manque un 0 là mais en revanche il y a quand même une erreur

ca fonctionne par tranche
tant que tu es en dessous du premier seuil, de 20000 tu payes
CA*0.07

Quand tu es au dessus de 20000 tu payes
7% de 20000 soit 1400€ (et non pas 700 comme j'avais mis !)
+ 9% du restant soit 9% de (CA-20000)
soit 1400 +(ca-20000)*0.09


Quand tu es au dessus de 30000 tu payes :
7% sur 20000 = 1400
+9% sur (30000-20000) = 900
11% sur (CA-30000)
soit 2300+(ca-30000)*0.11

Au dessus de 40000
7% sur 20000 = 1400
+9% sur (30000-20000) = 900
+11% de (40000-30000) = 1100
13% de (ca-40000)*0.11
soit 3400 +(ca-40000)*0.13

Au dessus de 50000
7% sur 20000 = 1400
+9% sur (30000-20000) = 900
+11% de (40000-30000) = 1100
13% de (50000-40000)=1500
15% de (CA-50000)
soit 4900+(ca-50000)*0.15

donc
avec le CA en A1 :
=SI(A1<=20000;A1*0.07;SI(A1<=30000;1400+(A1-20000)*0.09;SI(A1<=40000;2300+(A1-30000)*0.11;SI(A1<=50000;3400+(A1-40000)*0.13;4900+(A1-50000)*0.15))))

et en fait les bornes 2001, 30001.. n'ont pas lieu d'être c'est bien 20000, 30000... qu'il faut mettre

 

[MichD]

Re : je ne trouve pas la formule exact

Bonjour à nouveau Misange,

Teste ta formule avec 51,000 en A1.
Le résultat de la formule : 5050 et le résultat attendu : 4850

Si tu permets, j'ai modifié ta formule très légèrement et maintenant cela fonctionne très bien!
L'original utilisait : 4900+(A1-50000)*0,15 en fait c'est 4700, probablement une erreur de frappe.


=SI(A1<=20000;A1*0,07;SI(A1<=30000;1400+(A1-20000)*0,09;SI(A1<=40000;2300+(A1-30000)*0,11;SI(A1<=50000;3400+(A1-40000)*0,13;4700+(A1-50000)*0,15))))

Mais, elle ne peut pas battre ma grosse formule, elle a moins de 2 lignes...
;-)))


J'en profite pour corriger une petite erreur :

=CHOISIR(EQUIV(RECHERCHEV(C1;{0;20000;30000;40000;50000;1000000};1;VRAI);{0;20000;30000;40000;50000;1000000};0);(C1*0,07);(20000*0,07)+((C1-20000)*0,09);(20000*0,07)+(10000*0,09)+((C1-30000)*0,11);(20000*0,07)+(10000*0,09)+(10000*0,11 )+((C1-40000)*0,13);(20000*0,07)+(10000*0,09)+(10000*0,11 )+(10000*0,13)+((C1-50000)*0,15))

On pourrait la réduire à ceci :

=CHOISIR(EQUIV(RECHERCHEV(C1;{0;20000;30000;40000;50000;1000000};1;VRAI);{0;20000;30000;40000;50000;1000000};0);(C1*0,07);1400+((C1-20000)*0,09);2300+((C1-30000)*0,11);3400+((C1-40000)*0,13);4700+((C1-50000)*0,15))

 

[mapomme]

Re : je ne trouve pas la formule exact

Bonsoir à tous,

Une adaptation de la formule du msg #2 sans le tableau intermédiaire. Formule à valider normalrment avec la touche 'Entrée':

=SOMMEPROD({0,07;0,09;0,11;0,13;0,15}*SI(CAFF>{0;20000;30000;40000;50000};SI(CAFF-{0;20000;30000;40000;50000}<={20000;30000;40000;50000;10000000}-{0;20000;30000;40000;50000};CAFF-{0;20000;30000;40000;50000};{20000;30000;40000;50000;10000000}-{0;20000;30000;40000;50000});0))