Formule EXCEL : Calcul pourcentage Résolu/Fermé

Question

Bonjour, 

Je suis en train de créer un tableau de suivi et j'aurais besoin d'aide !
Dans mon tableau, j'ai une colonne Commissions et une colonne Pourcentage.
Je recherche une formule à mettre dans ma colonne Pourcentage qui dit que :
Si Cellule Commissions est comprise entre 0 et 2000 € alors la cellule commission sera multipliée par 10%
Si Cellule Commissions est comprise entre 2000 et 4000 € alors la cellule commission sera multipliée par 15%
Si Cellule Commissions est supérieure à 4000 € alors la cellule commission sera multipliée par 20%.
Il faut que la formule puisse tenir compte du fait que si dans la cellule commission par exemple, le montant est de 2500 €, le pourcentage est de 10% jusqu'à 2000 € et de 15% sur les 500 € restants !

Ci dessous l'exemple de ce que je souhaiterais mettre en place :

http://www.cijoint.fr/cjlink.php?file=cj201104/cijkxZr6gn.xls

Je vous remercie par avance pour l'aide que vous pourrez m'apporter !

Configuration: Windows 7 / Firefox 3.6.16

Vaucluse · Answer

Bonsoir
=SI(Cell<2000;Cell*10%;SI(Cell<4000;200+(Cell-2000)*15%;500+(Cell-4000)*20%))

crdlmnt

babs44 · Answer

Bonjour Vaucluse !

Décidément, je ne vais plus pouvoir me passer de tes services !!!
J'ai donc mis en place ta formule et cela fonctionne ! Par contre, je vais encore corser la tâche... :(

Ci joint toutes les explications :
http://www.cijoint.fr/cjlink.php?file=cj201104/cijgmHSAoO.xls

En tout cas, merci bcp pour ton aide ainsi que celle des autres bien sûr !!

Raymond PENTIER · Answer

Bonjour à vous deux.

Le principe même du tableau me semble illogique :
Dans les cellules E2 à E6 et E8 à E9 les calculs se font à partir de la colonne B.
Curieusement dans les cellules E7 et E10 ils se font à partir de B et C !
D'autre part pourquoi calculer des résultats pour chaque ligne, alors qu'il suffirait d'appliquer la formule de Vaucluse à la colonne C pour avoir directement le résultat total ?
Enfin les questions de Vaucluse nécessitent des réponses précises et détaillées ...

Cordialement.

babs44 · Answer

Bonjour,

Tout d'abord, je vous remercie de prendre le temps de me répondre !

Je vais essayer d'être plus précise !

Comment se calcule A? C'est le montant TTC de la commission (montant qui est rentré manuellement en fonction de chaque dossier)

quel est le rapport entre A et C? C est le cumul de toutes les commissions au fur et à mesure. C est calculé sur la base de B qui est le montant HT !

pourquoi 4000-C alors que la logique voudrait C -4000
à quoi correspond ce fameux résultat qui est un pourcentage sur une commission? la colonne E donne le montant calculé sur la base suivante :
- Si C est compris entre 0 et 2000 alors B*10%
- Si C est compris entre 2001 et 4000 alors B*15%
- Si C est > à 4000 alors B*20%

Sachant que dans certains cas (par ex C7), le montant jusqu'à 2000 € doit être calculé sur la base de 10% : 2000-C6 (on obtient ainsi la part jusqu'à 2000€ pour le calcul des 10%) + Le calcul du montant à multiplier par 15% : C7-(2000-C6) 
Ainsi, on fait bien la distinction entre le montant à multiplier par 10% et celui à multiplier par 15% ! Idem pour le passage du 15 au 20% !



Voilà, j'espère avoir été un peu plus précise dans mes explications !

Merci bcp par avance !

Raymond PENTIER · Answer

Salut.

