hulp bij formule maken

[mariannevanh]

Op de site van de OV-chipkaart kun je een transactieoverzicht genereren van de gemaakte treinreizen.
Als je in dat overzicht ALLE transacties meeneemt, kun je zien hoe laat je in- en uitstapt.
Omdat ik een voordeelabonnement heb, wil ik graag weten of deze rendabel is.
Met dit overzicht wil ik aan de hand van de data (waarmee je dus kunt bepalen welke dag het is) en het tijdstip bepalen bepalen hoeveel korting ik heb ontvangen.
Wie kan mij helpen met het maken van een formule hiervoor.
Het (enigszins bewerkte) transactieoverzicht vind je in de bijlage.

 

[oeldere]

1e begin.

met de formule in de gele cellen wordt de dag berekend.

dit kan (voor anderen) vast korter, maar het werkt in ieder geval wel.

 

[robdgr]

@oeldere: de dagen kun je gewoon met de celeigenschappen op de datum in beeld brengen.

@ guido: wat zijn die kortingregels? Ik vind ze niet in je bestand.

 

[oeldere]

@rob

het in een aparte kolom zetten heeft als voordeel (vind ik) dat je deze data straks ook kunt gebruiken voor te filteren op dagen.

 

[mariannevanh]

@robdgr: de kortingspercentages voor mijn abonnement staan hier.

 

[oeldere]

dan kun je het beste eerst een tabel aanmaken waarin deze gegevens worden opgenomen.

vervolgens kun je met b.v. vert.zoeken deze waarden ophalen.

 

[robdgr]

Geldt het volgende ook?:

•60-plusser? Voor € 14 (2e klas) of € 39 (1e klas) extra 7 Keuzedagen erbij waarop u gratis reist

 

[mariannevanh]

Nee, ik ben nog geen 60-plusser.

 

[robdgr]

Guido,

Volgens mij met deze formule in K:

=ALS(OF(G2="check-in";ISLEEG(G2));"";ALS(OF(WEEKDAG(A2;2)>5;EN(WEEKDAG(A2;2)<5;OF(D2>TIJD(18;30;0);EN(D2>TIJD(9;0;0);D2<TIJD(16;0;0));D2<TIJD(6;30;0))));F2/60*40;F2/80*20))

Test je hem even op een groter bestand? Ik vermoed dat je hier het grootse deel afvangt: voldoende om te kijken of die kaart loont.

 

[mariannevanh]

Dankjewel voor deze opzet.
De korting wordt in Excel echter te ruim berekend. Alleen waar in kolom I "Altijd Voordeel (jaarabonnement)" staat vermeld mag in kolom K een korting staan.
Op het bedrag in kolom F is de korting al toegepast en dient de 100%-prijs berekent te worden.

 

[robdgr]

De laatste zin snap ik niet, omdat de verkregen korting wordt berekend en 100% de optelling van betaald en korting is. Met een kleine toevoeging geeft de korting de juiste situatie weer:

=ALS(OF(G2="check-in";ISLEEG(G2);[COLOR="red"][B]I2=0[/B][/COLOR]);"";ALS(OF(WEEKDAG(A2;2)>5;EN(WEEKDAG(A2;2)<5;OF(D2>TIJD(18;30;0);EN(D2>TIJD(9;0;0);D2<TIJD(16;0;0));D2<TIJD(6;30;0))));F2/60*40;F2/80*20))