Posez votre question Signaler

Excel - Calcul par pourcentage et par tranche [Résolu]

ouiabsurfer 30Messages postés mardi 11 septembre 2007Date d'inscription 23 octobre 2011 Dernière intervention - Dernière réponse le 8 oct. 2007 à 05:33
Bonjour,
J'ai besoin de calculer un montant par pourcentage qui variera en fonction de la tranche du chiffre.
Exemple :
1,50% (de 0 à 1 725 €)
0,50% (de 1 726 à 4 600 €)
0,25% (de 4 601 à 34 500 €)
0,10% (au dessus de 34 500 €)
Si j'ai un montant de 5000 €, je dois calculer 1725 x 1,5%(=25,87) + 2874 x 0,50 (=14,37) + 399 x 0,25 (=0,99). Ce qui fait un montant de 41,23. Est-ce qu'il y aurait une âme charitable qui pourra m'aider à trouver la formule nécessaire pour réaliser ce calcul ? En effet, cela doit me permettre d'y entrer un chiffre et d'avoir la réponse dans une autre cellule.
J'ai essayé une multitude de formules avec SI et ET mais cela fait trop de conditions pour une seule condition.
Je vous remercie par avance de toute aide sur le sujet.
Lire la suite 
Réponse
+6
moins plus
Bonjour ouiabsurfer,

J'ai testé cette formule, ça a l'air de fonctionner, si ça peux t'aider :

=SI(A3>=1725;1725*1.5/100+SI(A3>=4600;2874*0.5/100+SI(A3>=34500;29899*0.25/100+(A3-34500)*0.1/100;(A3-4600)*0.25%);(A3-1725)*0.5/100);A3*1.5/100)

Il n'y a que des SI
Ajouter un commentaire
Réponse
+4
moins plus
Salut xkristi,

Enfin, ça marche !!! C'est très pratique d'avoir rajouté le module qui permet de modifier les plafonds de calculs sans réécrire les formules ! Merci mille fois pour votre aide.
Ajouter un commentaire
Réponse
+2
moins plus
En testant du majeur au mineur, cela ne fait seulement que 3 tests !
Ajouter un commentaire
Réponse
+2
moins plus
Bonjour
Je pense que vous pourriez réduire les calculs en incluant une base fixe correspondant au minimum de chaque tranche.
Ainsi, il vous suffirait, à mon avis, de placer la formule suivante
=si(X1<1725;X1*1,5%;si(X1<si(X1<4600;25,875+(X1-4600)*0,25%;si(X1<34500 etc......
Ce qui ne vous donne qu'une formule à 4 conditions alors qu'excel en autorise 7.Par ailleurs, le fait d'intégrer en nombre le taux correspondant aux sommes inférieures allège considèrablement vos formules
Excel dans le cas de cette formule s"arrète à la première condition remplie et ne prendra donc en compte que celle correpondant au montant sans allez plus loin.
Bien cordialement.
Ajouter un commentaire
Réponse
+1
moins plus
Variante :
la formule est à mettre en D3
te permet de faire varier tes plafonds et tes taux
elle n'est pas tout à fait exacte , je te laisse chercher (+1 ou -1) ? lol


Plafond Taux CA commissions
1725,00 1,50% 1725,00 "SI(C2>A$4;(C2-A$4)*B$5+(A$4-A$3)*B$4+(A$4-1725)*B$3+A$2*B$2;
SI(C2>A$3;(C2-A$3)*B$4+(A$3-1725)*B$3+A$2*B$2;
SI(C2>A$2;(C2-A$2)*B$3+A$2*B$2;
A$2*B$2)))"

4600,00 0,50% 3200,00 33,2500
34500,00 0,25% 32000,00 108,7500
0,10% 40000,00 270,0000
5000,00 41,2500

Ajouter un commentaire
Réponse
+1
moins plus
Salut Vaucluse,

Désolé d'être un rabat-joie, mais il subsiste une erreur dans vos formules.

Voici en récapitulation le principe de calcul pour une somme de 5000.

1ère tranche : de 0 à 1 725 x 1,5% = 25,875
2ème tranche : de 1726 à 4600 = 2874 x 0,5% = 14,370
3ème tranche : de 4600 à 5000 = 400 x 0,25% = 1,00
Total général : 41,245

Si tu essaies avec tes formules, les résultats sont erronés !
Ajouter un commentaire
Réponse
+1
moins plus
Xkristi,

Pourquoi me demandes-tu si j'ai essayé avec un montant inférieur à 1725 ? En effet, je constate que ça marche bien ! Est-ce qu'il y a quelque chose qui m'a échappé ? mdr
Ajouter un commentaire
Réponse
+0
moins plus
Bonjour Claude Claude
les % sont par tranche , peut-être pas si simple pour lui

si j'ai un peu de temps j'essaierais de l'aider ce matin
Ajouter un commentaire
Réponse
+0
moins plus
Bonjour,

tu peux imbriquer les conditions (jusqu'à 7)
test simple:
=si(test;valeur_si_vrai;valeur_si_faux)
tu remplaces valeur_si_faux par un autre test:
=si(test;valeur_si_vrai;si(test;valeur_si_vrai;valeur_si_faux)) etc
eric
Ajouter un commentaire
Réponse
+0
moins plus
Salut Coco44,

J'ai essayé ta formule, mais elle me renvoie toujours le même résultat; c'est-à-dire 0. J'ai certainement dû me planter quelque part ! Il se peut que je n'ai pas été insuffisamment précis lors de l'exposé du problème. Alors, voilà, j'aurai souhaité pouvoir introduite la somme a calculer dans une cellule et avoir les résultats par tranche dans des colonnes différentes, c'est-à-dire le résultat de la formule pour les tranches de : 1,50%, 0,50%, 0,25%, 0,10%.

Peux-tu m'aider à écrire la formule pour que le résultat de la cellule dans la colonne A1 soit ventilé par tranche dans les colonnes B1, C1, et D1 ?

En tous les cas, je te remercie infiniment de ton aide ;-)
Ajouter un commentaire
Réponse
+0
moins plus
Je récapitule :
Dans A2, tu as ta valeur.
Dans B2, tu as ta formule complète, soit :
=SI($A2>=1725;1725*1.5/100+SI($A2>=4600;2874*0.5/100+SI($A2>=34500;29899*0.25/100+($A2-34500)*0.1/100;($A2-4600)*0.25%);($A2-1725)*0.5/100);$A2*1.5/100)

Dans C2 tu as la formule pour calculer la tranche à 1.50% :
=SI($A2>=1725;1725*1.5/100;$A2*1.5/100)

Dans D2 tu as la formule pour calculer la tranche à 0.50% :
=SI($A2>=4600;2874*0.5/100;($A2-1725)*0.5/100)

Dans E2 tu as la formule pour calculer la tranche à 0.25% :
=SI($A2>=34500;29899*0.25/100;($A2-4600)*0.25%)

Dans F2 tu as la formule pour calculer la tranche à 0.10% :
=SI($A2>=34500;($A2-34500)*0.1/100;0)

Ces formules devraient fonctionner. Tu peux peut-être avoir des pbs avec les séparateurs (moi j'ai ; mais ça peut être ,) et le symbole décimal (chez moi le point mais ça peut être la virgule)
Ajouter un commentaire
Réponse
+0
moins plus
Salur Eriiic,

Je te remercie, mais si j'utilise la fonction TEST, elle me permet de savoir si un chiffre donné correspond à une valeur où pas mais elle ne me permets pas défaire une ventilation par tranche.

Je reprends le même exemple : Si j'introduis la somme de 5000, j'aurais souhaité que le programme doit pouvoir me calculer que :

de 0 à 1 725 € de la somme de 5 000, il doit appliquer un taux de 1,5%
de 1 726 à 4 600 € de la somme de 5 000, il doit appliquer un taux de 0,5%
de 4601 à 34 500 € de la somme de 5 000, il doit appliquer un taux de 1,5%
de 34 500 à l'infini de la somme de 5 000, il doit appliquer un taux de 0,10%

Ce qui donnera 4 colonnes avec comme résultats : 25,87 | 14,37 | 0,99 | 0

Je souhaite avoir les résultats par colonne pour pouvoir cumuler les sommes par tranches de plusieurs lignes de calcul.

Je sais que j'en demande peut-être un peu trop ;-)
Ajouter un commentaire
Réponse
+0
moins plus
ouiabsurfer,
Quelque chose m'échappe dans votre demande. A quoi sert de calculer la partie du montant relative à la tranche précedente puisque, et c'est là que je n'ai peut étre pas compris, il s'agit d'une constante liée à la partrie fixe du montant.
Il suffirait alors a mon avis de construire un tableau à 5 colonnes:
Colonnes A le montant à calculer
Colonne B:si(A1>1725;25,875;0)soit 25,87=1725*1,5%
Colonne C:Si(A1>4600;14,375;0) soit 14,375=(4600-1725)*0,5%
Colonne D :SI(A1>34500;74,75;0)soit 74,75=(34500-4600)*0,25%

Et derniére colonne:
=Si(B1=0;A1*1,5%;si(C1=0;(A1-1725)*0,5%+B1;si(D1=0;(A1-4600)*0,25%+B1+C1;B1+C1+D1+(A1-34500)*0,10%

En fin de compte excel devrait détecter lea fourchette du montant à partir de la colonne affichant le montant fixe % de cette tranche.
Cette formule n'utilisant que trois conditions, vous pouvez y rajouter 4 tranches sur le même principe.
Bien cordialement
Ajouter un commentaire
Réponse
+0
moins plus
Salut Coco44,

J'ai testé ta formule et elle marche comme sur du velours sauf pour un petit bug :-( Avec des valeurs au dessous de 5000 elle affiche les bons taux et dans les colonnes ou il n'y pas lieu d'avoir une valeur, elle affiche 0, ce qui est parfait. Par contre, si on y entre une valeur comme 4000, le résultat est le suivant :

Colonne B (le résultat global) : 37,25
Colonne C (le taux à 1,50%) : 25,88
Colonne D (le taux à 0,50%) : 11,38
Colonne E (le taux à 0,25%) : -1,50
Colonne F (le taux à 0,10%) : 0

Ma question est la suivante : Pourquoi ta formule affiche t'-elle parfois des valeurs négatives au lieu de 0 ?

Merci par avance.
Ajouter un commentaire
Réponse
+0
moins plus
Vois le fichier à l'adresse suivante
http://cjoint.com/?kboTl4Bi3X

Ajouter un commentaire
Réponse
+0
moins plus
Salut Vaucluse,

Je viens de tester votre formule pour laquelle je vous remercie chaleureusement. Elle est efficace, mais pas suffisamment loquace. C'est à dire; elle m'affiche la somme totale du calcul, mais pas le détail.

Exemple :

Si j'entre la somme de 10 000 avec vos formules, le résultat obtenu est :
Colonne B : 25,875
Colonne C : 14,375
Colonne D : 0
Colonne E : 53,75 (ce qui correspond au total des cumule)

Or, je souhaite que chaque colonne affiche le montant de son pourcentage respectif, c'est à dire :
Colonne B (le résultat global) : 53,75
Colonne C (le taux à 1,50%) : 25,875
Colonne D (le taux à 0,50%) : 14,370
Colonne E (le taux à 0,25%) : 13,500
Colonne F (le taux à 0,10%) : 0

Dans votre tableau, je n'ai pas le montant de la tranche à 0,25% qui participe au calcul pour arriver au montant final. C'est important pour moi, car le tableau peut avoir une centaine de lignes et il me faut avoir un cumul par tranche de pourcentage, c'est-à-dire toutes les écritures concernant les 1,50%, 0,50%, 0,25% et 0,10%.

Merci par avance de votre aide précieuse !!!
Ajouter un commentaire
Réponse
+0
moins plus
Alors essayer
A partir de la ligne 2(j'ai mis ls entête sur la 1)
Colonne B:=C2+D2+E2+F2
Colonne A, 1,5% / =SI(A2>1725;25,875;A2*1,5%)
Colonne B,0,5% / =SI(A2<1724;0;SI(A2>4600;23;(A2-1725)*0,5%))
Colonne C 0,25% =SI(A2<4600;0;SI(A2>34500;14,375;(A2-4600)*0,25%))
Colonne E 0,1 % ==SI(A2>34500;(A2-34500)*0,1%;0)

DSi j'ai bien compris votre souci, ça devrait marcher
Cordialement
PS:Si par hasard vous deviez changer vos taux ou vos plafond dans l'avenir, il serait prudent de leur réserver des cellules de bases et de remplacer dans les formules ci dessus, les valeurs fixes par le N° de cellule de référence où elles se trouvent, bien entendu en les bloquant.
Ajouter un commentaire
Réponse
+0
moins plus
Salut xkristi,

Mille mercis pour votre tableau, j'ai l'impression de rêver, je ne m’attendais pas à une telle avalanche de personnes voulant me donner un coup de main. C'est vraiment sympa !

Par contre, les résultats ne correspondent pas aux calculs supposés.

J'ai joint le tableau de Coco44 qui me permet d'avoir tout ce que je souhaite sauf qu'il affiche des valeurs négatives dans les colonnes ou il n'y rien a afficher. Sinon, il fonctionne bien et pourra vous permettre de comprendre ce que je recherche en terme de ventilation des tranches.

http://cjoint.com/?kbqpEa0quR

Voici en récapitulation le principle de calcul pour une somme de 5000.

1ère tranche : de 0 à 1 725 x 1,5% = 25,875
2ème tranche : de 1726 à 4600 = 2874 x 0,5% = 14,370
3ème tranche : de 4600 à 5000 = 400 x 0,25% = 1,00
Total général : 41,245

Encore merci !
Ajouter un commentaire
Réponse
+0
moins plus
Quelque soit la formulation conditionnelle adoptée, les valeurs négatives viennet du fait que lorsque l'on considére un plafond, on oublie le plancher précédent.
Ainsi tout ce qui va calculer, dans le cas par exemple entre 1725 et 4600, le % De 4600-1725 donnera une valeur négative lorsque le chiffre de base sera inférieur à 1725!
C'est ce point que j'ai contourné (cf mlessage 14)en vous proposant mon tableau et en éliminant en entrée de formule les valeurs au dessous du plafond précédent, qui devient le plancher suivant.

Nota: dans les formules que je vous ai proposées, j'ai laisser trainer un 1724 inutile, il n'y a pas besoin de décaler d'un point les valeurs (Plafond, plancher)car la formule ramenera de toute façon le résultat à 0 lorsque la valeur d'entrée sera égale au plancher, et comme cela on évite les ennuis avec d'éventuelles décimales
Bien cordialement
Ajouter un commentaire
Réponse
+0
moins plus
Suis je bête, j'ai omis de vous prsenter (tant bien que mal) mon résulat selonmessage 14. Est ce que vous siuhiatez?

Montant CUMUL 1,50% 0,50% 0,25% 0,10%
34600 63,35 25,875 23 14,375 0,1
4300 38,75 25,875 12,875
34500 123,625 25,875 23 74,75
172 2,58 2,58
200 3 3
5000 49,875 25,875 23 1
10000 62,375 25,875 23 13,5

Bonne chance
Science sans conscience n'est que ruine de l'Ame
Ajouter un commentaire
Ce document intitulé «  Excel - Calcul par pourcentage et par tranche  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes.

Le fait d'être membre vous permet d'avoir des options supplémentaires.