calcul par tranche excel Fermé

Question

Bonjour à toutes les bonnes âmes, 

J'ai beau chercher dans les forums, me triturer le cerveau je n'y arrive pas! Mon problème : 

c'est un calcul de commissionnement par tranche : 
de 0 à 21000 = 8% si 21000 est atteint 
entre 21000 à 25500 (c'est à dire 4500 max)= 12% 
entre 25500 et 33000 (c'est à dire 7500 max) = 20% 
entre 33000 et 41000 (c'est à dire 8 000 max)= 23% 
supérieur à 41000 = 25% 

mon problème : 
1 - Je dois voir mes résultats, ligne par ligne, tranche par tranche ex : si ma première vente est de 4000 et que le total des ventes est > 21000 je dois voir 320 (8% de 4000) sur ma ligne. 
2 - Si  j'arrive a 20000 et que j'ai une vente a 3000 je dois pouvoir lire sur cette ligne les commissions par tranche soit 80 (8% de 1000) et 240 (12% de 2000).. 

Voilà, j'espère que j'ai été a peu près clair!!!  

Merci d'avance  

Configuration: Windows 7 / Firefox 4.0.1

Vaucluse · Answer

Bonjour

"Voilà, j'espère que j'ai été a peu près clair!!!"

heu.... ? 

si vous avez un  fichier avec un modèle de ce que vous voulez faire, placez le sur:
http://www.cijoint.fr et revenez ici placer le lien  donné par le site.

à vous lire
crdlmnt

Vaucluse · Answer

RE 
voila ce que j'ai compris de votre problème, je ne garantis pas que cela soit complètement conforme, mais on retrouve les résultats souhaités dans la ligne exemple 
http://www.cijoint.fr/cjlink.php?file=cj201106/cijjogzUr2.xlsx 

nota: 
1°:colonne 8% 
Par exemple en cellule U10,lorsque le résultat en T10 est plus grand que 21000 et en T9 aussi, le calcul (21000-T3)*8% donne un résultat négatif. 
Ne sachant pas ce qu'il faut calculer dans ce cas, j'ai placé dans la formule une condition qui fait que  le montant précédent en T est supérieur à 21000, la ligne suivante en U renvoie le dernier montant positif calculable obtenu dans la colonne U (85 sur l'exemple) 
Bien sur on peut modifier pour obtenir ce que l'on veut, mais là, il faudra des infos, mais ce raisonnement m'a paru logique au vu des constantes qui se maintiennent dans les colonnes suivantes si les plafond sont dépassés??! 


si la première ligne (3) contient un montant supérieur à 21000, elle va renvoyer #VALEUR dans la colonne 8% car la formule fait référence à la ligne précédente où se trouve les titres. 
S'il y a risque, il faut: 
soit laisser une ligne vierge sous les titres 

http://www.cijoint.fr/cjlink.php?file=cj201106/cijjogzUr2.xlsx 

Revenez si besoin d'ajustement, et aussi si je n'ai rien compris:-)) 

bonne soirée 
crdlmnt 

Au cas où ce ne soit pas fait dans le fichier, pour explications, affichez le commentaire en T1!

Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?

Vaucluse · Answer

Bonjour
Il ya encore pas mal de choses à expliquer et je doute que l'on se comprenne comme ça, mais on va quand même tenter... en prenant notre temps.
Votre tableau dans le dernier ficher, ce que je ne comprend pas:
colonne W, ligne 10 vous demandez un  calcul donnant 1275 , on calcule donc dans cette colonne la partie d'évolution due 33000 au total ligne 10, soit 1275 . On a déjà calculé le plafond de 540 à la ligne précédente
par contre en colonne X, ligne 11 ou je pensais avoir 40839-33000 pour calculer la partie débordante du plancher,et donc (39166-33000) le calcul n'est plus du tout le même

par ailleurs pour la colonne U on suppose que si le total des ventes 'atteint pas 21000,, il n'y a pas de commission.?

A vous lire

vicam · Answer

Re,

-Effectivement si les ventes sont inférieur à 21000 = il n'y a pas de commissions.

"colonne W, ligne 10 vous demandez un calcul donnant 1275 , on calcule donc dans cette colonne la partie d'évolution due 33000 au total ligne 10, soit 1275 . On a déjà calculé le plafond de 540 à la ligne précédente "

- Chaque ligne de la colonne S correspond à une vente. La commission doit être calculé sur l'intégralité de son montant. Mais l'importance de son montant peut faire que le calcul se répercute sur 2 tranches de %age de commission différents


Ligne 10 : la vente = 12542, sachant que la première tranche s'arrête à 33000, je reprends le dernier cumul 26625 et le soustrait au max de la tranche (33000-26625) soit 6375 et j'applique donc 20% de 6375 soit 1275. 
Mais je n'ai commissionné que 6375 sur les 12542, il me reste donc 12542-6375 soit 6167 a commissionné. Donc la tranche supérieur à 23% (6167*23%) soit 1418.

D'où une ligne de vente peut être concerné par une ou plusieurs tranche de commission.

merci

Cdlt

Vaucluse · Answer

Re  

on avance petit à petit... peut être ????  :-)  
voila une nouvelle tentative qui devrait se rapprocher même si ce n'est pas encore ça???  
merci de retourner le fichier avec les corrections qui s'imposent et les explications avec pour affiner les formules;  
http://www.cijoint.fr/cjlink.php?file=cj201106/cijUi27CAD.xlsx  

Crdlmnt  
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?

edit: je pense qu'il y a erreur dans la dernière colonne,à revoir, mais là je suis à la bourre

vicam · Answer

Re
http://www.cijoint.fr/cjlink.php?file=cj201106/cijsMfJM7U.xlsx
Bravo.....ça chauffe...vous y êtes presque....
Je me suis permis de rectifier la formule de la colonne U (initialement si mes ventes étaient inférieures à 21000 j'avais quand même des commissions) mais à la fin de la colonne j'ai des valeurs négatives qui a mon avis correspond au commission de la tranche supérieur!!!
La colonne des commissions 12% n'a pas l'air de fonctionner...*

Sinon pour le reste j'ai l'impression que c'est que du bonheur...!!!!

Je vous abandonne pour ce soir!!!

Bonne soirée et toujours et encore merci !!

Vaucluse · Answer

Re
voila la dernière mouture

Dites moi précisément ce qui ne marche pas en colonne 12%
j'ai rectifié la dernière colonne et intégré une condition à la colonne si le total est <21000
http://www.cijoint.fr/cjlink.php?file=cj201106/cijulDuRka.xlsx
A vous lire... demain matin!
bonne soirée

vicam · Answer

http://www.cijoint.fr/cjlink.php?file=cj201106/cijm6kNOmN.xlsx

Re,

Vous touchez au but...j'ai refait une simulation j'ai fait quelques annotations sur le fichier.
Je me sent impuissant, je peux vraiment pas vous aider je suis largué !

merci

Vaucluse · Answer

Re re

alors cette fois peut être :-)
http://www.cijoint.fr/cjlink.php?file=cj201106/cijCgk8VKN.xlsx

c'est vrai que dans le total, on a tendance à en oublier un peu en route, mais à force d'éliminer ce que l'on ne doit pas trouver ....

A vous lire
crdlmnt

vicam · Answer

Waouh! ça y est! je pense que vous tenez le bon bout.
Je vais continuer a faire quelques simulations. Le vrai test sera grandeur nature, mardi prochain, avec la paie de juin.
Je vous tiens au courant et en tout cas je vous remercie encore une fois pour votre aide précieuse.
Si du Vaucluse vous montez dans la région saumuroise n'hésitez pas!!!!

Vaucluse · Answer

Alors vivement mardi :-)

