Formule à partir de plusieurs variables et conditions

Bonjour

Je cherche dans le tableau ci-joint à calculer un montant en fonction de 2 critères fixes puis de plusieurs variables

La colonne dans laquelle je cherche à calculer le montant est la colonne « hybride » (colonne L de l’onglet « base de données »)

L’autre onglet « hypothèses » montre la grille tarifaire à prendre en compte

Il y a au départ 2 conditions fixes (les mêmes que pour les colonnes « hypothèse barème » et « taux d’effort ») :

• Si la colonne « total » (colonne I) est < à 0, alors la colonne L est égale à 0.
• Si la colonne « tarifs / réductions » (colonne G) est égale à « tarif frais de dossier », alors la colonne I est égale à 30

En dehors de ces 2 conditions, il s’agit de calculer le montant à appliquer en fonction des éléments suivants :

• Type de tarifs (colonne A)
• QF mens (colonne E)
• Tarifs / réduction (colonne G)

La table à prendre en compte dans l’onglet « hypothèses » est le tableau qui va de la cellule BA1 à la cellule BV21.

Selon le tarifs / réductions (colonne G) pratiqué et le type de tarifs (colonne A) indiqué, le montant à indiquer colonne L correspond au QF mens (colonne E) multiplié par le taux d’effort indiqué dans la colonne BG, BJ, BM, BP, BJ ou BV de l’onglet hypothèses, la colonne à retenir étant celle qui correspond au nom de l’activité figurant dans la colonne « tarifs / réductions (colonne G)

Par exemple : si un ménage relève du tarif 1, a un QF mens de 1.070, et a un tarif courant, alors le montant à appliquer correspondra à QF mens * cellule BJ7 de l’onglet « hypothèses » (il s’agit de la cellule BJ7 car 1.070 se situe dans la tranche QF n°6 – cf. colonnes BA et BB de l’onglet « hypothèses »)

Autre exemple : si un ménage relève du tarif 2, a un QF mens de 1.843, et a un tarif cumul et alors le montant à appliquer correspondra à QF mens * cellule BJ20 de l’onglet « hypothèses » (il s’agit de la cellule BJ20 car 1.070 se situe dans la tranche QF n°9 – cf. colonnes BA et BB de l’onglet « hypothèses »)

D’une certaine manière, la formule correspondante est vraisemblablement une sorte de « mix » des formules utilisées dans les colonnes « hypothèse barème » et « taux d’effort »

Merci d’avance pour votre aide ; je reste bien sûr disponible s’il y a des questions

Bien cordialement

Bonjour,

Avant d'aller plus loin, je voudrais connaître la logique qui fait que tous les tarifs avec réduction ont un total négatif, sauf sur les lignes 851 et 2335.
Est-ce normal ou une erreur ?

Dans le 2e exemple, j'appliquerai BS20 et non pas BJ20. Vous confirmez ?

Autre besoin d'explication (exemple) : dans l'onglet "Hypothèses", la cellule BM18 contient la formule =BK18/BB18. BK18 vaut 4361 et BB18 vaut 1101. Comment arrivez-vous au résultat BM18 = 40%.

Enfin, et surtout, est-ce qu'une macro vous irait ?

Bonjour,
merci pour votre message

Pour répondre à vos questions:
- le tarif "réductions" s'applique quand plusieurs personnes d'une même famille sont inscrits à une activité.
Sur la ligne 851, le montant est positif pour compenser la réduction (ligne 852) qui a été appliquée à défaut. Pour la ligne 2335, cela fait référence à la ligne 2334, pour laquelle le montant est de 0. Aucune réduction ne s'applique donc ici.

- pour le 2ème exemple: vous avez raison, c'est bien BJ20 et non BS20 qu'il faut prendre en compte, excusez-moi pour l'erreur

- la cellule BK18 de l'onglet "hypothèses" renvoie 436€ et pas 4361 de mon côté: est-ce bien le cas pour vous aussi?

Et sinon, oui, une macro me conviendrait!

Merci

Bien cordialement

D'accord, maintenant les choses sont claires. Le problème de l'affichage dans la cellule BK18 venait de la mise en forme de l'onglet. Voici ce que je vois : 436| (probablement un problème de format monétaire avec la police Frutiger LT 45 Light que vous avez utilisée).

Mais le résultat de 40% est juste. C'est le plus important.

Je ne sais pas comment traiter les situations suivantes :
- sur les lignes 159 et 2748, on a des valeurs < 0. Faut-il mettre 0 en L même s'il s'agit d'une annulation ?)
- sur la ligne 851, on a une valeur > 0 en annulation. Que faut-il mettre en L ?

Pour le moment, essayez ceci. On verra ensuite s'il y a des corrections à apporter.

Bonjour,

merci pour cet envoi!

Sur les lignes que vous évoquez, elles sont toutes à mettre à 0 en L, ce qui est le cas dans le fichier que vous m'avez envoyé, sauf pour la ligne 851.

En dehors de cette ligne, tout le reste fonctionne: merci beaucoup!

Bien cordialement

Bonjour,
Le problème avec cette ligne 851, c'est qu'elle n'obéit à aucune règle fixe. Sa colonne total n'est pas < 0 et il ne s'agit pas de frais de dossier.

Les deux autres opérations en annulation (lignes 159 et 2743) sont ont un total négatif ; elles entrent donc dans la 1ère condition fixe.

Deux possibilités :
- on pourrait ajouter une 3e condition fixe : si la chaîne "nnul" est présente dans la colonne G, il s'agit d'une annulation, donc on met 0 dans la colonne L comme on le fait avec les valeurs négatives. C'est un bricolage qui ne vaut pas grand-chose. Il suffit d'écrire "annullation" au lieu de "annulation" et le résultat est faux.
- on traite toutes les opérations en annulation manuellement. Comme, il n'y en a que 3 sur 2742 lignes, ça me paraît beaucoup plus solide.

A vous de décider ou de trouver une 3e règle fixe.

Merci pour votre réponse

On peut laisser comme ça: il ne s'agit que d'une ligne sur une base de données qui n'est pas censé bouger. Ce sera plus simple de saisir 0 à la main!

Merci beaucoup pour votre aide

Alors bon vent.