ALS formule onoverzichtelijk geworden

[Hunuloeloe]

Ik ben een kosten Tool aan het maken waarbij het tarief p/ton berekend wordt. Echter nu moest ik wat criteria in de formule toevoegen en is deze dusdanig groot geworden dat ik niet meer weet wanneer ik wel haakje moet sluiten etc. etc....
Ofwel ik zie door de bomen het bos niet meer! :shocked:

Wie o Wie wilt hier even naar kijken en kan me helpen het bos te vinden
Bekijk bijlage Controleren formule.xlsm

of formule:

=ALS($B$8="";"";ALS($B$9="";"";ALS($B$22<>"";ALS($B$17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*(2-$B$22);ALS($B$17="";(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*(2-$B$22);ALS($B$23<>"";ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9-$B$23;ALS($B$17="";(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9-$B$23;ALS($B$24="No";ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+($B$14+$B$15)+($B$16+$B$18))/$B$9*2;ALS($B$17="";(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*2));ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9;(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9))))))))))

 

[Gijsbert1]

Pas de achterkant van de formule eens aan:

$B$9)))))));"")))

Er wordt een [waarde-als-onwaar] gemist bij de 3e ALS functie.
Ik ga niet proberen je formule te begrijpen/ontrafelen maar ben er wel van overtuigd dat dit veel korter kan.

EDIT: de 6e en 10e ALS functies missen ook een [waarde-als-onwaar].
Tip: zet je cursor één voor één op alle "ALS" functies in de formule en druk op het "Fx" knopje links naast de formule balk.
Als het [waarde-als-onwaar] deel van de functie leeg is zet daar dan "" in. De formule wordt automatich aangepast en je hoeft zelf niet te rommelen met haakjes, etc.

 

[Hunuloeloe]

Mede hierdoor heb ik voor mezelf het bos weer kunnen vinden
Ik heb teveel ALS functies gebruikt wat niet nodig was...
Voor de liefhebbers:

