Berekening betaald voorschot bedrag voor een gedeelte van de maand

[M@rcel]

Hallo Help-Mij leden,

Ik zit momenteel pruttelen met een excel vraagstuk waar ik niet geheel uit kom.

Inleiding:
Ik heb een bestand van een aantal appartementen ( 16 stuks ). Deze worden verhuurd. Zij betalen een voorschot voor de servicekosten per maand. Wanneer de appartementen een volledig jaar verhuurd worden, dan is de berekening van het voorschot geen probleem ( 12 maanden x het voorschotbedrag ). De uitdaging ontstaat wanneer er gedurende het jaar een wisseling van huurder plaats vindt. Ik wil bepalen wat zij betalen tot het moment zij verhuizen.

Bereken manier
Het primaire systeem dat deze berekening maakt, maakt de volgende berekening: Bedrag per maand / aantal dagen in de betreffende maand ( in januari wordt het bedrag gedeeld door 31 dagen, in februari door 28 of 29 dagen enz. ). Hierdoor verschilt het bedrag per dag per maand enkele 1/1000e centen.

Geprobeerde oplossing
Ik heb samen met een collega gezocht naar een formule die dit berekent. Deze formule is te vinden in cel H20 ( Dit gaat goed, maar de cellen H19 en H21 geven een NB fout ).

Kunnen jullie mij een zetje in de goede richting zetten? Of dat ik iets over het hoofd zie. Ik zit nu op zo'n punt dat ik door de formules het bos niet meer zie.

Bijlage
Ik heb een bijlage gemaakt. De kolommen B+C zijn momenteel hard ingegeven, deze worden bepaald op basis van de ingangsdatum van het contract. Hier is een formule voor. Ik heb onder de tabel de bedragen gezet die het primaire systeem berekend ( dus wat de oplossing zou moeten zijn. ).

Alvast bedankt.

Bekijk bijlage verhuurde huizen.xlsx

 

[Cobbe]

Is deze berekening fair genoeg?

 

[M@rcel]

Hallo Cobbe,

Bedankt voor je reactie. Het komt wel in de buurt van wat ik hebben wil, maar nog niet helemaal. Je bestand heeft me nieuwe inzichten gegeven. De uitkomst van cel H20 moet écht € 0,74 zijn.

 

[Cobbe]

Dan laat je toch gewoon het afronden achterwege.

 

[dirktimm]

Berekening

Hallo

Misschien zo in cel H5 en naar beneden en naar rechts kopieeren:

=ALS(($E5=365);12*I$3;ALS(EN(MAAND($B5)=1;DAG($B5)=1);(MAAND($C5)-MAAND($B5))*I$3+(DAG($C5)*(VERT.ZOEKEN(MAAND($C5);dagen1;KOLOM()-4;0)));ALS(EN(MAAND($C5)=12;DAG($C5)=31);(MAAND($C5)-MAAND($B5))*I$3+((VERT.ZOEKEN(MAAND($B5);dagen1;2;0)-DAG($B5)+1)*(VERT.ZOEKEN(MAAND($B5);dagen1;KOLOM()-4;0)));(VERT.ZOEKEN(MAAND($B5);dagen1;KOLOM()-4;0)*((VERT.ZOEKEN(MAAND($B5);dagen1;2;0)-DAG($B5)+1))+(DAG($C5)*(VERT.ZOEKEN(MAAND($C5);dagen1;KOLOM()-4;0)))))))

Dagen1 iets aangepast t/m kolom E

Groet

 

[M@rcel]

Hallo Dirktimm,

Bedankt voor je formule. Ik heb je formule geplakt in cel h5 en gekopieerd naar beneden en opzij. Alleen krijg ik in cellen I19 t/m I21 #VERW! foutmelding. Kan dit liggen aan de verwijzing naar dagen1?

Waarom heb jij kolom extra gebruikt bij dagen 1?

EDIT
Oplossing voor de foutmelding gevonden. In de formule komt het volgende stukje formule voor:

KOLOM()-4

, wanneer ik deze formule kopieer naar kolom I dan moet de formule als volgt zijn:

KOLOM()-5

er moet wat gezocht worden in de kolom die 5 kolommen eerder ligt dan kolom I

 

[dirktimm]

Berekening

Hallo Marcel,

In kolom H en I moet in beide kolommen Kolom() - 4 staan.
Voor kolom H moet Vert.zoeken de waarde ophalen in de 4e kolom (kolom() "H"= 8 - 4 = 4) van het bereik uit dagen 1, = bereik, dus uit kolom E en
voor kolom I moet Vert.zoeken de waarde ophalen in de 5e kolom (kolom()"I" = 9 -4 = 5) van het bereik uit dagen 1 = bereik B:F, dus uit kolom F.

groet

Dirk

 

[M@rcel]

Heren, bedankt voor jullie hulp. Ik ben hiermee geholpen. Ik heb nog een paar kleine aanpassingen in de formule gedaan. Dit het met name te maken met de verwijzing naar de tabelmatrix van de verticaal zoeken formule.