XL 2019 Calcul d'une suite de conditions

[Abdessadeq]

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]

Bonjour à tous

Pas certain de tout comprendre.

JHA

 

[Dranreb]

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]

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

=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]

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

=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 :

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

 

[Abdessadeq]

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

=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]

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]

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]

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]

Alors constatez mes dires sur mon classeur.

 

[Abdessadeq]

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]

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]

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]

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

 

[Abdessadeq]

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.