Toon posts:

EXCEL Teriefsberekening afhankelijk van ingegeven datumreeks

Pagina: 1
Acties:

Vraag


  • Eric H
  • Registratie: April 2009
  • Laatst online: 29-01 19:09
Mijn vraag

Voor reserveringen wil ik Excel laten uitrekenen wat de kosten zijn van boekingen afhankelijk van of de boeking in het hoog- laag- of middenseizoen plaats vindt.

Ik heb in een Excel blad diverse van-tot reeksen gedefinieerd met daarachter het geldende tarief voor die periode.
Kolom A = datum vanaf
Kolom B = tot en met datum
Kolom C = tarief voor die periode

In een ander tabblad noteer ik :
Kolom A : vanaf datum reservering
Kolom B : tot en met datum reservering

Vervolgens wil ik dan dat in Kolom C uitgerekend wordt wat de kosten zijn voor die periode.
Wat het wellicht lastig maakt is dat reserveringen kunnen doorlopen in verschillende periodes.

Oftewel een boeking begint in het middenseizoen (met daarbij behorend tarief) maar loopt door tot een datum in het hoogseizoen (met daarbij dus een ander tarief)

Misschien denk ik te moeilijk, maar ik kom er zo 1,2,3 niet uit.
...

Relevante software en hardware die ik gebruik
Excel
...

Wat ik al gevonden of geprobeerd heb
Ik heb al een Google poging gedaan maar heb moeite met de juiste zoekterm.
Tevens heb ik geprobeerd om het met een ALS functie te doen, maar hier kwam ik ook niet uit.

...

Eric, Signing off !!

Beste antwoord (via Eric H op 18-01-2023 13:50)


  • Patrick_6369
  • Registratie: April 2010
  • Laatst online: 12:28
Eric H schreef op woensdag 18 januari 2023 @ 09:45:
[...]Of ik begrijp gewoon niet wat je bedoelt met hulpkolommen en hoe deze toe te passen.
Om het dan maar even helemaal uit te werken met een fictief voorbeeld, op basis van tarieven die exact gelijklopen met de maanden en een reservering van 24 januari tot en met 7 maart. Dan krijg je een sheet die er in de basis als volgt uit ziet:



Achter de schermen zitten deze formules er dan achter:
https://tweakers.net/i/7JFqa8TE7c9ZaKiWd9WnIIX0n-g=/800x/filters:strip_exif()/f/image/MHWK3U3MvPi5fVMzSmxkEEaD.png?f=fotoalbum_large

Hier zou een handtekening kunnen staan.

Alle reacties


  • Sirhc_95
  • Registratie: December 2012
  • Laatst online: 29-01 21:57
Er zijn natuurlijk meerdere wegen die naar Rome leiden, maar de overgang tussen het midden- en hoogseizoen is in principe toch altijd op een vaste datum voor een reservering van een x aantal weken?

Volgens mij zou je dan met de formule DATUMVERSCHIL het aantal dagen voor en na de datum waarop het tarief wijzigt kunnen berekenen en vervolgens deze aantallen dagen dan weer met de bijbehorende tarieven kunnen vermenigvuldigen? Dit zou zelfs in een grote formule kunnen per regel, dan heb je aan een tabblad genoeg.

  • Eric H
  • Registratie: April 2009
  • Laatst online: 29-01 19:09
Probleem is alleen dat de seizoensopbouw wisselend is en door elkaar loopt.

Dus in het ene blad heb ik de seizoenen met de daarbij behorende periodes gespecificeerd :

Kolom A. Kolom B. Kolom C.
van. Tot en met. Tarief.
1-1-2023. 20-4-2023. 40
21-4-2023. 14-5-2023. 75
15-5-2023. 31-5-2023. 40
1-6-2023. 6-7-2023. 55
7-7-2023 3-9-2023. 75
4-9-2023. 12-10-2023. 55
13-10-2023 28-10-2023 75
29-10-2023 31-12-2023 40

En in een ander blad noteer ik de reserveringen :
Kolom A. Kolom B. Kolom C. Kolom D
Wie. Van datum. Tot en met datum. Te betalen bedrag.

Dus de formule die in "Te betalen bedrag" moet komen, moet dus rekening houden met het feit dat een klant een reservering kan doen die bijvoorbeeld bestaat uit 3 dagen tegen tarief 55 en 6 dagen tarief 75.
Bijvoorbeeld bij een reservering van 4-7-2023 tot en met 12-7-2023.

Eric, Signing off !!


  • OdeB
  • Registratie: April 2020
  • Laatst online: 29-01 16:25
Wellicht dat het met onderstaande formule eenvoudig is op te lossen:
=DAGEN(DATUM SEIZOEN 1;DATUM RESERVERING 1)*BIJBEHORENDE WAARDE +DAGEN(DATUM RESERVERING 2; DATUM SEIZOEN 2)*BIJBEHORENDE WAARDE

[Voor 11% gewijzigd door OdeB op 18-01-2023 00:31]


  • Eric H
  • Registratie: April 2009
  • Laatst online: 29-01 19:09
