• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

maandkosten berekenen d.m.v. tarief gekoppeld aan zone 1, 2 en 3

Status
Niet open voor verdere reacties.
M

MarkMarakus

Gebruiker
Lid geworden
25 mei 2020
Berichten
10
Beste forum leden en Excel experts!

Ik heb al vaak helmpij.nl bekeken maar dit is voor het eerst dat ik een echt specifieke vraag heb waar ikzelf niet uitkom. Hopelijk kunnen jullie mij helpen :)

Zie bijgevoegde Excel. Ik zou in kolom E graag de zone kosten hebben. Deze moeten berekend worden door de eerste 10.000 * tarief 1 te doen. Vervolgens moet de waarden tussen 10.001 en 50.000 * tarief 2 en boven de 50.000 * tarief 3.

Wanneer dit vanuit een totaal waarden moet komen is het geen probleem om dit in Excel op te lossen. Maar omdat het per maand berekend moet worden en de waarden van voorgaande maand meegenomen moet worden om te bepalen in welke zone het hoort wordt het erg moeilijk (vind ik dan lol). Dit betekend dat de waarde in februari deels tarief 1 is en deels tarief 2. Vervolgens moet het in augustus overgaan op tarief 3.

Ik hoop dat jullie een antwoord voor mij hebben :d


PS: Voor achtergrond informatie dit betreft een energiebelasting berekening. Waarbij de wens is de belastingkosten per maand inzichtelijk te hebben. Betreft overigens fictieve waarden.
 

Bijlagen

  • Voorbeeld bestand.xlsx
    9,5 KB · Weergaven: 36
Code: 
=ALS(SOM($C$3:C3)<=$I$3;C3*$J$3;ALS(SOM($C$3:C3)<=$I$4;C3*$J$4;C3*$J$5))

Probeer deze eens in E3
 
Laatst bewerkt:
JVeer zei:
Code: 
=ALS(SOM($C$3:C3)<=$I$3;C3*$J$3;ALS(SOM($C$3:C3)<=$I$4;C3*$J$4;C3*$J$5))

Probeer deze eens in E3
Klik om te vergroten...


Dankjewel @JVeer voor je snelle reactie!

Hij klopt nog niet helemaal, komt wel in de buurt. Als ik de totale kosten bereken kom ik uit op € 7.097,08 de totale kosten uit jouw som komt uit op € 6.730,08

Edit: Hij gaat niet goed in de cellen waarin hij deels in twee zones zit. In dit geval dus in februari en in augustus. In die twee maanden pakt hij voor de hele maand het lagere tarief. Heel februari doet hij nu dus * tarief van zone 2. In werkelijkheid zou hij in februari 2.200 * tarief 1 moeten doen en 4.700 * tarief 2. Daar gaat het mis maar geen idee hoe dat te fixen :D
 
Laatst bewerkt:
Hmm, ik zie even niet waar het mis gaat dan. De formule klopt en neemt vanaf augustus de prijs van zone 3.

Edit: zojuist handmatig uitgerekend en kom ook op 6.730,08
 
Laatst bewerkt:
Ik zie al waar het misgaat. Ik heb de gedeeltelijke tarieven niet meegenomen in feb en eventueel augustus
 
JVeer zei:
Hmm, ik zie even niet waar het mis gaat dan. De formule klopt en neemt vanaf augustus de prijs van zone 3.

Edit: zojuist handmatig uitgerekend en kom ook op 6.730,08
Klik om te vergroten...

Hoi JVeer, denk dat je net mijn update hebt gemist op mijn vorige post. Daarin staat waar het misgaat, misschien kan jij daar iets mee?

Totaal moet in ieder geval zijn:

10.000 * 0,1513 = 1.512,50
40.000 * 0,1069 = 4.275,17
31.800 * 0,0412 = 1.309,41

Totaal = 7.097,08


Update:

JVeer zei:
Ik zie al waar het misgaat. Ik heb de gedeeltelijke tarieven niet meegenomen in feb en eventueel augustus
Klik om te vergroten...


Idd :)
 
Laatst bewerkt:
Dat wordt een complexere formule. Ik heb op dit moment niet veel tijd. Misschien later vandaag kan ik er nog naar kijken. Anders weet vast iemand anders wel een mooie oplossing voor je binnenkort:d
 
JVeer zei:
Dat wordt een complexere formule. Ik heb op dit moment niet veel tijd. Misschien later vandaag kan ik er nog naar kijken. Anders weet vast iemand anders wel een mooie oplossing voor je binnenkort:d
Klik om te vergroten...

Haha ja met recht een breinbreker toch ;)

Oke in ieder geval bedankt tot dusver! Hopelijk hoor ik snel weer van je of van een ander forum lid :)
 
Probeer deze formule eens (zal waarschijnlijk nog wel eenvoudiger kunnen):
Code: 
=ALS(SOM(C$3:C3)>I$3;I$3;SOM(C$3:C3))*J$3+ALS(SOM(C$3:C3)>I$3;ALS(SOM(C$3:C3)>I$4;I$4-I$3;SOM(C$3:C3)-I$3);0)*J$4+ALS(SOM(C$3:C3)>I$4;ALS(SOM(C$3:C3)>I$5;I$5-I$4;SOM(C$3:C3)-I$4);0)*J$5-SOM(E$2:E2)
 
