Formule aanpassen ivm kringverwijzing

[rmk75]

Goedemiddag,

Op jaarbasis hebben wij o.a. een aantal avond/nacht schakeldiensten. Indien men 60% of meer wordt opgeschakeld dan geldt er een andere vergoeding.
60% van dit totaal aantal diensten wordt weergegeven in S4

Nu werkt de hulpkolom in kolom S niet omdat deze niet wijzigt als er een filter wordt toegepast in kolom B.
Om dit op te lossen heb ik een nieuwe hulpkolom gemaakt (U).
De gegevens hierin wijzigen als er een filter wordt toegepast in kolom B. Tot zover geen probleem.

Het gaat verkeerd in de formule in kolom I (kringverwijzing)
Hier word gekeken naar het aantal opgeschakelde A/N diensten en wanneer dit meer is dan het aantal in S4 dan geeft ie de hogere vergoeding weer.

Wanneer er gefilterd is op bijv. 2018 dan zie je dat 60% van het totaal aantal A/N schakeldiensten 4,2 is.
De eerste 4 diensten (zie kolom J) zouden dus € 80,- moeten zijn en pas de 5e dienst zou dan € 100,- moeten worden.

Ik denk dat het opgelost kan worden door in de formules van kolom J een toevoeging te doen door de regel en alle voorgaande regels bij elkaar op tellen en dit dan te vergelijken met de waarde in U4. Echter krijg ik dit niet voor elkaar.

=ALS(E66="Schakeldienst A/N, opgeschakeld naar nacht";ALS(EN(E66;S66>=$S$4);100;(VERT.ZOEKEN(E66;VERGOEDINGEN!$B$34:$I$34;1+WEEKDAG(B66;2))));"")

Ik dacht de oplossing te hebben door onderstaand stukje in de formule toe te voegen, maar dit krijg ik niet werkend.

=SOM(U5:U66>=$U$4);100

Iemand wellicht een oplossing??

Bekijk bijlage Blanco testversie werkrapport 09-11-2020.xlsx

 

[rmk75]

Een eventuele oplossing zonder hulpkolom is uiteraard ook welkom..

 

[Haije]

volgens mij gebruik je de EN-formule niet correct:

maak van

=ALS(E66="Schakeldienst A/N, opgeschakeld naar nacht";ALS[COLOR="#FF0000"][B](EN(E66;S66>=$S$4)[/B][/COLOR];100;(VERT.ZOEKEN(E66;VERGOEDINGEN!$B$34:$I$34;1+WEEKDAG(B66;2))));"")

eens

=ALS(E66="Schakeldienst A/N, opgeschakeld naar nacht";ALS[B][COLOR="#FF0000"](EN(E666>=$S$4;S66>=$S$4)[/COLOR][/B];100;(VERT.ZOEKEN(E66;VERGOEDINGEN!$B$34:$I$34;1+WEEKDAG(B66;2))));"")

 

[rmk75]

Bij elke 'opgeschakeld naar nacht' dienst krijg ik met uw formule nu € 80,- te zien.
Bij filteren op 2018 zouden de eerste 4 diensten € 80,- moeten zijn en vanaf de 5e dienst en meer € 100,-.

Overigens werkt hulpkolom S werkt niet naar behoren, dus dan krijg ik sowieso verkeerde data..
De nieuwe hulpkolom in U doet wat ie zou moeten doen.
Maar wanneer ik laat kijken naar kolom U krijg ik wederom een kringverwijzing.

 

[rmk75]

Ik dacht het op te lossen met:

=ALS(EN(E66="Schakeldienst A/N, opgeschakeld naar nacht";SOM(U5:U66>=U4;100;VERT.ZOEKEN(E66;VERGOEDINGEN!B34:I34;1+WEEKDAG(B66;2))));"")

maar dit resulteert ook in een kringverwijzing.

Is de denkwijze niet goed?

Als in cel E66 een 'Schakeldienst A/N, opgeschakeld naar nacht' staat en de som van deze diensten is meer dan het aantal in cel U4 dan moet er € 100,- ingevuld worden, zoniet dan moet de vergoeding gezocht worden op het tabblad vergoedingen..

 

[rmk75]

Wie heeft een idee hoe ik deze kringverwijzing kan oplossen?

Voor bijv. regel 68.

=ALS(E68="Schakeldienst A/N, opgeschakeld naar nacht";ALS(EN(E68;U68>=$U$4);100;(VERT.ZOEKEN(E68;VERGOEDINGEN!$B$34:$I$34;1+WEEKDAG(B68;2))));"")

Cel J68 kijkt naar bepaalde data in cel E68. Indien hier 'opgeschakeld naar nacht' staat dan wordt er een bepaalde vergoeding gezocht uit het tabblad vergoedingen. Als het totaal van voorgaande schakeldagen (incl. de huidige regel), welke worden geteld in kolom U, boven een bepaald aantal komt (cel U4) dan geld er een andere vergoeding.

De kringverwijzing zit in cel J68 / U68 maar het lukt me niet om deze te omzeilen..


Alvast bedankt,

rmk75

 

[rmk75]

Door middel van een extra hulpkolom (kolom W) heb ik een workaround kunnen maken.
Wellicht niet de meest nette oplossing maar voor nu werkt het wel (voor zover ik getest heb..) en heb ik geen kringverwijzing meer.

Bekijk bijlage Blanco testversie werkrapport 10-11-2020 (1).xlsx