XL 2019 Calcul d'une suite de conditions

Abdessadeq · 1 Novembre 2022

Bonjour, j'ai besoin, pour un ami, d'une formule qui calcul le résultat d'une suite de conditions applicable à une donnée émanante d'une cellule x:
je m'explique: voilà nous avons une cellule x qui contient une surface de terrain au m², nous voulons qu'une autre cellule y calcule un montant, en fonction du contenu de x mais selon des conditions données: ce dit montant doit se calculer de la manière suivante:
les conditions sont les suivantes :
C1) 0<surface<=40000 m² alors c'est un forfait de 10000 $
C2) 40.000 m²<surface<=100.000 m² alors c'est C1 + PLAFOND (Surface - 40000)/10000*2500$.
C3) 100.000 m²<surface<=200.000 m² alors c'est C1 + 6 * 2500 $ + PLAFOND (Surface - 100000)/10000*2000$.
C4) 200.000 m²<surface<=500.000 m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + PLAFOND (Surface - 200000)/10000*1700$.
C5) 500.000 m²< surface<=1million m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + 30 * 1700 $ + PLAFOND (Surface - 500000)/10000*1200$.
C6) 1million m²<surface<=2millions m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + 30 * 1700 $ + 50 * 1200 + PLAFOND (Surface - 1million m²)/10000*1000$.
C7) 2millions m²<surface<=4millions m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + 30 * 1700 $ + 50 * 1200$ + 100 * 1000$ + PLAFOND( Surface - 2millions m²)/10000 * 800$.
C8) 4millions m²<surface<=6millions m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + 30 * 1700 $ + 50 * 1200$ + 100 * 1000$ + 200 * 800$ +PLAFOND(surface - 4millions m²)/10000 * 600 $.
C9) surface>6.000.000 m² alors c'est C1+ 6 * 2500 $ + 10 * 2000 $ + 30 * 1700 $ + 50 * 1200$ + 100 * 1000$ + 200 * 800$ + 200 * 600$ + PLAFOND( Surface - 6millions m²)/10000 * 500$.
Merci d'avance pour votre retour.

JHA · 1 Novembre 2022

Bonjour à tous

Pas certain de tout comprendre.

JHA

Dranreb · 1 Novembre 2022

Bonjour.
Vous auriez intérêt à mettre ce tarif dans un tableau, puis à déterminer les tranches dans une colonne intermédiaire pour ne pas répéter l'expression 3 fois dans la formule.

Dranreb · 1 Novembre 2022

Si vous ne voulez pas de tableau ni de colonne intermédiaire, cette formule donnent le même résultat :

Code:

=MAX(MIN(($B24-{40000;100000;200000;500000;1000000;2000000;4000000;6000000})*{0,25;0,2;0,17;0,12;0,1;0,08;0,06;0,05}+{10000;25000;45000;96000;156000;256000;416000;536000});10000)

Dranreb · 2 Novembre 2022

Bonjour.
Une autre formule légèrement plus courte :

Code:

=MIN({0;5000;11000;36000;56000;96000;176000;236000}+MAX(40000;$B23)*{0,25;0,2;0,17;0,12;0,1;0,08;0,06;0,05})

Encore plus court :

Code:

=MIN({0;5;11;36;56;96;176;236}*1000+MAX(40000;$B23)*{25;20;17;12;10;8;6;5}/100)

Abdessadeq · 2 Novembre 2022

Dranreb à dit:
Si vous ne voulez pas de tableau ni de colonne intermédiaire, cette formule donnent le même résultat :

Code:

=MAX(MIN(($B24-{40000;100000;200000;500000;1000000;2000000;4000000;6000000})*{0,25;0,2;0,17;0,12;0,1;0,08;0,06;0,05}+{10000;25000;45000;96000;156000;256000;416000;536000});10000)

Bonjour et Merci beaucoup pour votre retour, bon et effectivement j'essaie d'éviter les tableau et les colonnes intermédiaires, j'ai essayer d'intégrer la belle formule que vous m'avez proposé elle ne m'a pas donné le bon résultat, je vais essayer de vous expliquer encore plus mon besoin, voir alors la photo attachée à ce message pour bien comprendre le but.
Salutations

