Problème de calcul du coût total avec plusieurs forfaits : Excel ne prend pas le tarif le plus avantageux

Question

Bonjour à tous,

Je rencontre un problème avec un fichier Excel qui doit calculer automatiquement le coût de location d’une salle en fonction de la durée et des forfaits disponibles.

Le problème :

Pour les longues durées (par exemple 252,5 heures avec un coût horaire de 100 €), Excel m’affiche un total de 25 250 € en utilisant le forfait heure, alors que le tarif cumulé du forfait semaine (15000€) et du forfait week-end (5000€) total (20 000 €) est plus avantageux.

Comment corriger la formule pour que excel affiche toujours le tarif le plus avantageux quitte à cumuler deux tarifs ?

Merci d’avance pour votre aide !

Answer 1

Ok, 2016 ca diminue un peu les solutions 😰

Pour bien cerner la demande/réponse pour un tarif optimisé :

Si entre la date de début et de fin il y a un/des week-end, on va utiliser le tarif à 750 € pour ces jours, c'est bien ça ?

Apres si la date de début ou de fin sont sur un week-end, là ça se complique car plusieurs combinaison tarifaires existent :

Date de début :

• Samedi entre 9h et 13h => 750 (S+D)
• Samedi entre 14h et 17h => 200+330+450 (D) ou 450+450 (D) donc toujours 750 €
• Samedi entre 17h et 24h => 330 (S) + 450 (D) donc toujours 750 €
• Dimanche entre 0h et 1h => 330 (S) + 450 (D) donc toujours 750 €
• Dimanche entre 9h et 13h => 450 €
• Dimanche entre 14h et 24h =>220+330 ou 450 € donc 450 €
• Vendredi entre 9h et 17h => 350+450 +750 (S+D)
• Vendredi entre 17h et 24h => 450 +750 (S+D)

Il faudrait faire le même résonnement pour la date de fin.

Es-tu d'accord avec cette analyse ?

Answer 2

Je ne sais plus ce que doit être le résultat avec ça !

Rappel de la demande du 1er message : "le tarif le plus avantageux quitte à cumuler deux tarifs".

• N'est ce pas plus un outil pour sélectionner les tarifs "applicables" en fonction des dates et horaires de début/fin ?

• QUELLE EST LA VERSION D'EXCEL ? (là je peux pas faire plus pour avoir une réponse directe au moins à cette question)

Answer 3

Un début de réponse pour réflexion :

• J'ai considéré une version 365 ou 2021 (sinon la formule renvoie l'erreur #NOM!) .
• J'ignore la notion de demi journée, ignorant son fonctionnement.
• Une heure commencée est une heure facturée
• A moins de 2 heures de location, on prend le tarif heure (100 €).
• A partir de 2 h, même un jour de week-end, la tarification journée est plus intéressante (2h=200€<=>1j=100€ ou 170€ 🤪🤔 là j'ai un doute sur le barème !)
• J'ai considéré les jours fériés comme un tarif week-end (liste des jours fériés dans le tableau structuré Tableau1[Jours fériés]).
• Les cellules/plages utilisées sont toutes définies dans la 1ère ligne pour facilité la mise à jour.

=LET(dd;A2;hd;B2;df;C2;hf;D2;jf;Tableau1[Jours fériés];

h;ARRONDI.SUP((df+hf-dd-hd)*24;0);

js;NB.JOURS.OUVRES(dd;df;jf);

jw;df-dd-js+1;

SI(h<2;100;js100+jw170))

Answer 4

On a quelques infos en plus mais il en manque toujours.

• "la location c'est en 24heures" => c'est donc à la journée et non à l'heure !?
• Dans la formule je vois "Demi-journee", c'est avant/après 12h ou une durée de 4h ?
• Et une mystérieuse "Mini tranche" (par heure ?)
• Il faudrait avoir les "forfaits existants". J'ai tenté de les déduire de la formule :

100 /h
87.5 /demi-j
100 / jour sem
170 / jour week-end (edit)

• Comment gérer les jours fériés ?
• Quelle est ta version d'Office ?

Answer 5

Bonjour Marie-Charlotte,

Il manque beaucoup d'informations pour répondre !

• Un tarif à l'heure => pour 252,5 h, il me semble plus logique de compter 253 h (sinon c'est un tarif à la 1/2h 😁).
• 252.5h correspond à 15 000 € + 5 000 € mais comment deviner le tarif d'une semaine si on ne connais pas la durée d'une semaine pour toi (35 h ?) , idem pour les week-ends (j'ai interrogé une boulle de cristal, des os de poulet... sans succès 😁).
• Forfait semaine => Ici c'est des semaines de 5 jours semble t'il, doit on considérer que les locations commencent toujours un lundi (sinon doit on évaluer la semaine complète / heures réelles des jours "utilisés") ?
• Idem pour la dernière semaine.
• Idem si la location se termine le samedi.