=ALS($B$8="";"";ALS($B$9="";"";ALS($B$22<>"";ALS($B$17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*(2-$B$22);(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*(2-$B$22));ALS($B$23<>"";ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9-$B$23;(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9-$B$23);ALS($B$24="No";ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+($B$14+$B$15)+($B$16+$B$18))/$B$9*2;(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9*2);ALS(B17<>"";(($B$8*$B$17)+($B$7*$B$17)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9;(($B$8*$B$20)+($B$7*$B$20)+($B$10+$B$12+$B$13)+(($B$14+$B$15)*2)+($B$16+$B$18))/$B$9))))))

 

[AlexCEL]

De EN/OF-functies kunnen ook helpen om het e.e.a. wat korter te maken...

=ALS(OF($B$8="";$B$9="");"";ALS(EN($B$22<>"";$B$17<>"");....

Ik ga net als Gijsbert niet proberen je formule te ontrafelen maar ook ik ben ervan overtuigd dat het veel korter kan.

 

[Gijsbert1]

Als je een formule gaat gebruiken met zo veel "vaste" verwijzingen ($ dollartekens $), is het wellicht beter om de cellen te hernoemen.
Daar wordt de formule misschien wel op het oog langer van maar ook veel meer leesbaar!
Voorbeeld:Bekijk bijlage Controleren formule (1) _leesbaar.xlsm

 

[Hunuloeloe]

Dankjewel Alex en Gijsbert, beide tips overgenomen.
Echter hoe krijg ik in de formule de celnamen te zien en niet het cel nr? bijv. cel Dist_empty ipv C7? ofwel hoe past dit zich automatisch aan?

 

[Gijsbert1]

Ik heb de cellen waarnaar verwezen wordt eerst hernoemt.
Daarna met "zoeken/vervangen" alle verwijzingen vervangen.
Dat is het snelst maar automatisch gaat dat helaas niet!

 

[Hunuloeloe]

Dank voor de tip!:thumb:

 

[roeljongman]

Ik heb wel je formule geanaliseerd en het kan inderdaad flink korter.
Ik krijg bij elke combinatie dezelfde uitkomst, alleen als de coverage ratio of Discount p ton ingevuld gaan worden dan kom ik op andere totalen.
maar de formule vond ik ook niet logisch bij discount p ton. en bij gebruik van return_freight. maar dat kan zijn omdat ik niet helemaal doorzie wat er uit moet komen.
Wellicht helpt deze eenvoudiger formule helpt.. want zoals eerdere reageerders al zeiden hij kan eenvoudiger omdat er heel veel herhaling in zit. Nu heb je 1 voor 1 alle opties opgesomd in de formule

De sleutel van vereenvoudiging zit in het bepalen wat de variabele factoren zijn en welke cellen ze beinvloeden, alle cellen die in elke berekening op dezelfde manier meetellen of geen invloed hebben op elkaar kun je dan buiten de ALS reeks houden
Bekijk bijlage Kopie van Controleren formule.xlsm

=(+B10+B12+B13+(B14+B15)*ALS($B$24="no";1;2)+B16+B18+ALS($B$17<>"";($B$7+$B$8)*$B$17;($B$8+$B$7)*$B$20))/ALS($B$22<>"";$B$9*(2-B22);ALS($B$23<>"";$B$9-$B$23;ALS($B$24="no";$B$9*2;$B$9)))

De formule zou nog korter kunnen als je de volgorde van de velden aanpast.
als je waiting hours omwisselt met cleaning costs of helemaal doorschuift naar onder, direct boven total waitinghours
en Ferry en train verschuift onder other costs. kun je alle vast kosten in 1 somformule vangen.

 

[Hunuloeloe]

Roel Dankje voor je nieuwe inzicht + analyse! Echter kom ik niet op het gewenste resultaat hiermee maar dat komt wss door de geringen achtergrond info, bij deze dus even wat B22 tot B24 voor invloed hebben.
Als ik geen retourvracht heb (B24=no) dan worden alle kosten x2 gerekend.
Stel ik heb een deel van mijn retourvracht gedekt (B22) dan wordt dit % van de 200% afgehaald (200% is voor geen retourvracht= kosten x2)
Stel ik wil mijn klant korting geven per ton in euro's dan gaat dit van de totaal berekende prijs af vandaar ook die/$B$9-$B$23 (excel doet toch altijd eerst / en vervolgens pas - )
De kosten van de trein en boot dienen bij een enkele vracht al x2 gedaan te worden, echter kon ik hier nog niet voor verzinnen hoe ik die x2 liet staan want nu wordt het bij een 10% cov_ratio toch x3.8 (x2 normaal + (2-10%=1.8)=3.8) gedaan en dat is eigenlijk niet de bedoeling

Niet gedacht dat iemand daadwerkelijk zo'n analyse wilde uitvoeren nogmaals dank hiervoor! Hopelijk kun je met deze achtergrond info een laatste zetje in de goede richting geven :thumb:

 

[roeljongman]

Het leuke van als formules is dat als je ze weer onder elkaar zet je heel snel de dubbelingen ziet, daarbij heb ik ook wel eens kostencalculaties gemaakt voor dit soort kosten dus dat helpt ook..

Stel ik wil mijn klant korting geven per ton in euro's dan gaat dit van de totaal berekende prijs af vandaar ook die/$B$9-$B$23 (excel doet toch altijd eerst / en vervolgens pas - )

Ja dat klopt, daar was ik even verdwaald in de formule.

Als ik je nieuwe informatie combineer dan kom ik op deze formule

=(SOM(B14:B15)*2+SOM(B10;B12;B13;B16;B18;ALS($B$17<>"";($B$7+$B$8)*$B$17;($B$8+$B$7)*$B$20))*ALS(B24="yes";1;ALS($B$22<>"";(2-$B$22);2)))/$B$9-$B$23

waarbij:
- De Ferry en Train altijd 2 keer gerekend moeten worden (deze vallen niet onder de conv_ratio korting) > SOM(B14:B15)*2
- Alle overige kosten en de kilometers*tarief worden opgeteld > +SOM(B10;B12;B13;B16;B18;ALS($B$17<>"";($B$7+$B$8)*$B$17;($B$8+$B$7)*$B$20))
- Deze kosten moeten allemaal maal 2, behalve als er een volle retourvracht is (dan *1) of als er een gedeeltelijke retourvracht is dan (2-b22) > *ALS(B24="yes";1;ALS($B$22<>"";(2-$B$22);2)))
- er wordt altijd gedeeld door het tonnage uit B9 (gehele forumle)/$B$9
- en als de Discount per ton methode wordt gebruikt dan trek je dat helemaal op het eind van de formule dat bedrag af -$B$23
(omdat jij al met een validatie afvangt dat max 1 kortingsmethode wordt gebruikt hoeft b23 niet door een als-formule bepaald te worden. b23 kan alleen en waarde bevatten als b22 en b24 leeg zijn)

ik kom nog niet altijd op hetzelfde resultaat als jij, maar dat zit vermoedelijk in de afhandeling van de Ferry en train kosten. ik denk dat de formule nu wel de goede elementen bevat en alleen nog misschien moet aangepast op de voorwaarden wanneer 2x of minder wordt berekend.

 

[Hunuloeloe]

@Roeljongman dit is de formule geworden:

=(SOM(B14:B15)*2+SOM(B10;B12;B13;B16;B18;ALS($B$17<>"";($B$7+$B$8)*$B$17;($B$8+$B$7)*$B$20))*ALS(B24="no";2;ALS($B$22<>"";(2-$B$22);ALS($B$23<>"";2;1))))/$B$9-$B$23

Standaard ga ik namelijk uit van een retourvracht. Dit klinkt raar natuurlijk maar als je nu 1 euro per ton korting geeft gaat die wel uit van 2x de route ofwel geen retourvracht...
De giga ALS formule die ik eerst had, had namelijk ook wat foutjes die ik er nu uitgefilterd heb! heb ook alle mogelijke combi's getest en met behulp van jouw formule + kleine aanpassing is die nu 100% correct!
Dankjewel hiervoor dit zal voor het vervolg wel een stuk beter lezen

Maargoed bedankt voor je hulp Topper:thumb: