de ALS formule

[agric009]

Hallo,

Ik heb een vraag met betrekking tot de ALS formule.

In de kolom reisuren wil ik graag een formule maken zodat de reisuren uitgerekend worden aan de hand van de ingevoerde KM's.

Ik beperk me even tot regel 6. de rest is toch te kopiëren.

In de cellen C6; E6; G6; I6 en K6 vul je de gereden km in
In de cellen D6; F6; H6; J6 en L6 vul je 1 in als de persoon als chauffeur heeft gereden.

Wat de cellen M6; N6; O6; P6 en Q6 moeten weergeven staat in het gegevensblokje.

Ik had de formule =ALS(C6>V4<W4;(C6*2-60)/60) in cel D6 ingevoerd. deze geeft ONWAAR als uitkomst als ik 30 in vul in cel C6.
ook zou ik graag de andere km opties in dezelfde formule verwerken.


Kan iemand mij hierbij helpen?

Alvast bedankt voor de genomen moeite.

Mvg, Martijn

 

[Cobbe]

Ik heb maandag en dinsdag voor u gedaan.

 

[joost1]

Tussen 25 en 50 moet blijkbaar de formule gebruikt van (2 maal het aantal km - 60)/60
Moet dit niet zijn tussen 30 en 50 km ? ( (2*25-60)/60 levert een negatief getal op )
In mijn bijlage ben ik daarom maar even uitgegaan van een minimum van 30 km.
Hoe ga je de werkelijke reisuren invullen ?

Groet

 

[agric009]

Hallo Cobbe en Joost,

Ik snap de uitkomst van jou formule niet helemaal Cobbe. Deze zijn wel erg hoog?
Waarschijnlijk heb ik het niet goed uitgelegd.

Tussen de 30 en 50 km moet idd zoals Joost juist opmerkt de formule gebruikt worden.
Tussen 50 en 59 moet de uitkomst 1 zijn tenzij hij als chauffeur rijdt dan is het 2.
Tussen 59 en 70 moet de uitkomst 1,2 zijn tenzij hij als chauffeur rijdt dan is het 2,2.
Tussen 70 en 81 moet de uitkomst 1,4 zijn tenzij hij als chauffeur rijdt dan is het 2,4.
Tussen 81 en 92 moet de uitkomst 1,5 zijn tenzij hij als chauffeur rijdt dan is het 2,5
Tussen 92 en 105 moet de uitkomst 1,8 zijn tenzij hij als chauffeur rijdt dan is het 2,8
Dus als bij ch een 1 ingevuld word moet deze er bij opgeteld worden. Ik had x 2 staan dit is niet juist.

De werkelijke reistijd zal denk ik handmatig ingevoerd moeten worden.

Kunnen jullie hier zo verder mee?

 

[agric009]

Joost,

ik heb in jou formule de *D6 veranderd in +D6 en de D..* bij kleiner dan 50 ervoor weggehaald zodat je met 0 en 1 kan werken.
Nu krijg ik de juiste uitkomsten bij het invullen.

TOP!!

Enorm bedankt.

 

[agric009]

Zo is ie dan geworden.

 

[MarcelBeug]

Hm, ik wil de pret niet drukken, maar de formules houden geen rekening met de dubbele kolommen in het bereik C-L.
Zo pakt de vrijdagformule in Q de gegevens van woensdag in G.
Ik zal er vanavond eens verder naar kijken.

 

[MarcelBeug]

Nog even een opmerking over de formule genoemd in post #1: =ALS(C6>V4<W4;(C6*2-60)/60)
Dat is het soort wiskundige notatie waar Excel zo zijn eigen interpretatie aan geeft:
eerst wordt gekeken of C6>V4, dus dat levert WAAR of ONWAAR op. Vervolgens wordt dat vergeleken met de waarde in W4 en in elk geval levert dat ONWAAR op.
Niet helemaal wat er bedoeld werd.

Nu de oplossing: ik ben er van uitgegaan dat in de bijlage van post #6 de formule in M6 correct was.

Ik heb 2 namen gedefinieerd (zie tab formules - Namen beheren):
KM =INDEX(Blad1!$C6:$K6;2*KOLOMMEN(Blad1!$M6:M6)-1)
ch =INDEX(Blad1!$D6:$L6;2*KOLOMMEN(Blad1!$M6:M6)-1)
Edit: let op: deze namen zijn gedefinieerd nadat eerst cel M6 was geselecteerd. Vanuit andere cellen zijn de bereiken in die formules dienovereenkomstig verschoven.