xUpsilon · 2 Novembre 2022

Bonjour le fil, @Abdessadeq

Rapide passage pour vous rappeler la charte : joindre un fichier sera toujours fortement apprécié.
Un screen d'un fichier Excel, c'est comme placer l'objet que l'on souhaite dans une cage inaccessible : on essaye de voir ce qu'on cherche à atteindre, mais on ne peut pas, c'est rageant.

Bonne journée et courage aux collaborateurs qui vous consacrent du temps,

Dranreb · 2 Novembre 2022

679500, c'est la valeur donnée par ma formule pour 8870000 m²
Et elle donne 113421,36 pour 645178 m²
Par conséquent joignez votre fichier pour que je puisse trouver pourquoi votre capture d'écran montre autre chose.

Abdessadeq · 2 Novembre 2022

xUpsilon à dit:
Bonjour le fil, @Abdessadeq

Rapide passage pour vous rappeler la charte : joindre un fichier sera toujours fortement apprécié.
Un screen d'un fichier Excel, c'est comme placer l'objet que l'on souhaite dans une cage inaccessible : on essaye de voir ce qu'on cherche à atteindre, mais on ne peut pas, c'est rageant.

Bonne journée et courage aux collaborateurs qui vous consacrent du temps,

je m'excuse xUpsilon, le fichier a beaucoup de liaisons et liens avec d'autres classeurs et si je le télécharge ici il risque de ne pas garder ces liaisons et par suite vous verrez les cellules vides ou #valeur#.

Dranreb · 2 Novembre 2022

Alors constatez mes dires sur mon classeur.

Abdessadeq · 2 Novembre 2022

Dranreb à dit:
679500, c'est la valeur donnée par ma formule pour 8870000 m²
Et elle donne 113421,36 pour 645178 m²
Par conséquent joignez votre fichier pour que je puisse trouver pourquoi votre capture d'écran montre autre chose.

ouiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii, ça a marché, merci beaucoup Dranreb,

Abdessadeq · 2 Novembre 2022

Abdessadeq à dit:
ouiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii, ça a marché, merci beaucoup Dranreb,

j'ai juste demander devant $B24 le plafonnement : PLAFOND($B24;10000), et j'ai insérer devant toute la formule la logique "" SI"" : =SI($B24>0;votre formule;0) pour qu'elle me donne 0 au cas ou la cellule de donnée B24 est vide ou comporte 0, encore merci beaucoup

Abdessadeq · 2 Novembre 2022

Abdessadeq à dit:
j'ai juste demander devant $B24 le plafonnement : PLAFOND($B24;10000), et j'ai insérer devant toute la formule la logique "" SI"" : =SI($B24>0;votre formule;0) pour qu'elle me donne 0 au cas ou la cellule de donnée B24 est vide ou comporte 0, encore merci beaucoup

parceque si la B24 n'affiche rien la cellule de calcul affiche 10000.

Dranreb · 2 Novembre 2022

Et peut-on savoir pourquoi votre capture d'écran montrait autre chose ?

Abdessadeq · 2 Novembre 2022

Dranreb à dit:
Et peut-on savoir pourquoi votre capture d'écran montrait autre chose ?

si vous parlez du chiffre 679000 c'est le faux chiffre calculé par une formule fausse, le vrai est 104000 juste à droite mais ce chiffre cumule les résultats des cellules qui lui sont à droite, c'est pour cela que je vous ai demandé une formule qui calcule directement de la cellule source et éviter de cumuler plusieurs cellules d'une plage.

XL 2019 Calcul d'une suite de conditions

Abdessadeq

XLDnaute Nouveau

JHA

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Pièces jointes

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Abdessadeq

XLDnaute Nouveau

Pièces jointes

xUpsilon

XLDnaute Accro

Dranreb

XLDnaute Barbatruc

Abdessadeq

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Pièces jointes

Abdessadeq

XLDnaute Nouveau

Abdessadeq

XLDnaute Nouveau

Abdessadeq

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Abdessadeq

XLDnaute Nouveau

Discussions similaires