Afschrijvingen per maand verdelen

[Stef21]

Goedenavond allemaal,

Ik heb een Excel-probleem waar ik niet helemaal uit kom. Ik zal mijn best doen om het te verwoorden, maar voor de duidelijkheid heb ik ook een bestandje toegevoegd.
In kolom A wordt de inkoopdatum van een voertuig ingevuld. In kolom B wordt de verkoopdatum gezet en in kolom C zijn de afschrijvingen die in de tussentijd verwerkt moeten worden.
Dit betekent dat als de inkoopdatum op 28 februari is, de verkoopdatum op 4 juni en de afschrijving in deze periode €2000 is, dat over de maanden maart t/m juni in elke maand €500 afschrijvingen geboekt moeten worden. Ik wil hier een automatische formule van maken zodat achter de maanden (zie A14 tot A25) de betreffende afschrijving getoond wordt.

Mijn gedachtegang tot nu toe is de volgende: Als het jaar in kolom A gelijk is aan 2023, doe dan C1:C4 gedeeld door (Maand(C1:C4) minus Maand(B1:B4)). Daarop kwam ik op de volgende codes:

=SOMPRODUCT(C1:C4/(ALS(JAAR(A1:A4)="2023";MAAND(C1:C4)-MAAND(B1-B4);0)))
=SOMPRODUCT(C1:C4/(--(JAAR(A1:A4)="2023";MAAND(C1:C4)-MAAND(A1:A4)))

Code Nr1 heb ik het probleem dat je geen maand met meerdere cellen in de waarde mag zetten.
Code Nr2 is het probleem dat de maand in C1 wel eens 12 is en de maand in B12 ook (zie bijvoorbeeld C4), waardoor deze de fout "delen door 0!" geeft. Dit zou echter geen probleem op moeten leveren aangezien ik al eerder heb verteld aan de formule dat de formule alleen maar naar de cellen moet kijken als het jaar in A1:A4 2023 is, maar deze criteria pikt Excel pas later op tijdens het berekenen. Mij lijkt code 2 in ieder geval beter te werken, dus ik denk dat het deze wordt die verbeterd moet worden.

Ik heb niet alle details van de formule uitgewerkt aangezien ik al een groot deel wel werkend heb kunnen krijgen, maar bovenstaande is tot nu toe het enige probleem nog.

Als iemand mij hiermee zou kunnen helpen, dat zou ik heel fijn vinden!

Alvast bedankt!

MVG, Stef

 

[AlexCEL]

Hoe moeten maanden afgerond worden? Bijv. 10 maart. Moet er dan afschrijvingen in maart worden meegenomen of pas vanaf April? En bij 4 oktober? 28 februari?
Ik ga er nu vanuit dat als er een transactie is in een maand deze meegenomen moet worden in de afschrijvingstermijn.

In bijlage een optie met een iets andere opzet van je afschrijvingentabel... bekijk maar eens.

Als je afschrijvingen hebt over meerdere jaren moet e.e.a. uitgebreid worden.

En gebruik je office 365?

 

[Stef21]

Hallo Alex,

In de maand van de investering moeten er nog geen afschrijvingen worden verwerkt. De maand van de verkoopdatum moet er wel afgeschreven worden, ook al is het verkocht op de 1e van die maand.
De regel in C3 moet dus in september en oktober afgeschreven worden voor €250 per maand.

Op zich is deze opzet wel een leuke manier (ik kende deze formules nog niet), ik wil echter dat alle formules in B15:B26 worden geplaatst, aangezien de tabel dan makkelijker uitgebreid kan worden zonder dat de formule in E:Q telkens naar beneden gekopieerd moet worden. Heb je hier ook een manier voor?

Als je afschrijvingen hebt over meerdere jaren moet e.e.a. uitgebreid worden.

Dat weet ik, deze formules heb ik al dus heb ik deze voor het overzicht uit het voorbeeld gelaten.

Ik hoor het graag!

MVG, Stefan

 

[AlexCEL]

Jij wilde het in 1 formule:

=SOMPRODUCT(ALS.FOUT((C$2:C$5/(DATUMVERSCHIL(A$2:A$5;B$2:B$5;"m")+(DAG(B$2:B$5)<DAG(A$2:A$5))))/((B$2:B$5>=A15)*(B$2:B$5<=LAATSTE.DAG(A15;0))+((A$2:A$5<A15)*(B$2:B$5>LAATSTE.DAG(A15;0))));0)

Graag wel in A15:A26 datums neerzetten, dus 1-1-23, 1-2-23, 1-3-23 etc. Daarna eventueel een andere opmaak zodat alleen de namen van de maanden te zien zijn.

 

[Stef21]

Hallo Alex,

Hartelijk bedankt, de code werkt!

 

[AlexCEL]

In Excel 365 kan het nog korter genoteerd worden:

=LET(a;A$2:A$5;b;B$2:B$5;c;C$2:C$5;L;LAATSTE.DAG(A14;0);SOM(ALS.FOUT(c/(DATUMVERSCHIL(a;b;"m")+(DAG(b)<DAG(a)))/((b>=A14)*(b<=L)+(a<A14)*(b>L));0)))