Progression de tarifs par qutient familial
Bonjour
J'ai cherché survle forum la réponse à ma question mais je nai pas trouvé.
J'ai des tranches de quotient familial dont les bornes supérieures sont1,99 puis 2,99 puis 3,99 puis 5,99 puis 8,99. A chaque tranche correspond un prix payé comme suit: en dessous de 1,99 c'est 0,41€ puis entre 2 et 3,99 c'est 0,81€ puis 1,18€ puis 1,41€ qui est le prix plafond (cest à dire que au dessus de 8,99 tout le monde paie 1,41€)
Je cherche à lisser le tarif pour chaque progression de quotient familial pour éviter les effets de seuils à chaque changement de tranches. Ainsi dans mon exemple le dernier de la première tranche (0 à 1,99) paie 0,41€ mai que le premier de la deuxième tranche ne pais pas 0,81€ mais le juste prix par rapport au dernier de sa tranche qui lui paiera 0,81€.
Je n'ai pas de problème pour appliquer la formule à chaque tranche (je passe par une formule si(et( en cumulé, mais je n arrive pas a trouver la formule de lissage des tarifs à l'intérieur de chaque tranche. Je tourne en rond car j'ai toujours des effets de seuils qui apparaissent.
Je pense quil s agit dune formule logarithmique mais comment la mettre sur Excel... ?
Merci à ceux qui pourront M aider.
Bonjour,
Avec un système de calcul par tranches successives, comme pour le cher IRPP.
Formules au choix (PREVISION ou SOMMEPROD) :
A+
Bonjour,
Si tu veux des valeurs exactes aux QF 2, 3, 6 et 9 je vois plutôt 3 interpolations linéaires sur les segments des droites basées sur une tables des QF/Prix :
=ARRONDI(PREVISION(MIN(9;MAX(A2;2));DECALER($E$1:$E$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5););DECALER($D$1:$D$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5);));2)
Si tes QF ne peuvent jamais être <2 ou >9 tu peux remplacer MIN(9;MAX(A2;2)) par QF partout.
eric
bonjour
en principe les calculs "par tranches" sont (avec des coeff de 0.25 et .32 par exemple )
- si valeur<tranche1 alors =0,25* valeur
- si valeur entre tranche 1 et tranche 2 alors = 0,25* tranche1 + 0.32*valeur
ainsi il n'y a aucun souci de continuité ni injustice
edit : salut à tous
j'arrive après la bataille
Bonjour,
Dans ma collectivité, pour les repas dans les restaurants scolaires, le tarif est calculé ainsi :
Tarif = (Tmin + ((Tmax - Tmin) / (Qmax - Qmin)) * (QF - Qmin))
Où :
Tarif est le tarif à calculer
Les autres éléments sont fixés par une délibération du conseil municipal
TMin est un tarif plancher : personne ne paie moins.
TMax est un tarif plafond : personne ne paie plus.
QMin est le quotient plancher : en-dessous de ce quotient, les familles paient le TMin
QMax est le quotient plafond : au-dessus de ce quotient, les familles paient le TMax (comme celles dont on n'a pas pu calculer le QF, d'ailleurs, parce qu'elles n'ont pas fourni les infos)
Ce qu'on observe en appliquant cette formule à un échantillon de familles représentatifs, c'est une droite, sans effet de seuil, entre le QMin et le QMax, ce qui est censé garantir une certaine justice sociale. Le plus efficace, c'est de rédiger une fonction intégrant cette formule. Le plus délicat, c'est de faire varier les paramètres pour éviter les effets indésirables entre deux situations.
Bien cordialement, Daniel
eriiic a écrit :Bonjour,
Si tu veux des valeurs exactes aux QF 2, 3, 6 et 9 je vois plutôt 3 interpolations linéaires sur les segments des droites basées sur une tables des QF/Prix :
=ARRONDI(PREVISION(MIN(9;MAX(A2;2));DECALER($E$1:$E$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5););DECALER($D$1:$D$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5);));2)
Si tes QF ne peuvent jamais être <2 ou >9 tu peux remplacer MIN(9;MAX(A2;2)) par QF partout.
eric
Super merci. Les autres formules proposées fonctionnent toutes mais la votre m'évite le problème de la dernière tranche, celle qui va jusqu'à l'infini puisque j'ai un tarif plafond. Pour votre formule comment je dois faire pour renvoyer aux tranches c'est-à-dire imbriquer des SI et ET. ex: si le QF est entre 1,99 et 3,99, renvoie à telle formule de calcul du prix: je mets ça où dans votre formule ?
Dan42153 a écrit :Bonjour,
Dans ma collectivité, pour les repas dans les restaurants scolaires, le tarif est calculé ainsi :
Tarif = (Tmin + ((Tmax - Tmin) / (Qmax - Qmin)) * (QF - Qmin))
Où :
Tarif est le tarif à calculer
Les autres éléments sont fixés par une délibération du conseil municipal
TMin est un tarif plancher : personne ne paie moins.
TMax est un tarif plafond : personne ne paie plus.
QMin est le quotient plancher : en-dessous de ce quotient, les familles paient le TMin
QMax est le quotient plafond : au-dessus de ce quotient, les familles paient le TMax (comme celles dont on n'a pas pu calculer le QF, d'ailleurs, parce qu'elles n'ont pas fourni les infos)
Ce qu'on observe en appliquant cette formule à un échantillon de familles représentatifs, c'est une droite, sans effet de seuil, entre le QMin et le QMax, ce qui est censé garantir une certaine justice sociale. Le plus efficace, c'est de rédiger une fonction intégrant cette formule. Le plus délicat, c'est de faire varier les paramètres pour éviter les effets indésirables entre deux situations.
Bien cordialement, Daniel
Super ça marche. Merci bcp
Bonjour,
Pour votre formule comment je dois faire pour renvoyer aux tranches c'est-à-dire imbriquer des SI et ET. ex: si le QF est entre 1,99 et 3,99, renvoie à telle formule de calcul du prix: je mets ça où dans votre formule ?
Regarde le fichier.
La formule reste la même, seule la référence du QF (A2) change. La table $D$2:$D$5 fourni les valeurs à utiliser, pas de SI ou ET à ajouter.
Là elle est présente sur toute la colonne B mais c'est juste pour que tu puisses voir le résultat selon différents QF. Sur ton fichier tu la mets où tu veux.
eric
eriiic a écrit :Bonjour,
Pour votre formule comment je dois faire pour renvoyer aux tranches c'est-à-dire imbriquer des SI et ET. ex: si le QF est entre 1,99 et 3,99, renvoie à telle formule de calcul du prix: je mets ça où dans votre formule ?
Regarde le fichier.
La formule reste la même, seule la référence du QF (A2) change. La table $D$2:$D$5 fourni les valeurs à utiliser, pas de SI ou ET à ajouter.
Là elle est présente sur toute la colonne B mais c'est juste pour que tu puisses voir le résultat selon différents QF. Sur ton fichier tu la mets où tu veux.
eric
Ok merci j'ai compris. En revanche pouvez vous m'expliquer un peu la logique de la formule, c'est-à-dire comment il faut la lire. J'ai compris que le dernier chiffre était celui des décimales souhaitées, mais le reste est très abscons.
PS: comment mettre "résolu" sur le forum ?
Bonjour,
Popur Prevision() regarde l'aide excel pour les paramètres attendus, inutile que je la recopie ici.
MIN(9;MAX(A2;2)
: rend 2<=QF<=9
EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5)
: recherche dans la table la ligne avec le QF <= QF recherché
DECALER($E$1:$E$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5);)
: décale la plage $E$1:$E$2 d'autant de lignes pour la passer en paramètre à Prevision()
idem pour l'autre decaler()
eric
eriiic a écrit :Bonjour,
Popur Prevision() regarde l'aide excel pour les paramètres attendus, inutile que je la recopie ici.
MIN(9;MAX(A2;2)
: rend 2<=QF<=9
EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5)
: recherche dans la table la ligne avec le QF <= QF recherché
DECALER($E$1:$E$2;EQUIV(MIN(9;MAX(A2;2));$D$2:$D$5);)
: décale la plage $E$1:$E$2 d'autant de lignes pour la passer en paramètre à Prevision()
idem pour l'autre decaler()
eric
Super. Merci. Comme ça je vais pouvoir modifier les paramètres en comprenant ce que je fais. Résolu