frais sur volumes - formule avec mois de SI!

[happymarmotte]

bonsoir
je dois faire des calculs d`estimation de marge et j`ai pleins de parametres (frais fixe, frais variables, frais variable selon nombre de produits...) bref une vrai usine a gaz! (pas du gaz hilarant! )

j`ai des cas ou plus le volume d`affaire augmente, moins ca coute cher a produire/acheter (normal).

le probleme est que parfois j`ai 4 a 6 niveau de frais sur volume
bref pour 1000 les frais sont de 25%, les 1000 suivant j`ai des frais a 20%...
quand je fais mon calcul de frais sur volume, je me retrouve avec une formule enorme avec pleins de "si".
si volume<1000 alors prendre montant x 25%
si volume >1000 and < 2000, alors prendre 25% sur les 1000 premiers et 20% sur le reste...
j`ai fait un exemple qui explique.

existe t`il une formule autre que SI qui fasse le calcul souhaite ? ou une methode de presentations differente?

en fait je voudrais definir un format avec tous mes parametres, taper un volume, le nombre de produits... et avoir une marge estimee mais avant ca, faut que je simplifie mes formules excel car c`est illisible et mon objectif est que les gens qui vont potentiellement utiliser le file excel comprennent (ou du moins ne soient pas effrayes par la formule avec 6 "SI" ou plus).
Meme moi avec tous les SI j`ai peur de faire une erreur - et pourtant j`aime les SI


Merci pour votre aide

happymarmotte

 

[ERIC S]

Re : frais sur volumes - formule avec mois de SI!

Bonjour

ton tableau de pourcentage peut être utilisé comme une liste tarifaire voir fichier joint

tu peux peut-être utiliser ce principe.
Dans ton exemple, il y a pour moi un léger flou entre les notions de volume et de montant

ton calcul :
si volume >1000 and < 2000, alors prendre 25% sur les 1000 premiers et 20% sur le reste...
entrainera toujours des dificultés de compréhension, quelles que soient les formules

 

[Tibo]

Re : frais sur volumes - formule avec mois de SI!

Bonjour,

La présentation du tableau est effectivement source d'interprétation.

Je te propose deux formules (Base et Dérive) :

en B20 (Base) :

=SOMMEPROD((DECALER($A$33;1;0;EQUIV(B$16;$A$34:$A$37))-DECALER($A$33;0;0;EQUIV(
B$16;$A$34:$A$37)))*DECALER($B$33;1;0;EQUIV(B$16;$A$34:$A$37)))+(B$16-INDEX(
$A$34:$A$37;EQUIV(B$16;$A$34:$A$37)))*INDEX($B$34:$B$37;EQUIV(B$16;$A$34:$A$37)+1)

à recopier pour les cellules Base

en C20 (Dérive) :

=SOMMEPROD((DECALER($A$33;1;0;EQUIV(C$17;$A$34:$A$37))-DECALER($A$33;0;0;EQUIV(
C$17;$A$34:$A$37)))*DECALER($D$33;1;0;EQUIV(C$17;$A$34:$A$37)))+(C$17-INDEX(
$A$34:$A$37;EQUIV(C$17;$A$34:$A$37)))*INDEX($D$34:$D$37;EQUIV(C$17;$A$34:$A$37)+1)
+100

à recopier pour les cellules Dérive

Je te laisse tester

@+

 

[happymarmotte]

Re : frais sur volumes - formule avec mois de SI!

bonjour

je vais tester la formule lundi mais eric a pas bien compris le systeme de tranche.
Sur les 1er mille, faut appliquer les fees pour 1000 et entre 1000 et 2000, tu appliques le % defini entre 1000 et 2000 (en exces ) .
ce que tu fais avec decaler est d`appliquer le taux sur le montant total mais pas sur la partie superieure a 1000.

si tu as 1300, tu appliques 1000 a 25% et 300 a 20%.
sauf erreur Eric, toi tu me donnes la tranche 20%.
Hors je veux calculer les frais tel que j`ai 1000 a 25% (250) + 300 a 20% (60).

merci

je vais tester la formule de Tibo

hapymarmotte

 

[chris]

Re : frais sur volumes - formule avec mois de SI!

Bonjour

J'étais partie sur deux pistes et notamment : recalculer le pourcentage pour 1000, 2000, 3000, 5000 pour appliquer une formule comme celle d'Eric mais on se heurte au problème des quantités inférieures à mille d'une part et à la quantité située entre deux tranches : du coup la formule est aussi longue qu'en gardant tes pourcentages initiaux.

Ayant vu que Tibo avait posté, je n'avais pas mis mes cogitations, car même si je n'ai pas testé celles de Tibo (à qui je fais la bise) à première vue cela me semble correspondre à ta demande (et connaissant Tibo, cela le restera à seconde vue ).

 

[happymarmotte]

Re : frais sur volumes - formule avec mois de SI!

Bonsoir,

salut Chris. en effet, la formule de Tybo marche nickel.
il est fort ce Tibo!

merci a vous 3 pour votre aide.

Bonne nuit

happymarmotte