Eh bien non ! Il est faux de dire : 
Sachant que dans certains cas (par ex C7), le montant jusqu'à 2000 € doit être calculé sur la base de 10% : 2000-C6 (on obtient ainsi la part jusqu'à 2000€ pour le calcul des 10%) + Le calcul du montant à multiplier par 15% : C7-(2000-C6).

En appliquant tes consignes 
- Si C est compris entre 0 et 2000 alors B*10%
 - Si C est compris entre 2001 et 4000 alors B*15%
 - Si C est > à 4000 alors B*20%
ce serait plutôt :
Sachant que dans certains cas (par ex C7), le montant jusqu'à 2000€ doit être calculé sur la base de 10% : 2000€*10%= 200€ + Le calcul du montant à multiplier par 15% : C7-2000.

Tu n'es pas d'accord ?

babs44 · Answer

Bonjour Raymond,

J'ai testé ta formule, mais ça ne fonctionne pas :
Si on prend l'exemple de C7 :
Le montant de la commission est de 540,13 € HT
Sachant qu'avec cette commission, le cumul est de 2421,20 €
Il faut donc calculer la part des 540,13 € qui sera multipliée par 10% et la part des 540,13 € qui sera multipliée par 15%.
Sachant qu'il manque 118,93 € pour aller jusqu'à la somme de 2000 € (2000-C6) C'est donc 118,93 € qui seront multipliés par 10% et le reste (540,13 €-118,93 €) qui sera multiplié par 15%. Donc (B7-(2000-C6))

Mais j'avoue que j'en suis à un point où je m'embrouille un peu !!

Je continue à tester et à chercher ! En attendant de vous lire, merci encore pour votre aide !

Raymond PENTIER · Answer

De toute ma vie je n'ai jamais vu de consignes de calcul aussi fantaisistes ! 

Pourrais-tu retrouver et nous envoyer le document ORIGINAL (note de service, convention collective, prescriptions techniques ...) qui définit ce mode de calcul ? 

J'ai l'impression que quelqu'un en a fait une mauvaise interprétation !

Très amicalement.

babs44 · Answer

Hihihi !! Je suis bien d'accord avec toi !!

Ci dessous le seul document original que j'ai en ma possession !!! Je te l'accorde, c'est très léger !!! Mais c'est la seule chose que l'on m'a donné pour réaliser ce tableau de suivi !!! ;)

http://www.cijoint.fr/cjlink.php?file=cj201104/cijv5ZFOJe.xlsx

J'espère néanmoins que tu pourras m'aider à résoudre ce casse tête !!

Amicalement !

Raymond PENTIER · Answer

Je regrette, mais il m'est intellectuellement impossible de résoudre un problème dont je n'ai pas compris la pertinence ni la logique.
C'est comme demander à un électricien de faire un branchement dans un appartement dont l'installation ne respecte plus aucune norme de sécurité.
Tu devrais interpeler celui qui t'a demandé de faire ce travail.

Vaucluse · Answer

Bonjour
au vu de votre dernier fichier essayez cette formule à placer en M5 et à tirer sur la hauter 
M4 n'ayant pas d'antécédents, ne pas changer la formule actuelle ou alors donner des précisions sur la règle.

=SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*10%+(J5-2000)*15%;I5*15%))

Nota:
cette formule renvoi les laveurs affichées actuellement en colonne M, ais elle ne correspondent pas à l'échelle de votre premier message , à savoir:
que deviennent les 20% au dessus de >4000 alors que votre tableau évoque à l'origine directement  M12: I5*15%

revenez si besoin de complément avec des infos sur ce dernier point, oun alors compléter la formule ci dessus d'une condition supplémentaire.

A vous loire

crdlmnt

Vaucluse · Answer

Bonjour
au vu de votre dernier fichier essayez cette formule à placer en M5 et à tirer sur la hauter 
M4 n'ayant pas d'antécédents, ne pas changer la formule actuelle ou alors donner des précisions sur la règle.

=SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*10%+(J5-2000)*15%;I5*15%))