OdeB schreef op woensdag 18 januari 2023 @ 00:30:
Wellicht dat het met onderstaande formule eenvoudig is op te lossen:
=DAGEN(DATUM SEIZOEN 1;DATUM RESERVERING 1)*BIJBEHORENDE WAARDE +DAGEN(DATUM RESERVERING 2; DATUM SEIZOEN 2)*BIJBEHORENDE WAARDE
Dat lijkt me ook niet goed te gaan aangezien ik niet zie hoe deze formule rekening houdt met overgang van seizoensprijs wanneer de vanaf en tot en met datum in verschillende seizoenen liggen.

Eric, Signing off !!


  • Patrick_6369
  • Registratie: April 2010
  • Laatst online: 12:28
Ik zou met hulpkolommen werken. Eén hulpkolom voor ieder tarief. In iedere hulpkolom bereken je met de tip van @Sirhc_95 hoeveel dagen er in die tariegroep vallen. Daarna is het een koud kunstje van vermenigvuldigen en optellen.

Hier zou een handtekening kunnen staan.


  • Lustucru
  • Registratie: Januari 2004
  • Niet online

Lustucru

26 03 2016

Om het eerst op te zetten met hulpkolommen is een goed plan zodat je doorgrondt hoe de berekening in elkaar zit. In principe kun je een overlap berekenen door van de kleinste van einde_periode/einde reservering de grootste van begin-periode/begin-reservering af te trekken en het eindresultaat te controleren op groter dan 0.

Je krijgt dan iets als MAX(0,MIN(pe,re)-MAX(pb,rb)). Als dat goed werkt kun je het met een matrixformule optimaliseren tot één korte formule voor alle periodes.

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland


  • Eric H
  • Registratie: April 2009
  • Laatst online: 29-01 19:09
Patrick_6369 schreef op woensdag 18 januari 2023 @ 08:31:
Ik zou met hulpkolommen werken. Eén hulpkolom voor ieder tarief. In iedere hulpkolom bereken je met de tip van @Sirhc_95 hoeveel dagen er in die tariegroep vallen. Daarna is het een koud kunstje van vermenigvuldigen en optellen.
Maar daarmee ondervang ik toch niet de reserveringen die een overlap hebben in verschillende tariefsperiodes ?
Dan weet ik alleen hoeveel dagen er in een bepaalde periode zitten en tegen welk tarief er berekend moet worden.

Maar in het door mij gestelde voorbeeld kun je niet rekenen met 1 tarief voor een totaalperiode die over twee tariefsperiodes loopt.
Oftewel, klant maakt reservering van 3 weken waarvan 10 dagen onder tarief a vallen en 11 dagen onder tarief b.

Of ik begrijp gewoon niet wat je bedoelt met hulpkolommen en hoe deze toe te passen.

Eric, Signing off !!


Acties:
  • Beste antwoord
  • +1Henk 'm!

  • Patrick_6369
  • Registratie: April 2010
  • Laatst online: 12:28
Eric H schreef op woensdag 18 januari 2023 @ 09:45:
[...]Of ik begrijp gewoon niet wat je bedoelt met hulpkolommen en hoe deze toe te passen.
Om het dan maar even helemaal uit te werken met een fictief voorbeeld, op basis van tarieven die exact gelijklopen met de maanden en een reservering van 24 januari tot en met 7 maart. Dan krijg je een sheet die er in de basis als volgt uit ziet:



Achter de schermen zitten deze formules er dan achter:
https://tweakers.net/i/7JFqa8TE7c9ZaKiWd9WnIIX0n-g=/800x/filters:strip_exif()/f/image/MHWK3U3MvPi5fVMzSmxkEEaD.png?f=fotoalbum_large

Hier zou een handtekening kunnen staan.


  • Eric H
  • Registratie: April 2009
  • Laatst online: 29-01 19:09
Ahhhh, er gaat hier een lampje branden.....

Wat je eigenlijk doet is een soort van "tussenberekening"
Nu is de uitdaging nog dat ik drie tarieven heb, maar die komen terug in meerdere delen van het jaar.
( zie mijn eerdere post)

Maar ik ga wel kijken of ik hier omheen kan werken.

EDIT:

Helaas, maar dit lijkt niet te werken.


Ik zet de 8 tariefsperiodes in een tabel (tarief 1 t/m 8 met de daarbij behorende periodes en waardes)
Vervolgens zet ik datum "van" op 8-4-2023 en datum "tot en met" op 15-4-2023

Bij berekening van Tarief1 krijg ik als uitkomst 8 (dat klopt dus)
Maar wanneer ik Tarief2 wil uitrekenen krijg ik -5 te zien....
------------------------------------------------------------------------------------------------
EDIT2:
Volgens mij heb ik dit opgelost met het volgende:
=MAX(0;jouw formule) toegevoegd.
Dit lijkt goed te gaan!

[Voor 53% gewijzigd door Eric H op 18-01-2023 13:21]

Eric, Signing off !!


  • Lustucru
  • Registratie: Januari 2004
  • Niet online

Lustucru

26 03 2016

Eric H schreef op woensdag 18 januari 2023 @ 12:10:
Ahhhh, er gaat hier een lampje branden.....

EDIT2:
Volgens mij heb ik dit opgelost met het volgende:
=MAX(0;jouw formule) toegevoegd.
Dit lijkt goed te gaan!
En dan heb je dus wat hier ook al staat: Lustucru in "EXCEL Teriefsberekening afhankelijk van ingegeven datumreeks" :p

De oever waar we niet zijn noemen wij de overkant / Die wordt dan deze kant zodra we daar zijn aangeland

Pagina: 1


Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee