Waarden uit tab 1, berekenen met formule uit tab 2 en 3

[brianvdw]

Kan ik met de waarden uit tab 1, de formule uit tab 2 en 3 laten rekenen en zodoende tab E en F in laten vullen?

Of is hier geen automatische koppeling voor?

 

[Cobbe]

Ja dat kan natuurlijk wel, gewoon koppelen et vwalla.

Anders een voorbeeldbestand posten.

 

[brianvdw]

Bestand bijgevoegd

Ik kom er dus niet achter hoe. En dom, dacht dat ik dit al had gedaan. Bijgevoegd het bestand!

 

[VenA]

Lijkt mij dezelfde onbegrijpelijke vraag als hier.http://www.helpmij.nl/forum/showthr...it-tab-1-laten-rekenen-met-formules-uit-tab-2

Dus je zal even in duidelijke taal uit moet leggen wat je nu precies wilt.

 

[Cobbe]

Voor de intrestvoet kan je deze gebruiken:

=ALS.FOUT(VERT.ZOEKEN($B3;Handelsrente!$A$4:$B$18;2;1);"")

voor de incassokosten zou je beter een tabel opstellen zodat je die waarden ook kan ophalen met een Vert.Zoekenformule.

 

[brianvdw]

Lijkt mij dezelfde onbegrijpelijke vraag als hier.http://www.helpmij.nl/forum/showthr...it-tab-1-laten-rekenen-met-formules-uit-tab-2

Dus je zal even in duidelijke taal uit moet leggen wat je nu precies wilt.

Hoi VenA, ik heb het met dit bericht duidelijker proberen te maken, zou niet echt weten hoe ik het beter moet beschrijven, maar hier een poging.

Ik heb twee formules in twee verschillende tabs (tab 2 en 3), waar er data ingevuld moet worden voor de uitkomst. Ik wil graag een koppeling zodat ik niet telkens zelf de waardes moet invullen, maar dat dit automatisch gebeurd en ik de waardes in tab 1 dankzij deze koppeling zichtbaar krijg.

 

[brianvdw]

Voor de intrestvoet kan je deze gebruiken:

=ALS.FOUT(VERT.ZOEKEN($B3;Handelsrente!$A$4:$B$18;2;1);"")

voor de incassokosten zou je beter een tabel opstellen zodat je die waarden ook kan ophalen met een Vert.Zoekenformule.

Ik krijg er als waarde met deze formule uit 0,08 en het antwoord moet zijn 2,22. Maar ik ben wel al een stukje verder hiermee, dank je voor de input.

 

[VenA]

Wil je svp niet quoten?

Hoe kom je aan 2,2 als uitkomst? De rente is toch 8% in 2016?

 

[brianvdw]

Het rentepercentage wordt berekend aan de hand van het aantal dagen na vervaldatum.

De formule is:
=ALS.FOUT(VERT.ZOEKEN(F24;A4:B18;2;WAAR)*(DATUMVERSCHIL(F24;H24;"d")/365);"")

Ingevulde gegevens:
Vervaldatum: 31-12-2016
Hoofdsom: 55,90

Uitkomst:
Te betalen rente: € 0,01 over de periode van 31-12-2016 tot 1-1-2017
Te betalen rente: € 2,21 over de periode van 1-1-2017 tot 30-6-2017
Totaal: 2,22

Zoals je zelf wel kan bedenken heb ik de formule ook niet alleen bedacht, helaas ben ik nog niet zo gevorderd in Excel. Wel heb ik de formule gecontroleerd aan de hand van online rekenmodellen en hij klopt.

 

[roeljongman]

Nou ja brian, misschien moet je niet proberen uit te leggen hoe het in excel moet, want dat weet je niet. wat jij met je sheet wil bereiken is dat je rentekosten en incassokosten wilt kunnen berekenen voor facturen die niet op tijd betaald zijn.
De incassokosten zijn het simpelst op basis van het factuurbedrag (hoofdsom) is dit een bepaald percentage van de hoofdsom met altijd een minimum van 40 euro.

in cel f3 zet je daarvoor de volgende formule

=MAX(40;VERT.ZOEKEN(C3;Incassokosten!$A$15:$C$19;3;WAAR)*C3)

De renteberekening is een ander verhaal daar ben ik ook niet uitgekomen maar kan het wel beschrijven voor je.
Maar de methodiek is als volgt. Zolang er niet betaald is moet er rente gerekend worden over het orgineel factuurbedrag. het percentage staat in tabblad Handelsrente.
Complicerende factor is dat deze handelsrente wettelijk is vastgesteld en elk halfjaar wordt dit percentage aangepast (hoewel soms ongewijzigd gelaten)
De rente berekening bestaat dus uit het bepalen van het aantal dagen dat er na vervaldatum nog niet is betaald
en bij de renteberekening moet je er rekening mee houden dat de goede percentages wordt toegepast op het tijdvak op basis van de wettelijk vastgestelde (maximum rente)

de formule wordt dus eigenlijk iets in de trant van
* bepaal in welke renteperiodes de factuur open staat
* bereken de rente per rentetijdvak.
- het eerste tijdvak loopt vanaf de vervaldatum tot 1 juli of 1 jan.
- tussenliggende tijdvakken zijn steeds een halfjaar (wel met echte dagen rekenen).
- het laatste tijdvak wordt bepaald door de standaard begindatum 1 januari of 1 juli en de feitelijke betaaldatum,
- zolang er niet betaald is dan geld de datum van vandaag als einddatum van het laatste tijdvak

 

