XL pour MAC Calcul par tranche en fonction du CA cumulé

[Josselin]

Bonjour,

Je tiens une agence immobilière, et je rémunère mes agents de 50% à 80% de leur CA HT.
0 > 50000€ = 50%
50000€ > 100000€ = 60%
100000€ > 150000€ = 70%
> 150000€ = 80%

La rémunération change à l'euros près, exemple :
si le commercial à 45000€ de CA HT cumulé et qu'il signe une vente à 10000€, il a donc 5000€ à 50% et 5000€ à 60%...

Pour les aider à tenir leurs comptes et pour faciliter la facturation j'ai voulu formaliser ça.
Et je suis paumé.

Car le calcul doit être par tranche (donc ca avec de simples SI je m'en serais sorti) mais aussi tenir compte de la somme cumulée de CA et répartir à l'euros près la partie à 50, 60, 70... comme dans l'exemple plus haut.

Quelqu'un saurait-il m'aider ? Dans le fichier ci-joint vous trouverez mon ébauche. Il y a déjà quelques conditions (notamment sur la partie de com sur laquelle ils seront rémunéré : la totalité s'ils ont trouvé le vendeur et l'acheteur, ou moitié s'ils n'ont trouvé que vendeur ou que acheteur)...

J'espere que c'est lisible...

Merci d'avance !

 

[Bebere]

bonjour
Josselin à coller en i11 et recopier vers le bas

=SI([@[Com HT]]=0;0;SI([@[Com HT]]<$I$5;[@[Com HT]]*$J$4;SI(ET([@[Com HT]]>=$I$5;[@[Com HT]]<$I$6);[@[Com HT]]*$J$5;SI(ET([@[Com HT]]>=$I$6;[@[Com HT]]<$I$7);[@[Com HT]]*$J$6;SI([@[Com HT]]>=$I$7;[@[Com HT]]*$J$7)))))

 

[Dranreb]

Bonjour.
Je dirais plutôt :

=MAX([@[Com HT]]*0,5;[@[Com HT]]*0,6-5000;[@[Com HT]]*0,7-15000;[@[Com HT]]*0,8-30000)

Mais je peux me tromper. À vérifier sur un graphique si tout se raccorde bien, au bonnes limites de tranches.

Pour moi ça a l'air bon :

 

[Josselin]

Bonjour,

Tout d'abord, merci pour vos réponses, vous êtes des génies XD

J'ai essayé vos deux solutions, le soucis c'est que dans l'exemple présent :
- l'agent a déjà touché 3 041,67 € et 6 666,67 € de com soit 9 708,34 €
- il encaisse une com de 83 333,33 € (totalement faux, mais au moins on passe des "tranches" et on voit ce qu'il se passe)

Donc il doit être rémunéré ainsi :
(50000-9708,34)*0,5+(83333,33-(50000-9708,34))*0,6 = 40 291,66*0,5 (ce qu'il manquait pour arriver au premier palier) et 43 041,67*0,6 (ce qui dépasse le 1er palier) = 45 970,832 €

Et aucune de vos formules ne donnent le bon résultat :/ ?

(j'ai remarqué que si je remplis plus bas un nouveau CA par contre là vos formules donnent le même résultat)

 

[Dranreb]

Non mais alors ajoutez un colonne de cumul jusqu'à la ligne, faite le calcul sur ce cumul et retranchez de cette commission totale le cumul des commission touchées plus haut. Sinon, je ne sais pas.

 

[Josselin]

Bonsoir,

Même avec une ligne de com intermédiaire (en gros le cumul des com déjà perçues avant celle concernée), je ne trouve pas la logique pour dire "compte 50% pour la partie de com <50000 et 60% pour le 50/100 et 70%...."
J'ai fait plusieurs essais merdiques, ne maîtrisant pas masses de formules ><

bref, je suis perdu !
Je remets le fichier avec les com intermédiaires, si quelqu'un à une idée à proposer, merci d'avance

 

[Dranreb]

Ce que je voyais c'est :
En I10 :

Cumul HT

En I11, à propager sur 38 lignes :

=SOMME(G$11:G11)

En J11 :

=MAX([@[Cumul HT]]*$J$4;[@[Cumul HT]]*$J$5-5000;[@[Cumul HT]]*$J$6-15000;[@[Cumul HT]]*$J$7-30000)

En J12, à propager sur 37 lignes :

=MAX([@[Cumul HT]]*$J$4;[@[Cumul HT]]*$J$5-5000;[@[Cumul HT]]*$J$6-15000;[@[Cumul HT]]*$J$7-30000)-SOMME(J$11:J11)

 

[Josselin]

Haaaaa... t'es un génie !!
Bon j'avoue que j'ai compris la logique mathématique mais que les formules m'ont perdues.
En tout cas merci beaucoup
Je saurais quel forum recommander si on me demande des stars du excel.

A une prochaine !