Formule te lang (meer dan 8192 tekens) bij staffelberekening

[oceanrace]

Goedendag,
Ik zit met het volgende probleem:
Mijn formule voor het cumulatief berekenen van waarden wordt te lang, namelijk zo'n 15000 tekens één formule terwijl maximaal 8192 is toegestaan.

Ter verduidelijking wat de formule doet:
De waarde wordt cumulatief berekend, dus 0‐100 waarde X, 100‐200 waarde Y etc. De
waarden van X, Y etc. worden vervolgens opgeteld.

Zie voorbeeld, met goed werkende korte versie van de formule.

 

[JeanPaul28]

Graag meer uitleg over formule, kan deze niet lezen.
# waarde

 

[oceanrace]

Hallo JeanPaul28,
De waarde wordt cumulatief berekend, dus 0‐100 waarde X, 100‐200 waarde Y etc. De
waarden van X, Y etc. worden vervolgens opgeteld.

 

[JeanPaul28]

Ik zie het niet, geef eens een volledig uitgewerkt voorbeeld

 

[sylvester-ponte]

met vba:

Function DoorTellen(Aantal As Long, OppervlakteLijst As Range, PrijsLijst As Range) As Double
Dim Teller As Long, Temp As Range
For Teller = 2 To OppervlakteLijst.Cells.Count
    Set Temp = OppervlakteLijst(1, Teller)
    If Aantal > Temp Then
        DoorTellen = DoorTellen + (Temp - Temp(1, 0)) * PrijsLijst(1, Teller - 1)
    Else
        Exit For
    End If
Next Teller
DoorTellen = DoorTellen + (Aantal - Temp(1, 0)) * PrijsLijst(1, Teller - 1)
End Function

 

[sylvester-ponte]

verander:

For Teller = 2 To OppervlakteLijst.Cells.Count

in

For Teller = 2 To OppervlakteLijst.Cells.Count + 1

 

[oceanrace]

Hallo Sylvester,
Ik ben je ontzettend dankbaar, wat een geweldige functie!
Deze kan ik voor meerdere doeleinden gebruiken.
Hoewel ik het nog wel wat lastig vind om te begrijpen hoe hij precies werkt doet hij precies wat hij moet doen.
Bedankt.

 

[sylvester-ponte]

ik had van ochtend niet zo veel tijd.

deze function is netter en er staat uitleg bij:

Function DoorTellen(Aantal As Long, OppervlakteLijst As Range, PrijsLijst As Range) As Double
Dim Teller As Long, Temp As Range
'eerst alle hele blokjes
For Teller = 1 To OppervlakteLijst.Cells.Count 'teller wordt steeds met 1 verhoogt tot deze loop verlaten wordt
    Set Temp = OppervlakteLijst(1, Teller) 'temp wijst nu in de oppervalktelijst naar vakje teller
    If Aantal > Temp(1, 2) Then  'temp(1,2) wijst naar de naast gelegen cel
        DoorTellen = DoorTellen + (Temp(1, 2) - Temp) * PrijsLijst(1, Teller) 'het gebiedje wordt er bij geteld
    Else
        Exit For    'als aantal niet groter is dan temp(1,2) dan de loop verlaten
    End If
Next Teller
DoorTellen = DoorTellen + (Aantal - Temp) * PrijsLijst(1, Teller) 'en het restje
End Function

 

[oceanrace]

Dankje, ik denk dat het ook lastig is om dit met een formule op te lossen.
Op deze manier is het aantal staffels (of knikpunten) in principe onbeperkt.
De uitleg is helder!

 

[oceanrace]

Beste Sylvester,
Ik vroeg me nog af of er ook een mogelijkheid was om tussenliggende lege cellen "over te slaan".
Nu klopt de eindwaarde niet wanneer er tussenliggende staffels leeg zijn of een 0 bevatten.
Ik heb dit nu met formules opgelost, maar misschien dat het ook in jou function kan

 

[snb]

Het kan ook gewoon met een formule:

in C8:

=INDEX($F8:$AT8;1;MATCH($B8;$F$7:$AT$7;1)-1)*INDEX($F$7:AT$7;MATCH($B8;$F$7:$AT$7;1))

en doortrekken naar beneden.

 

[oceanrace]

Bedankt snb,
Ben benieuwd of de formule ook sneller werkt dan de VBA oplossing.

 

[sylvester-ponte]

Bas,wil je aangeven wat er fout gaat?
ik zie de verschillen niet.
het hoeft niet een groot bestand te zijn als er maar een fout in zit.

 

[oceanrace]

Ik heb in het voorbeeld beide oplossingen naast elkaar gezet.

Bekijk bijlage Cumulatief optellen voorbeeld met vba 4.xlsm

 

[sylvester-ponte]

bedoel je met een lege cel een copy van de voorgaande?

 

[sylvester-ponte]

zo iets?

 

[oceanrace]

bedoel je met een lege cel een copy van de voorgaande?

Ja dat is wat ik bedoel, in voorbeeld 5 gaat dit nog niet helemaal goed.
Alle waarden (type 1 t/tm 5) zouden in dit voorbeeld dezelfde prijs moeten krijgen bij elke gevulde oppervlakte.

 

[sylvester-ponte]

voorbeeld 5 zie ik niet ik zie Type 1 tm 4
wil je voorbeeld 5 toevoegen?
die aflopende waarden van de prijzen, is daar een functie voor? of is dat handwerk?
en de oplopende waarden van de oppervlakten?

 

[oceanrace]

Sorry, ik bedoelde het nummer van de bijlage.
Type 1 t/m 4 zouden dezelfde prijs moeten krijgen.

 

[sylvester-ponte]

in type1 van post 16 staan hele andere prijzen in dan in type 2 tm 4