Calculer un tarif selon un revenu
Bonjour,
Je suis face à un dilem assez ennuyeux qui est celui de calculer les tarifs d'une garderie selon un revenu dans Excel.
Je vais essayer d'être clair.
Rev = Revenu
T1 = tarif calculer selon un pourcentage prix sur le revenu
T2 = Prix de la nourriture à aujouter à T1 (ce prix sera Fixe)
Tt = Tarif total
Je dois calculer Tt en foncion du Rev, du T1 et du T2. Pour cela, il me faut faire le calcule suivant:
Tt = T1 + T2 en sachant que T1 = x% du Rev
exemple:
Rev = 300; T1= 12 (4% de 300); T2= 50
Tt = 12 + 50 = 62.-
Jusque la, pas de problème. Ou ça se corse, c'est au moment ou il faut calculer le Tt selon le revenu (comme avant) mais en augmentant le pourcentage de manière linéaire à force que le revenu augmente.
Donc on fixe le pourcentage le plus bas pour le revenu considéré comme le plus bas et le pourcentage le plus haut pour le revenu considéré comme le plus élevé. Entre les deux, une augmentation linéaire du pourcentage, faisant varié la valeur T1 et modifiant donc le résultat Tt en fonction du revenu Rev.
exemple:
Rev: 300 -------- T1: 12 (4%) + T2: 50 ------- à payer: Tt: 62 // revenu minimum, pourcentage minimum.
|...
| ... augmentation du pourcentage de manière linéaire
| ...
Rev: 15000 -------- T1: 2250 (15%) + T2: 50 ------- à payer: Tt: 2300 // revenu maximum, pourcentage maximum.
Voila, est-ce que quelqu'un à une idée de la procédure qu'il me faudrait utiliser?
Si je n'ai pas été assez clair, veuillez, svp, ne pas hésiter à me le signaler.
Ps: j'imagine que je vais devoir utiliser du VBA, j'averti que je n'en ai utiliser que très peu de fois.
Mille fois merci à celui qui arrivera à m'aider à me démêler tout ca.
Cyrius.
Bonjour,
Une pièce jointe avec quelques exemples concrets et les résultats à obtenir faciliterait grandement la compréhension de la question.
Quels sont les % minimum et maximum ?
Sur quelles fourchettes de revenus s'appliquent-ils ?
très bien, je ne peux hélas pas le faire pour le moment, mais demain sa sera fait dans le plus de détail possible.
merci et bonne soirée.
Salutcyrius a écrit :Bonjour,
Je suis face à un dilem assez ennuyeux qui est celui de calculer les tarifs d'une garderie selon un revenu dans Excel.
Je vais essayer d'être clair.
Rev = Revenu
T1 = tarif calculer selon un pourcentage prix sur le revenu
T2 = Prix de la nourriture à aujouter à T1 (ce prix sera Fixe)
Tt = Tarif total
Je dois calculer Tt en foncion du Rev, du T1 et du T2. Pour cela, il me faut faire le calcule suivant:
Tt = T1 + T2 en sachant que T1 = x% du Rev
exemple:
Rev = 300; T1= 12 (4% de 300); T2= 50
Tt = 12 + 50 = 62.-
Jusque la, pas de problème. Ou ça se corse, c'est au moment ou il faut calculer le Tt selon le revenu (comme avant) mais en augmentant le pourcentage de manière linéaire à force que le revenu augmente.
Donc on fixe le pourcentage le plus bas pour le revenu considéré comme le plus bas et le pourcentage le plus haut pour le revenu considéré comme le plus élevé. Entre les deux, une augmentation linéaire du pourcentage, faisant varié la valeur T1 et modifiant donc le résultat Tt en fonction du revenu Rev.
exemple:
Rev: 300 -------- T1: 12 (4%) + T2: 50 ------- à payer: Tt: 62 // revenu minimum, pourcentage minimum.
|...
| ... augmentation du pourcentage de manière linéaire
| ...
Rev: 15000 -------- T1: 2250 (15%) + T2: 50 ------- à payer: Tt: 2300 // revenu maximum, pourcentage maximum.
Voila, est-ce que quelqu'un à une idée de la procédure qu'il me faudrait utiliser?
Si je n'ai pas été assez clair, veuillez, svp, ne pas hésiter à me le signaler.
Ps: j'imagine que je vais devoir utiliser du VBA, j'averti que je n'en ai utiliser que très peu de fois.
Mille fois merci à celui qui arrivera à m'aider à me démêler tout ca.
Cyrius.
pas tout compris, mais je vais essayer :
A1 : la valeur de départ
B1 : =A1*SI(A1<=300;4%;SI(A1>=15000;15%;(0,04+((A1-300)/(15000-300))*0,11)))+50
A1 = 100 => B1 : 54 => 4,00%
A1 = 300 => B1 = 62 => 4,00%
A1 = 7650 => B1 = 776.75 => 9,50%
A1 = 15000 => B1 = 2300 => 1500%
A1 = 16000 => B1 = 2450 => 15,00%
de 0 à 300 => 4,00%
égal ou plus de 15000 => 15%
de 300 à 15000 => Pour avoir le pourcentage, on applique la formule :
=(0,04+((A1-300)/(15000-300))*0,11)
A+
Merci pour cette réponse Gorfael, mais ce que je recherche serait de donner la possibiliter à celui qui ouvrira la page Excel de changer le %min et le %max à sa guise simplement en le changeant dans la cellule (voir document en annexe).
J'ai donc mis un document en annexe avec quelques explications. SVP, signalez le moi si ce n'est pas claire.. merci..
https://www.excel-pratique.com/~files/doc/calcul_pourcent.xls
Mais en fait, en y réfléchissant, c'est pas si mal ta solution.. je vais quand même essayer.. mais si vous avez d'autre propositions, n'ésitez pas..
Car plus tard, ce qui serait le top du top, serait que l'utilisateur puisse choisir à certains revenus des pourcentages fixe et que depuis la, l'augmentation linéaire se fasse.
Je sais, ce n'est pas clair, mais une chose à la fois, je reviendrais dessus si besoin est plus tard..
Edit:
Gorfael, es-tu sur de la formule pour calculer le pourcentage? car quand je l'inscrit il y a une erreur au niveau du " *0,11 ".
Bonjour,
Le problème rencontré avec la formule de Gorfael est le séparateur de décimales, qui me semble être le point chez toi et la virgule chez Gorfael.
Ci-joint un exemple avec un petit tableau qui permet d'ajuster les paramètres, tant du montant min et max des revenus que le min et max du pourcentage,
https://www.excel-pratique.com/~files/doc/cyrius.xls
dré
Wouuaaa.. parfait.. merci beaucoup dré..
Autre chose.. (dites moi si j'abuse..
Un exemple pour être plus clair:
"Hmmm" dis le directeur en voyant se super ptit programme. " C'est bien beau tout ca, mais je trouve que les personnes ayant des revenus j'usqua 4000 devrait pouvoir rester à 4% mais par contre à partir de 10'000 il faudrait que le pourcentage soit un peu plus élevé." Ausitôt dis ausitôt fait, le directeur à la possibilité d'adapter la page pour que jusqu'à 4000.- le % soit à 4 et que depuis la, ca augmente un peu plus vite (toujour linéaire) jusqu'à 10'000.- ou la il insère un 12% et que sa continue (tj de manière linéaire) jusqu'au 15% des 15'000.-
Au bout d'un mois, il se rend compte que le tout n'est pas top et que c'était mieu avant, alors il remet le 4% pour les salaires brut s'élevant jusqu'à 3000 et radabte le tout comme au départ.
Voila.. jesper que c'est compréhensible. Au cas contraire, merci de me le signaler..
++
Cyrius
Re,
Si tu as bien regardé ce petit programme, les revenus inférieurs à 4000 restent à 4%, tandis que ceux supérieurs à 15000 restent à 15%
Ces deux fourchettes de valeurs peuvent être modifiées.
C'est bien ce qui avait été demandé initialement.
La dernière modification demandée est fort possible, en adaptant le petit tableau supérieur (supprimer minimum et maximim, et les remplacer par 1ère tranche, 2ème tranche, ..., et spécifier en-dessous les revenus maximim et les % correspondants, ensuite adapter la formule existante à cette modification.
Toutefois, la première chose à faire est d'établir excatement, de façon complète et concise, ce qui est souhaité, car travailler (bénévolement !) par à coups n'est pas très agréable.
D'ailleurs rien ne dit que suite à la solution de cette dernière demande, il n'en suivra pas une autre.
S'il y a problème, joint ton fichier avec tes essais et dis-nous ce qui ne va pas.
Edition : A titre d'exemple, si un revenu tombe à cheval sur deux tranches, que faut-il faire, à la proportionnelle, sur base de la tranche inférieure ou de la supérieure ?
Salut dré,
Je suis désolé d'avoir procédé de cette façon, je me rend compte que ça peut être désagréable de se donner de la peine pour quelque chose en prenant du temps et au final de remarquer que la chose n'est pas finie, qu'il faudrait encor s'investir plus et meme que cela n'est pas pour nous même. (à comprendre: la personne qui aide).
Le fait est que au départ je ne pensais pas possible de réaliser tout ca sans passer par du VB et je ne connais pas du tout le VB... Comme il me fallait la première partie dans des délais assez court, je n'aurais pas imaginé que cela soit fait si rapidement. Donc j'ai minimiser le travail à la partie qu'il me fallait absolument. Mais grace à vous, j'ai le temps d'imaginer quelque chose de plus "complet" et c'est pour cette raison que j'ai continué sur ma lancé. Encore désolé et je t'assure qu'il n'yaura pas d'autre version. Enfin rien de prévu si ce que je fais est effectivement utile.
Pour en revenir au programme, j'avais remarqué qu'on pouvais modifier les fourchettes des revenus. et donc pour ce qui est des tranches, je vais tenter de réaliser se que tu as expliqué. Merci beaucoup pour tout..
Et si un revenu tombe entre deux tranche, il faudrait l'adapter à la tranche inférieure.
Cyrius.
Re bonjour,
Voici donc ou j'en suis: Je désirais donc pouvoir modifier le pourcentage en fonction d'un revenu et que tous les autres pourcentages s'adaptent de manière linéaire au pourcentage minimum, au maximum et en prennant compte d'un pourcentage intermédiaire rajouté en fonction du revenu.
Bref.. j'en suis au point ou j'arive à ajouter un pourcentage intermédiaire, mais le reste continue de manière linéaire en allant du minimum au maximum sans prendre en compte le pourcentage intermédiaire.
J'ai fais plusieurs essais mais je n'arrive pas à trouver le petit truc qui fait que..
https://www.excel-pratique.com/~files/doc/QE6OYcalcul_pourcent_final2.xls
Merci d'avance..
Cyrius
Bonsoir
Pour aller de 4% pour un revenu de 3000 à 15% pour un revenu de 15000, de façon linéaire, c'est à dire en augmentant le % de 0,46% tous les 500, je ne vois pas comment tu peux envisager de modifier cette échelle tant que tu conserves ton minimum et ton maximum.
Pour modifier un des pourcentage et conserver une évolution linéaire, il faut que tu change au moins une des 2 bornes. A mois que j'ai mal compris...
Cordialement
Bonjour,
En considérant les valeurs intermédiaires comme maximum d'une première tranche et comme minimum d'une seconde tranche, puis en additionnant ces deux tranches on devrait obtenir le résultat escompté.
J'essaie d'établir un exemple en cours de journée.
dré
Bonjour Amadéus,
en fait si on veut, l'intermédiaire aurait double face. pour la première partie, il serait vu come le maximum, et pour le 2ème partie comme le minimum afin d'appliquer un rendu linéaire. Mais jusque la je pourais m'en sortir. ou ca bug un peu, c'est qu'il faut adapter la formule pour que si il n'y ait pas de revenu intermédiaire, l'évolution se fasse du minimum au maximum. Je sais pas si je suis assez claire.. je dois avouer que c'est pas si simple à expliquer...
Bonjour dré,
c'est effectivement comme cela que j'imaginais faire, mais ou ca croche, c'est comme expliqué plus haut, si il n'y a pas d'intermédiaire, ca donne quoi? mais l'idée d'additinoner ces 2 tranches, je n'ai pas encor essayé. Je m'y met.
Merci beaucoup.
Cyrius
Re,
Essai en pièce jointe, à tester.
En résumé, il a été tenu compte de :
- revenu < revenu minimum → % minimum
- revenu > revenu maximim → % maximum
- revenu > revenu minimum et < revenu intermédiaire → répartition linéaire entre % minimum et % intermédiaire
- revenu > revenu intermédiaire et < revenu maximum → répartition linéraire entre % intermédiaire et % maximum
- pas de revenu intermédiaire ou pas de % intermédiaire → répartition linéaire entre % minimum et % maximum.
https://www.excel-pratique.com/~files/doc/cyriusV2.xls
Remarque : en faisant appel à la fonction SI, la formule est très complexe.
dré
Merci beaucoup dré.. C'est parfait.
20/20 cette fois-ci..
Effectivement, la formule est complexe.. je ne connaissais pas toutes ces possibilité.. je vais essayer d'en prendre note. Ma version à moi, avait une formule trop long pour être reconnue comme valide..
Mais maintenant, tout est bon.
Encor merci à tous.
Cyrius