petite remarque à ne pas perdre de vue dans le tableau:

pour unifier les formules, celles de la ligne 4 font parfois référence à des cellules de la ligne 3(ligne des titres)
Ne pas remplacer les titres dans cette ligne par des valeurs numériques, ça pourrait perturber la 1° LIGNE_(pas sur, mais on ne sait jamais)
(par exemple ne pas mettre 12% au lieu de Taux 12%)

Bonne route
au plaisir

Crdlmnt

Vaucluse · Answer

Bonjour pour ce lundi, j'espère que vous avez passé un bon week end:    

votre fichier avec quelques corrections:    
la colonne 12% est reprise    
dans la colonne 23%,Il y avait une erreur de taux dans un item,     

....mais l'ensemble ne donne pas toujours ce que vous attendez et donc là,moi qui croyais avoir tout compris, je ne comprend plus rien!    

pour éclaircir la situation, j'ai placé à droite du tableau, pour chacun des taux de 12%à 25%, les calculs que font les parties de formules et à coté le raisonnement qui s'y rattache pour les lignes du tableau qui affichent un  montant.    

Il reste quelques décimales qui viennent sans doute d'un empilage de décalages de valeurs dans les transferts de tableau à tableau,mais en gros, tout devrait y être.    

Par contre, si, comme vous le demandez il faut trouver une commission totale de 1600€ pour la tranche à 20% alors que la fourchette est de :    
33000-25500=7500 soit 7500*20%=maxi:1500, il faudra m'expliquer par où vous passez!!    

