Ils ont besoin de votre aide

12:26 Mon pc est infecté, rapport SmitfraudFix (Virus/Sécurité)
12:25 lecteur cd/dvd ne répond plus (Gravure)
12:24 disez moi vos avis svp sur mon site (Webmastering)
12:24 Probleme connexion internet avec free (Réseaux)
12:24 Comment changer les programme au... (Windows)
12:23 Pas de synchronisation de mon aol box (Internet)

Statut : Résolu

Excel - Calcul par pourcentage et par tranche

Posté par

ouiabsurfer, le lundi 1 octobre 2007 à 07:57:34

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.

Configuration: Windows XP
Excel 2007

Répondre à ouiabsurfer Signaler ce message aux modérateurs Aller au dernier message

39 réponses 12

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Claude Claude, le lundi 1 octobre 2007 à 08:18:39

En testant du majeur au mineur, cela ne fait seulement que 3 tests !
Soutenez le Comité de lutte contre le langage sms et les fautes volontaires sur Internet

Répondre à Claude Claude

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

xkristi, le lundi 1 octobre 2007 à 09:04:24

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 xkristi
La vie en plus ...

Répondre à xkristi

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Coco44, le lundi 1 octobre 2007 à 09:19:52

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épondre à Coco44

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

eriiic, le lundi 1 octobre 2007 à 09:21:52

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épondre à eriiic

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 11:18:58

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. Science sans conscience n'est que ruine de l'Ame

Répondre à Vaucluse

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

xkristi, le lundi 1 octobre 2007 à 11:29:58

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

xkristi
La vie en plus ...

Répondre à xkristi

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 11:41:44

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épondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Coco44, le lundi 1 octobre 2007 à 12:11:09

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épondre à Coco44

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 12:13:34

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épondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 12:56:48

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épondre à Vaucluse

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 14:26:50

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épondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

xkristi, le lundi 1 octobre 2007 à 14:48:12

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

xkristi
La vie en plus ...

Répondre à xkristi

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 15:32:35

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épondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 16:14:12

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.
Science sans conscience n'est que ruine de l'Ame

Répondre à Vaucluse

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 16:30:09

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 !

Répondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 17:19:17

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
Science sans conscience n'est que ruine de l'Ame

Répondre à Vaucluse

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 17:26:51

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

Répondre à Vaucluse

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

ouiabsurfer, le lundi 1 octobre 2007 à 17:37:25

Salut Vaucluse,

Absolument parfait ! Je ne sais pas comment vous remercier. Vous m'avez économisé de longues heures de calcul avec calculette et café à gogo. Vous avez en effet su contourner le problème de valeurs négatives ce qui me permettra d'additionner facilement les colonnes !

Sans avoir recours à des VARIABLES, TEST et de SI imbriqués, je suis en admiration de vos formules de calcul vraiment concises, mais néanmoins puissantes.

Pour l'instant, je réfléchis au 1724 en trop. Je ne suis pas sûr d'avoir compris pourquoi il était de trop ? Mais je tenais avant tout à vous, remercie, de votre assistance bien précieuse.

Merci également à xkristi, Coco44 et eriiic pour toute leur aide.

Répondre à ouiabsurfer

Ce message vous semble utile, votez !
Signaler ce message aux modérateurs

Vaucluse, le lundi 1 octobre 2007 à 17:51:03

ouiabsurfer
Heureux d'avoir pu vous rendre service. Mais ne vous leurrez pas, mes solutions ne sont que des "combines" et les spécialistes qui fréquentent ces forums pourraient vous en donner de bien meilleures.
N'attachez pas trop d'importance au 1724, mais laissez 1725 à la place.Mon propos portait sur le fait que lorsque l'on utilise less ignes > et <, on laisse bien souvent de coté la valeur de référence (> oui, <oui, mais = jamais)
Dans votre cas, le jamais s'annule par l'opération de fin de formule qui donnera (1725-1725)*0,5% ce qui de toute façon, donnera 0 dans la colonne 1,5 %
Au plaisir
Science sans conscience n'est que ruine de l'Ame

Répondre à Vaucluse

39 réponses 12 Posez votre question Répondre

Trucs & astuces pertinents trouvés dans la base de connaissances

12/11 01h11	[excel]	Excel
12/06 03h04	Créer rapidement un planning de congés simple [Excel]	Bureautique
28/10 19h36	Google - Fonction calculatrice	Google
06/02 00h35	bureautique	Excel
15/02 16h35	[Word] Impossible de rétablir une liaison DDE avec Excel	Word

Plus d'astuces sur « Excel Calcul par pourcentage et par tranche »

Discussions pertinentes trouvées dans le forum

19/12 14h23	Calcul par tranche sous Excel	2
21/07 14h03	[Excel] Calcul au sein d'une coordonée	8
04/07 09h58	EXCEL : calcul différence dates en AA MM JJ	3
25/06 16h11	Excel calcul de différence d'heure.	3

Plus de discussions sur « Excel Calcul par pourcentage et par tranche »

Logiciels pertinents trouvés dans les téléchargements

	Excel Viewer - Avec Microsoft Office Excel Viewer 2003, vous pouvez ouvrir, afficher et imprimer des classeurs Excel (fichiers XLS ), même...	Catégorie: Tableur Licence: Freeware/gratuit
	PSPad - PSPad est un excellent éditeur de texte. Il est utile pour travailler sur du simple texte, mais aussi sur du HTML, CSS,...	Catégorie: Développement Licence: Freeware/gratuit
	Ma Calculatrice - Windows propose par défaut une calculatrice avec des fonctions dites scientifiques mais tout de même incomplètes. ...	Catégorie: Calculatrice Licence: Freeware/gratuit
	The Love Calculator - L'amour est un grand mot, alors tous les moyens sont bons pour la trouver. Le nom de chacun peut en dire beaucoup sur la...	Catégorie: Bureautique Licence: Freeware/gratuit

Plus de logiciels gratuits sur « Excel Calcul par pourcentage et par tranche »

Produits relatifs

Sony Micro Vault Excellence	Catégorie: Carte Mémoire / Clé USB	41.50 € MicroChoix
Sony Micro Vault Excellence	Catégorie: Carte Mémoire / Clé USB	16.74 € Misco FR
Sony Micro Vault Excellence	Catégorie: Carte Mémoire / Clé USB	29.33 € PriceMinister
Sony Micro Vault Excellence	Catégorie: Carte Mémoire / Clé USB	41.00 € PriceMinister

Plus de produits sur « Excel Calcul par pourcentage et par tranche »