Formule de calcul d'un pourcentage vis à vis d'un chiffre d'affaire
Salut à toutes et à tous,
Je n'ai pas l'habitude de demander de l'aide de cette façon mais je n'arrive pas à trouver de solution à ma formule ... Je fais donc appel à votre science pour me trouver l'erreur.
Contexte : Je gère une équipe de collaborateurs, plus ils font de ventes, plus leur chiffre augmente et plus ils gagnent sur leurs ventes. La formule prend en compte les paliers de chiffre d'affaires pour adapter le pourcentage. A partir d'un certain chiffre, mes collaborateurs gagnent 100 % du chiffre produit et c'est à cet endroit que j'ai un soucis. Je n'arrive pas à faire prendre le dernier palier à ma formule, dès que le cumul de chiffre d'affaires dépasse le chiffre indiqué, elle calcule tout à 100%, sauf qu'entre le chiffre d'affaire indiqué et celui d'avant, il restait un petit bout qui n'était pas à 100 %.
En clair, voici comment ça fonctionne :
Chiffre d'affaires | Rémunération du collab' | Etat de la formule |
| <35000 | 75 % | Ok |
| 35000<CA<70000 | 80 % | Ok |
| 70000<CA<100000 | 85 % | Ok |
>100000 | 100% | Fail. |
Pour y voir clair avec les chiffres, si le cumul de CA est à 95 000 par exemple, et qu'on à une entrée à 10 000, on devrait avoir 5000 calculés à 85% et 5000 calculés à 100%. La formule calcule tout à 100% et je n'arrive pas à lui poser le palier..
Voici ma formule : =(SI(G4<=35000;F4*0,75;SI(ET(G4<=70000;G3>35000);F4*0,8;SI(ET(G4<=100000;G3>70000);F4*0,85;SI(ET(G4>100000);F4;SI(G4<=70000;(35000-G3)*0,75+(G4-35000)*0,8;SI(G4<=100000;(70000-G3)*0,8+(G4-70000)*0,85))))))-I4)
Données :
G4 > Chiffre d'affaire cumulé avec l'entrée
G3 > Précédent chiffre d'affaire cumulé
F4 > C'est l'entrée la nouvelle commission à calculer
Fichier de test : https://docs.google.com/spreadsheets/d/1tZivBACrdwVC276Abfu2dw_2M-drc7AjZ7RYg__Fiic/edit?usp=sharing
Si quelqu'un a une idée pour cloisonner la partie supérieure à 100000 et permettre à ma jolie formule de prendre en compte le palier avant de passer à 100 %, ce serait top !
Bonjour,
Même en reprenant ta formule, je ne m'y retrouve pas !
Je te propose plutôt une formule où les seuils/plafonds sont dans une feuille séparée avec un calcul de type sommeprod
Regarde si les résultats sont ceux que tu attends
https://docs.google.com/spreadsheets/d/1WlfL_HOHoQ8hIYh38YnPXosJ1JmZYuvFyFAq_teEx8U/copy
Hello,
Je te remercie pour ta réponse. Le soucis dans ta solution c'est qu'on ne peut pas calculer une entrée en fonction du cumul de chiffre. Ou alors je comprends pas bien l'objectif de la SOMMEPROD.
En mettant en pratique ta solution, j'arrive à avoir les mêmes retours que sur ton exemple mais ils correspondent pas à mes besoins.
Grossièrement ma formule doit effectuer le raisonnement suivant :
Exemple : Entrée à 10 000 / Cumul à 95 000 / Paliers : Cumul < 100 000 : 85% et Cumul > 100 000 : 100 %
Raisonnement formule recherché
100 000 - 95 000 = 5 000 / 5 000 x 0,85 = 4 250
95 000 + 10 000 = 105 000 / 105 000 - 100 000 = 5 000
4 250 + 5 000 = 9 250
Pour ma formule, je l'avais faite il y a plus de 2 ans en bidouillant pas mal, j'ai du mal à comprendre comment j'étais arrivé à ce raisonnement mais les SI(ET permettaient de faire le taff de prise en compte des palier dans le calcul d'une entrée jusqu'à ce que je me rende compte que ça foire sur le dernier palier.
J'ai beau y réfléchir, ça me semble infaisable... Au pire je me mettrais une mise en forme conditionnelle à 100 000 et je ferais à la main la transition du dernier palier..
Dans ce cas, tu calcules une fois pour 95000 et une fois pour (95000+10000)=105000 et tu fais la soustraction.
Je peux l'intégrer à ton projet, mais j'ai du mal à m'y retrouver dans les différentes colonnes. Mais si difficulté de ton côté, je le ferai.
Comme tu le dis, ces formules sont parfois tellement complexes à mettre au point, que je préfère partir sur une formule quasi-standard de calcul au seil/plafond genre calcul d'impôts faite une fois pour toutes et paramétrable. Cela dit j'ai quand même mis une demi-heure à l'établir ce matin.
Bien joué, ça fonctionne ! J'ai réussi à l'intégrer dans mon projet sans encombres.
Je te remercie pour la réflexion et la solution !
Bon, par contre j'ai du mal à comprendre ta formule ça m'ennuie un peu. De ce que je peux lire sur la SOMMEPROD (https://www.excel-pratique.com/fr/fonctions/sommeprod), elle remplace une formule plus longue qui voudrait ajouter la somme de plusieurs produits.
Dans ta formule, tu as ajouté des conditions sur les tranches. J'use une dernière fois de ta science pour comprendre, dis moi si je raconte une connerie :
A2>TRANCHES!$A$2:$A$5 : Pour que la formule fonctionne, tu poses la condition que A2 doit toujours être supérieur à ce qui est prévu dans les tranches (En gros si on avait prévu un début des tranches à 100 plutôt qu'à 0, si A2 est de 50, la formule ne fonctionne pas). Partant de ce postulat que A2 est dans les clous, il va être multiplié
(A3-TRANCHES!$A$2:$A$5) : Je n'arrive pas à comprendre ce passage, pourquoi on va soustraire les tranches à A3 ?
(TRANCHES!$C$2:$C$5)-(TRANCHES!$C$1:$C$4) : Là il s'agit de la condition, je pense avoir capté !
SOMMEPROD a ici 2 termes :
A-
le premier terme est constitué d'un produit de 2 facteurs
- le second facteur qui va prendre l'écart entre la valeur et chacun des paramètres qui définissent les tranches,
(A2-TRANCHES!$A$2:$A$5) - mais que si la valeur est supérieure (le premier facteur vaudra alors 0 et non 1 et annulera le second facteur)
(A2>TRANCHES!$A$2:$A$5)
(A2>TRANCHES!$A$2:$A$5)*(A2-TRANCHES!$A$2:$A$5)B-
celui qui fait le différence des taux d'une tranche à l'autre,
(TRANCHES!$C$2:$C$5)-(TRANCHES!$C$1:$C$4)N'oublie pas de clore ce fil de discussion si c'est ok en cliquant sur
et conserve la formule, cela peut servir (par exemple calcul des impôts)
Au top, j'y vois plus clair.
Merci pour tes réponses et bonne journée !