même remarque pour la tranche de 33 à 41000 dont la plafond est d'après vos calculs,de 2000, alors que (41000-33000)*23%=1840   

A vous lire    

bien cordialement    

  

Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?

vicam · Answer

Bonjour,

J'espère que vous avez passé un bon week-end, vous aussi! 
Imaginez un peu aujourd'hui, nous avons presque aussi chaud que par chez vous, le thermomètre affiche 38° à l'ombre ( sans les cigales)!

Pour en revenir au calcul...vous aviez raison mes  formules étaient fausses. Dans ce cas, même en les corrigeant je n'arrive pas a trouver le même résultat! 

Dans l'attente de vous lire!!

Cordialement

http://www.cijoint.fr/cjlink.php?file=cj201106/cijBRuIwcl.xlsx

Vaucluse · Answer

Bonjour

Il ne suffit pas de corriger votre fichier pour que cela fonctionne... le fichier que vous me renvoyez dans votre dernier message est bien corrigé dans votre domaine, mais ce n'est pas le dernier que je vous ai transmis.

Je vous remet le lien ci dessous
. il avait été rectifié, donc vérifiez si cela correspond maintenant que vous avez rectifié votre partie.
Voyez surtout les détails des calculs que j'ai reporté dans les colonnes Z à AC

Regardez donc ce dernier modèle, pour moi les calculs du tableau sont conformes à ce que vous demandez, . Dans le cas contraire , précisez moi ce qui cloche dans les calculs détaillés.

A vous lire

crdlmnt
http://www.cijoint.fr/cjlink.php?file=cj201106/cijeqoSkXS.xlsx

Vaucluse · Answer

Bonjour
Allons y calmement. 
Vous avez bien rectifié vos montants, mais le fichier que vous renvoyez maintenant n'est pas celui que je vous ai transmis dans mon dernier message.Il avait été corrigé et je vous demandais de vérifier la conformité des calculs que j'avais détaillés sur ce fichier dans les  colonnes
Je vous remet le lien ci dessous à toutes fins utiles.
Vérifiez s

http://www.cijoint.fr/cjlink.php?file=cj201106/cijeqoSkXS.xlsx

vicam · Answer

Bonjour,
Effectivement je vous ai renvoyé mon fichier car dans votre message du 24 je n'avais pas trouvé votre dernier version (ci-dessus renvoyé), du coup je comprends mieux le message du 24 ..:)

J'ai fait quelques copier/coller de formules car j'avais des résultats négatifs...tout semble coller. J'ai fait plusieurs simulations il n'y a que dans le cas ci-joint qu'il se glisse une petite erreur. Cette situation de chiffre est quasiment impossible donc si c'est trop compliqué laissez faire!

Merci
Cordialement,


http://www.cijoint.fr/cjlink.php?file=cj201106/cij0faBci4.xlsx

Vaucluse · Answer

Bonjour
s'il s'agit de la non concordance des 8% du tableau avec ceux de la cellule F40;le problème est chez vous , pas chez moi!

Mais il est très facile à résoudre:

Reprenez votre ligne 28 des totaux et faites les totaux jusqu'à la ligne 27 plutôt qu'à la ligne 24 et ça ira beaucoup mieux  :-))))

même problème sur la feuille 2 où vous répétez les résultats jusqu'à 24 alors que le tableau va à ligne 27?

bien cordialement

Calcul par tranche excel

17 réponses

Discussions similaires