[brianvdw]

Hoi Roel, Ik reageer gewoon naar mijn beste kunnen en weten. Ik vraag niet voor niets hulp, maar thnx voor je input. Kom een stuk verder met je formule, maar ook hier klopt het uiteindelijke antwoord niet helemaal.

Op het moment dat het factuurbedrag bijv. 9000 wordt, dan is de juiste uitkomst 825. Met jouw formule wordt dit 450,-. Ik zal hier verder mee stoeien, mocht jij of iemand anders de juiste aanpassing voor mij kan doen, heel graag.


De renteberekening is overigens al juist, hij moet alleen ingevuld worden en de waarden moeten zichtbaar worden in het eerste tabblad.

 

[roeljongman]

o ja sjips, die incassokosten is ook een gestaffelde berekening.. ik was abuis dat het altijd een percentage van de hoofdsom was.. dus ook een stuk ingewikkelder functie..

dat maakt de formules ook lastig.. je hebt nu aparte tabbladen nodig met meerdere berekenstappen. dat is vrij lastig in een formule te vangen.

eens kijken of ik met VBA en de bestaande tabbladen iets kan..

 

[brianvdw]

Hoi Roel,

Bedankt voor je hulp tot zo ver, ik heb er ook mee gestoeid, maar het gaat mijn pet te boven helaas.

 

[Timshel]

Incassokosten, matrixformule in F3 (ingeven met CTRL SHIFT ENTER):

=MAX(40;SOM(ALS.FOUT(WORTEL(ALS(D3<Incassokosten!$B$15:$B$19;D3-Incassokosten!$A$15:$A$19;Incassokosten!$B$15:$B$19-Incassokosten!$A$15:$A$19));0)^2*Incassokosten!$C$15:$C$19))

Rente stoei ik nog mee.

 

[roeljongman]

Hallo brainw,

ik geef het niet zo snel op.. Ik gebruik nu gewoon jou berekeningstabbladen en vul met hulp van een macro de waarden in op tabblad facturen
enige waar je een oplossing voor moet vinden is of incassokosten met of zonder btw berekend moeten worden. Ik heb het opgelost door een kolom ex btw en incl btw te maken.

Het is nu letterlijk een druk op de knop om achter de lijst uitkomst van de berekeningen te krijgen.
Er worden waarden geplakt en de macro gaat feitelijk gewoon regel voor regel af en vult de velden die jij nu met de hand invult om daarna het bedrag over te nemen
alleen de macro is ietsje sneller als jij

 

[brianvdw]

Ik vind jullie echt helden , niet normaal wat jullie allemaal kunnen met Excel.

@Roeljongman - je oplossing is echt heel vet , ik ga kijken of ik het voor elkaar om deze macro is mijn originele Excelbestand te krijgen (een stuk groter en meer data, heb het even ingekort ter verduidelijking). De incassokosten mogen zonder BTW, maar die extra kolom met BTW is alleen maar handig!

@Timshel - jouw oplossing is voor mij op dit moment het makkelijkste omdat ik nog niet precies weet hoe ik de macro's ga overbrengen, dus ben ik ook heel blij mee. Mocht je er uitkomen met de rente zou het ook heel fijn zijn. Vind het namelijk sowieso heel fijn om een check uit te kunnen voeren altijd gezien de foutgevoeligheid met bedragen.

 

[Timshel]

De rente heb ik niet met een formule kunnen oplossen. Daarvoor is een UDF (User Defined Function, VBA dus) gemaakt in combinatie met een gedefinieerd bereik.

Function Rente(FacDate As Date, Amount As Double) As Double
    Dim Br, i As Long
    
    Rente = Amount
    Br = [Rentetabel].Resize([Rentetabel].Rows.Count + 1)
    Br(UBound(Br), 1) = Date
    For i = 2 To UBound(Br) - 1
        If FacDate <= Br(i + 1, 1) Then _
            Rente = Rente * (1 + Br(i, 2) * (Br(i + 1, 1) - Application.Max(FacDate, Br(i, 1))) / 365)
    Next
    Rente = Rente - Amount
End Function

 

[brianvdw]

Hi Timshel,

Bedankt voor al jouw moeite. Helaas klopt de macro die je hebt opgegeven m.b.t. de rente niet. De rente bedragen zijn te hoog die er uitkomen, echter klopt de Marco van Roel wel. Ik denk dat ik met jullie beide oplossingen een heeel stuk geholpen ben. Ik ga het proberen toe te passen in het volledige Excelbestand. Zal het even laten weten of het mij lukt of niet.

In ieder geval bedankt!!

 

[Timshel]

Ik kan geen onjuiste uitkomsten ontdekken.
Op basis van een steekproef krijg ik met mijn UDF precies dezelfde uitkomsten als uit jouw rekensheet.
Waar zit het verschil?

 

[brianvdw]

Hoi Timshel,

Ben er nog even ingedoken en ik zie al wat er is gebeurd, je berekening is volledig accuraat, echter heb je als waarde de factuurdatum in plaats van de vervaldatum gepakt! Had ik misschien beter moeten beschrijven.

Maar de uitkomst klopt uiteindelijk indien de juiste waarde wordt meegenomen.