Excel - Calcul par pourcentage et par tranche

Résolu

Excel
Formule
Calcul

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention -
Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention - 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.

Afficher la suite

Répondre (39) Posez votre question

Répondre (39)

A voir également:

Formule excel barème progressif
Formule si et excel - Guide
Formule moyenne excel plusieurs colonnes - Guide
Formule somme excel colonne - Guide
Excel mise en forme conditionnelle formule - Guide
Liste déroulante excel - Guide

39 réponses

Réponse 1 / 39

Meilleure réponse

La meilleure réponse est la réponse qui a été validée par nos équipes.

Coco44

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

Réponse 2 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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.

Réponse 3 / 39

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

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.

Réponse 4 / 39

Claude Claude Messages postés 1756 Date d'inscription Statut Membre Dernière intervention 584

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

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 39

xkristi Messages postés 4264 Date d'inscription Statut Membre Dernière intervention 564

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

Réponse 6 / 39

xkristi Messages postés 4264 Date d'inscription Statut Membre Dernière intervention 564

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

Réponse 7 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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 !

Réponse 8 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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

Réponse 9 / 39

ouiabsurfer

Bonjour,

Voici le tableau :

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

Réponse 10 / 39

eriiic Messages postés 24603 Date d'inscription Statut Contributeur Dernière intervention 7 276

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

Réponse 11 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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

Réponse 12 / 39

Coco44

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)

Réponse 13 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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

Réponse 14 / 39

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

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

Réponse 15 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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.

Réponse 16 / 39

xkristi Messages postés 4264 Date d'inscription Statut Membre Dernière intervention 564

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

Réponse 17 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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

Réponse 18 / 39

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

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.

Réponse 19 / 39

ouiabsurfer Messages postés 30 Date d'inscription Statut Membre Dernière intervention 14

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 !

Réponse 20 / 39

Vaucluse Messages postés 26496 Date d'inscription Statut Contributeur Dernière intervention 6 438

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

Répondre Posez votre question

Votre réponse

Discussions similaires

calcul de pourcentage sur excel

valerie -
Raymond PENTIER -

37 réponses

Calculer un nombre de cellules

Calcul ancienneté

Degrés minutes secondes

EAN SOUS EXCEL

Calcul de la différence entre deux nombres

Always -
Tehani_t -

3 réponses

Devenez membre en quelques clics

Connectez-vous simplement avec ceux qui partagent vos intérêts
Suivez vos discussions facilement et obtenez plus de réponses
Mettez en avant votre expertise et aidez les autres membres
Profitez de nombreuses fonctionnalités supplémentaires en vous inscrivant

S'inscrire