Nota:
cette formule renvoi les laveurs affichées actuellement en colonne M, ais elle ne correspondent pas à l'échelle de votre premier message , à savoir:
que deviennent les 20% au dessus de >4000 alors que votre tableau évoque à l'origine directement  M12: I5*15%

revenez si besoin de complément avec des infos sur ce dernier point, oun alors compléter la formule ci dessus d'une condition supplémentaire.

A vous loire

crdlmnt

babs44 · Answer

Bonjour Vaucluse !!!!

J'ai bien l'impression que tu as trouvé la solution !!!! Effectivement, tu as raisons, les 20% n'ont pas été calculés ! Il faut cependant les rajouter !
J'ai donc repris ta formule en y rajoutant le calcul des 20%, mais ma logique ne doit pas être au top ce matin, car ça ne fonctionne pas !!

=SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*10%+(J5-2000)*15%;I5*15%);SI(J5>4000;(4000-J4)*15%+(J5-4000)*20%))

Si tu peux juste y jeter un coup d'oeil ce serait super ! Sinon, t'inquiète pas, tu m'as déjà bcp aidé !!!

Merci encore !

Amicalement.

babs44 · Answer

Oups, j'ai oublié de coller la fin de ma formule :

SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*10%+(J5-2000)*15%;I5*15%);SI(J5>4000;(4000-J4)*15%+(J5-4000)*20%;I5*20%))

Vaucluse · Answer

Re babs Peut être ai je trouvé la formule, mais je pense qu'elle reste quelque part illogique autant que la votre dans le dernier fichier transmis Je tente de m'expliquer avec une exemple. Il s'agit du cas où deux valeurs successives se trouvent dans la même fourchette en J2 1800 en J3 2100 en J4 2400 la calcul en J3: =(2000-J2)*(10%+(J3-2000)*15% est dans la logique et donne 35 par contre, le calcul de J4 =(2000-J3)*10%+(J4-2000)*15% enléve 10% sur 2000-J3, soit 10 et rajoute 15% sur J3-2000, soit 60 dans ce cas, le calcul 2000-J2 donne -10 alors que le même =écart de 100 avec 2000 a donné 100*15% à la ligne précédente. Ne faudrait il pas calculer J4 en enlevant tout ce dont J3 à bénéficié au dessus de 2000 , soit 100*15%;plutôt que d'en enlever que 10% D'accord, ce n'est pas plus clair que vos explications, on fait ce qu'on peut :-) Dans tous les cas 1° votre formule à priori ne correspond à rien (excusez moi!) et je verrai: si mon souci est à négliger =SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*10%+(J5-2000)*15%;(4000-J4)*15%+(J5-4000)*20%)) je ne vois pas ce que font les I5*15% en fin de formule?Il n'existe pas dans votre dernier fichier.? expliquez vous sur le sujet il n'est pas utile de recréer une condition >4000, car par défaut, c'est la seule qui va rester si les deux autres conditions ne sont pas remplies si par contre mon souci est justifié, c'est à peine plus compliqué: =SI(J5<2000;I5*10%;SI(J5<4000;(2000-J4)*SI(J4>2000;15%;10%)+(J5-2000)*15%;(4000-J4)*SI(J4>4000;20%;15%)+(J5-4000)*20%)) bon courage avec tout ça Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?

babs44 · Answer

OH MY GOD ! Vous êtes un génie VAUCLUSE !!!!!!!!!! La dernière formule fonctionne à merveille !!!

J'avoue que je n'ai pas saisi toutes vos explications, mais le principal est que je vais enfin pouvoir consacrer ma matière grise (euh, la vôtre !!) à autre chose !!!!!!!!!!!

Mille mercis, vous ne savez pas à quel point ma semaine se finit en beauté grâce à vous !!!!!!!!

Très, très amicalement !!

Formule EXCEL : Calcul pourcentage

15 réponses

Discussions similaires