rebmog zei:
Probeer deze formule eens (zal waarschijnlijk nog wel eenvoudiger kunnen):
Code: 
=ALS(SOM(C$3:C3)>I$3;I$3;SOM(C$3:C3))*J$3+ALS(SOM(C$3:C3)>I$3;ALS(SOM(C$3:C3)>I$4;I$4-I$3;SOM(C$3:C3)-I$3);0)*J$4+ALS(SOM(C$3:C3)>I$4;ALS(SOM(C$3:C3)>I$5;I$5-I$4;SOM(C$3:C3)-I$4);0)*J$5-SOM(E$2:E2)
Klik om te vergroten...

Wow nice @rebmog, dankjewel dit lijkt inderdaad de juiste formule te zijn. Onderaan de streep komt het in ieder geval overeen. Ik ga het meenemen in wat alles bij elkaar een grote rapportage is.

Nogmaals hartelijk dank!
 
Toch nog een aanvullende vraag, hoe bouw ik in wanneer hij opnieuw moet beginnen voor de volgende 12 maanden dus een andere locatie? Zie nieuwe bijlage voor voorbeeld.
 

Bijlagen

  • Voorbeeld bestand.xlsx
    11,2 KB · Weergaven: 26
Ik heb daarvoor overal in de formule SOM vervangen door SOM.ALS. Dus dan wordt het:
Code: 
=ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$3;J$3;SOM.ALS(B$3:B3;B3;D$3:D3))*K$3+ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$3;ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$4;J$4-J$3;SOM.ALS(B$3:B3;B3;D$3:D3)-J$3);0)*K$4+ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$4;ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$5;J$5-J$4;SOM.ALS(B$3:B3;B3;D$3:D3)-J$4);0)*K$5-SOM.ALS(B$2:B2;B3;F$2:F2)
 
rebmog zei:
Ik heb daarvoor overal in de formule SOM vervangen door SOM.ALS. Dus dan wordt het:
Code: 
=ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$3;J$3;SOM.ALS(B$3:B3;B3;D$3:D3))*K$3+ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$3;ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$4;J$4-J$3;SOM.ALS(B$3:B3;B3;D$3:D3)-J$3);0)*K$4+ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$4;ALS(SOM.ALS(B$3:B3;B3;D$3:D3)>J$5;J$5-J$4;SOM.ALS(B$3:B3;B3;D$3:D3)-J$4);0)*K$5-SOM.ALS(B$2:B2;B3;F$2:F2)
Klik om te vergroten...

Hoi rebmog,

Dat werkt niet helemaal zoals het hoort. Volgens mij wordt de scheiding tussen de locaties niet goed gemaakt. In de bijlage heb ik de som in kolom G gezet en doorgetrokken. Kan jij daar nog wat mee?
 

Bijlagen

  • Voorbeeld bestand.xlsx
    12,5 KB · Weergaven: 25
Laatst bewerkt:
Omdat je de formule nu in een andere kolom hebt geplaatst moet je het laatste deel van de formule aanpassen: F$2:F2 moet dan worden G$2:G2
 
rebmog zei:
Omdat je de formule nu in een andere kolom hebt geplaatst moet je het laatste deel van de formule aanpassen: F$2:F2 moet dan worden G$2:G2
Klik om te vergroten...

Oja, natuurlijk dat had ikzelf moeten weten / kunnen zien, excuus! :eek:

Dankjewel ik ben voortreffelijk geholpen zo!
 
Oei energiebelasting. Altijd een leuke materie. Ik heb hier ook mee te maken gehad tijdens een van mijn opdrachten waar ik verantwoordelijk was voor de afrekening servicekosten. Moet even graven in mijn geheugen of ik hier nog een betere/makkelijkere/simpelere oplossing voor heb.
 
Nog een optie, iets korter allemaal (formule voor kolom G)
Code: 
G3: =MIN(SOM.ALS(B$3:B3;B3;D$3:D3);$K$3)*$L$3+MAX(MIN($K$4-$K$3;SOM.ALS(B$3:B3;B3;D$3:D3)-$K$3);0)*$L$4+MAX(SOM.ALS(B$3:B3;B3;D$3:D3)-$K$4;0)*$L$5-SOM.ALS(B$2:B2;B3;G$2:G2)
Of, als je in bijv. kolom E het cumulatieve gebruik zet:
Code: 
E3: =SOM.ALS(B$3:B3;B3;D$3:D3)
G3: =MIN(E3;$J$3)*$K$3+MAX(MIN($J$4-$J$3;E3-$J$3);0)*$K$4+MAX(E3-$J$4;0)*$K$5-SOM.ALS(B$2:B2;B3;G$2:G2)
Door alle verwijzingen e.d. toch maar even in je voorbeeldje gezet ter inspiratie.
 

Bijlagen

  • Voorbeeld bestand (AC).xlsx
    12,6 KB · Weergaven: 27
Laatst bewerkt:
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan