Excel - Calcul par pourcentage et par tranche

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.

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.

En testant du majeur au mineur, cela ne fait seulement que 3 tests !

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

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

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 !

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

Bonjour,

Voici le tableau :

https://www.cjoint.com/?khniKcmxnL

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

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 ;-)

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)

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 ;-)

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

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.

Vois le fichier à l'adresse suivante
https://www.cjoint.com/?kboTl4Bi3X

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 !!!

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.

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.

https://www.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 !

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