En de formule in M6 aangepast naar:

=ALS(KM<30;""; ALS(KM>=105;"";ALS(KM>=50;VERT.ZOEKEN(KM;$V$4:$X$9;3;WAAR)+ch;ALS(KM<50;(KM*2-60)/60;))))

(Dus C6 vervangen door KM en D6 vervangen door ch.)

Deze formule naar rechts en naar beneden doorgetrokken zodat deze het bereik M6:Q9 omvatte.

Tenslotte bereik M6:Q9 gekopieerd en geplakt vanaf M11, M16 etcetera om het hele jaar af te dekken.

 

[agric009]

He Marcel,

Dank voor je input.

Ik ben zelf ook nog even aan het sleutelen geweest. zie het bestand.

=ALS(C6<30;""; ALS(C6>=105,1;"";ALS(C6>50;VERT.ZOEKEN(C6;$V$4:$X$9;3;WAAR);ALS(C6<=50;(C6*2-60)/60))+D6)

ik heb de waarden iets aangepast zodat hij tot 50 KM ook gaat rekenen en geen 1 als uitkomst geeft.
ook heb ik +D6 verplaatst zodat er bij KM beneden de 50 ook 1 word opgeteld.

nu loop ik tegen het volgende aan.

als D6(ch) = 1 dan zou de volgende berekening moeten volgen 1/60*(C6*2)
dit moet dus werken voor km beneden de 50

Weet jij hoe we deze kunnen toevoegen?

MVG, Martijn

 

[MarcelBeug]

Toevoegen in de code

=ALS(C6<30;""; ALS(C6>=105,1;"";ALS(C6>50;VERT.ZOEKEN(C6;$V$4:$X$9;3;WAAR);ALS(C6<=50;ALS(D6)="ch";1/60*(C6*2); (C6*2-60)/60; ))))+D6)

Jammer dat je mijn input met gedefinieerde namen niet gebruikt hebt...

 

[agric009]

Die heb ik later pas gezien...

Lijkt mij ook een goede oplossing, alleen moet ik die nog ff bekijken.

 

[joost1]

Ik heb hier ook nog even weer naar gekeken.
In de bijlage in het rechter gedeelte wat kolommen toegevoegd zodat de formule gekopieerd kan worden naar de andere dagen.
Kijk maar of je er iets mee kunt.

 

[agric009]

Marcel,

Ik kan zien wat je gedaan hebt.
Maar mijn kennis van excel reikt nog niet zover dat ik het snap.
Wat doet het stuk 2*kolommen bijvoorbeeld in de formule?
Het stuk (Blad1!$M6:M6)-1) begrijp ik ook niet, wat doet dat?

MVG, Martijn

 

[agric009]

Heren,

Op basis van dit stukje probeer ik een formule te bouwen.

Hebben jullie ook even de info waar het om gaat.

 

[MarcelBeug]

...Wat doet het stuk 2*kolommen bijvoorbeeld in de formule?
Het stuk (Blad1!$M6:M6)-1) begrijp ik ook niet, wat doet dat?...

Tja, als je het zo opdeelt, is het ook niet te volgen.

KM =INDEX(Blad1!$C6:$K6;2*KOLOMMEN(Blad1!$M6:M6)-1)

KOLOMMEN(Blad1!$M6:M6) geeft het aantal kolommen van M6:M6 oftewel 1
Als je die formule naar rechts sleept, wordt het M6:N6 = 2 etcetera.

Dat maal 2 wordt 2 4 etcetera. Minus 1 wordt 1 3 etcetera.

Met de functie INDEX krijg je dan het eerste, derde etcetera element van C6:K6, oftewel C6, E6, G6, I6, K6.

 

[snb]

Een doordachte opbouw bespaart je veel hoofdbrekens:

 

[joost1]

Mijn oplossing van #12 geeft dezelfde waarden als in het word document.
Dus misschien kun je nog eens kijken naar de formule kijken die ik gebruikt heb ( zie bijlage) .
Ik ben het eens met snb dat een goede opbouw je veel hoofdbrekens kan besparen.

Groet

 

[snb]

En..

- vermijd samengevoegde cellen

- gebruik in Excel ingebouwde funktionaliteit (de Tabel bijvoorbeeld)