Formule

[Scallebe]

Beste ontwikkelaars,

Mag ik alle Helmpmij-ers een spetterend 2016 toewensen en jullie bedanken voor de fantastische inspanningen die jullie leveren om ons met de vele vragen verder te helpen. :thumb:

Bekijk bijlage Map.xlsx

Een kleine() uitleg bij de bijlage :

Ik zou in de cel Y2 een formule willen maken om het aantal nog in te geven inputs te tellen ten opzichte van het totaal in te geven inputs.

Mijn Eigen berekening in het voorbeeld is de volgende :

Het totaal aantal lijnen met mogelijke inputs = 292 (van rij 6 tem rij 297)

Maar niet alle cellen in kolom Y hebben een input nodig.

De cellen in kolom Y die geen input moeten hebben zijn die waar de waarde in kolom P gelijk is aan "zonder" en die waar de maand van de cel in kolom M kleiner is dan de vaste cel Y3 (Dat zijn de gele cellen)


Ik heb volgende formules (Met jullie medewerking gerealiseerd en dus ook perfect kloppen)

Het aantal rijen die voldoen aan de waarde "Zonder" = 49 (zie formule in W1)

Het aantal gele cellen = 53 (zie formule in V1)

Er zijn echter ook rijen die voldoen aan de twee voorwaarden (zowel geel als "Zonder" en worden dus dubbel geteld) = 16 (zie formule in U1)

Als ik dus een combinatie wil maken van de verschillende formules : (292 (=297 lijnen; waarde terug te vinden in AA1; -5) - (53 gele + 49 zonder) = 190)+16 dubbele = total 206

in cijfers : ((((297-5)-53)-49)+16) = 206

In het voorbeeld is de kolom Y volledig ingevuld (behalve de gele cellen zoals het zou moeten - waarde kan ook 0 zijn) en het eindresultaat = "Nog in te geven : -33/206" terwijl het : "Nog in te geven : 0/206" zou moeten zijn.

Als je in de kolom Y van rij 6 tem rij 297 alles leeg maakt dan zie je ook het resultaat : "Nog in te geven : 206/206" Dus vertrekkende van een blanco kolom Y klopt het resultaat. En als je begint in te geven dan begint het ook naar beneden af te tellen.

In de kolom Y blijven de gele cellen altijd leeg en in alle andere cellen komt een input. Kan ook 0 zijn. en bij de rijen "Zonder" is het principeel altijd 0.

Ik hoop dat mijn uitleg voldoende is...


Mijn formule loopt dus grondig fout.


Hoe kan ik dit oplossen?

 

[Cobbe]

Ik denk dat je moet weten waarom die cellen geel zijn om een berekening te kunnen doen.

 

[Scallebe]

Cobbe,

Met de volgende formule wordt de cel geel (Voorwaardelijke opmaak) : =$AB$3<MONTH($M6) (vb voor cel AB6)

Na hulp van hier kreeg ik de formule : =(SUMPRODUCT(--(MONTH($M6:$M297)>$AB$3))) om al de cellen te tellen die aan deze voorwaarde voldeden.

Dus de formule is eigenlijk wel gekend.

Greetz

Pascal

 

[Cobbe]

Je post een formule die een waarde vergelijkt met $AB$3 --- in AB3 staat niets.

Zet in kolom AB eens een formule die een x oplevert als aan de voorwaarde is voldaan om niet laten mee te tellen.

Kan je hier iets mee?

="Nog in te geven"&" : "& $Z$1-($AA$1-5-$V$1-$W$1+$U$1) &"/"&(($AA$1-5)-(SOMPRODUCT(--(($P6:$P297)="ZONDER")))-(((SOMPRODUCT(--($Y$3>MAAND((INDIRECT("$N$6:$N$"&$AA$1))-1)))))+(SOMPRODUCT(--($Y$3<MAAND(INDIRECT("$M6:$M$"&$AA$1))))))+(SOMPRODUCT((MAAND($M$6:$M$297)>$Y$3)*($P$6:$P$297="ZONDER"))))

 

[Scallebe]

Cobbe,

Sorry, die waarde staat in het voorbeeld in de cel Y3 in de tekstkleur wit zodat het onzichtbaar is voor de gebruikers, de formule eerder vermeld komt uit de originele sheet waar de waarde in AB3 staat. Ik heb in het voorbeeld wat kolommen verwijderd, daardoor het misverstand. Ik kan eigenlijk ook die waarde in mijn formule zetten natuurlijk.

Ik probeer straks de formule.

Alvast bedankt

Greetz

Pascal

 

[Scallebe]

Cobbe,

Misschien heb ik mij verkeerd uitgedrukt.

De formule die je meegegeven hebt kan ik volgen en het resultaat is inderdaad 0/206.

De bedoeling is echter wanneer ik met een blanco kolom Y vertrek dan zou er 206/206 moeten staan (en dat is het geval wanneer ik de kolom leeg maak) zijnde in deze situatie het max aantal in te voeren cellen (zijnde 297 lijnen -5 -de gele -de zonders +de dubbel getelde)

Eens dat we beginnen met de gegevens in te voeren zou de eerste waarde moeten beginnen aftellen 205/206, 204/206, 203/206, enz...

Wanneer alle cellen zijn ingebracht (behalve de gele) zou het eindcijfer 0/206 moeten zijn.

De formule zou ergens moeten beginnen (volgens mij) met : aantal blanco cellen of aantal cellen >0 tussen y6 en y297 - de gele - de zonders + de dubbele = 206. bij inputs verminderd deze uitkomst.

maar het wil niet lukken...

Ik heb bv deze formule :

=(AANTALARG($A$6:$A$1000000))-(AANTAL.LEGE.CELLEN(Y6:Y297))-(SOMPRODUCT(--(($P$6:$P$297)="ZONDER")))+(SOMPRODUCT((MAAND($M$6:$M$297)>Y3)*($P$6:$P$297="ZONDER")))

deze formule telt bij een volledig ingevulde kolom 206. wanneer ik alles verwijder (= blanco kolom) zou het 0 moeten zijn en het is -33

Ik den dat dit gedeelte van de formule

(SOMPRODUCT((MAAND($M$6:$M$297)>Y3)*($P$6:$P$297="ZONDER")))

de spelbreker is

Wat ik ook doe ik kom bij een lege kolom Y op 239 ipv 206 - of anders heb ik -33 ipv 0 bij een volledig ingevulde kolom Y of anders om....

:shocked:

Uw formule staat in AA3

Mijn probeersel staat in AA4

Bekijk bijlage Map2.xlsx


Greetz

Pascal

 

[Cobbe]

Ik denk dat er iets mis is met je Excel.
Deze formule geeft bij mij in Xl2013 -->16

=SOMPRODUCT((MAAND(A6:A297)>J2)*(G6:G297="ZONDER"))

Klopt deze uitkomst?

Probeer dit eens te openen.

 

[Scallebe]

Cobbe,

16 klopt want dit is de formule om het aantal rijen te tellen die aan de twee voorwaarden voldoen. zowel <1 en zonder, de dubbele getelde dus.

het is ook maar een onderdeel van mijn grote formule.

De opbouw van mijn formule was het aantal rijen (292); -de formule voor de zonders; -de formule voor de <1; +de formule voor de cellen die aan de twee voorwaarden voldoen.

in feite moet er geen rekening gehouden worden met 86 rijen zijnde 53 zonders + 49 gele - de 16 dubbele.

Dus 292 - 86 = 206

Maar als ik de verschillende formules combineer vertrekkende van het tellen van bv de lege cellen klopt het niet en ik denk dat het te maken heeft met het gedeelte formule voor de dubbele.

want die -33 ipv 0 of 239 ipv 206 als eindresultaat is precies de 49 gele -de 16 dubbele.

Is er geen andere manier om het aantal cellen te tellen die aan de twee voorwaarden (<1(of de cel waar die waarde staat) en zonder) voldoet?

Thanks

Greetz

Pascal

 

[Cobbe]

Ik heb alles overgeplakt in xl2016 als waarden en daarna de formules overgenomen.

Dan heb ik de Y-kolom in standaard-opmaak gezet.

Dan heb ik de formule ingepast en schijnt nu een bevredigend resultaat op te leveren.

Kijk maar of mijn uitleg wel klopt.

 

[Scallebe]

Cobbe,

Goede morgen...

Heel even leek het optimistisch en het klopt inderdaad wanneer alle mogelijke cellen zijn ingevuld dan komt de teller op 206/206

Maar wanneer je de kolom volledig leegmaakt (bv nieuw werkjaar) dan is weer het resultaat -33/206 terwijl het 0/206 zou moeten zijn.

Ik heb mij al verloren gezocht

Ik denk nog steeds dat onderstaand gedeelte van de hele formule de spelbreker is :evil::

=SOMPRODUCT((MAAND(A6:A297)>J2)*(G6:G297="ZONDER"))

Is er echt geen ander mogelijkheid om het aantal cellen te tellen die aan de twee voorwaarden voldoet? (de zonders + <1)


Greetz

Pascal

 

[Cobbe]

Je zoekt voortduren de maand van kolom A, en in de hele kolom A is er géén datum te vinden. ?????

 

[Scallebe]

Cobbe,

Sorry, mijn fout. In de originele sheet staan de gegevens soms in andere kolommen.

De juiste formule in het voorbeeld voor het gedeelte voor de / is :

=(AANTALARG($A$6:$A$1000000))-(AANTAL.LEGE.CELLEN(Y6:Y297))-(SOMPRODUCT(--(($P$6:$P$297)="ZONDER")))+(SOMPRODUCT((MAAND($M$6:$M$297)>Y3)*($P$6:$P$297="ZONDER")))

En het gedeelte in de formule dat volgens mij de spelbreker is (met de juiste verwijzing)

(SOMPRODUCT((MAAND($M$6:$M$297)>Y3)*($P$6:$P$297="ZONDER")))

De datums staan inderdaad in de Kolom M en niet in A

Maar ik merk nog foutje op in de volledige formule want de gele worden hier niet geteld.

Ik ga eerst eens mijn formule deftig controleren en ik hou je dan op de hoogte.

Zo kunnen we blijven zoeken natuurlijk...


Mijn grijze cellen staan nog in vakantiemodus... tis nog maar mijn tweede werkdag dit jaar :shocked:

Tot later

Greetz

Pascal

 

[Cobbe]

Ik heb het ontpuzzeld (denk ik bijna zeker) en wel met deze:

="Nog in te geven"&" : "&$Z$1-(AANTALARG($A$6:$A$1000000)-(AANTAL.LEGE.CELLEN($Y$6:$Y$297))-SOMPRODUCT((MAAND($M$6:$M$297)>$Y$3)*(MAAND($M$6:$M$297)<$Y$3)*($P$6:$P$297="ZONDER")))&"/"&(($AA$1-5)-(SOMPRODUCT(--(($P6:$P297)="ZONDER")))-(((SOMPRODUCT(--($Y$3>MAAND((INDIRECT("$N$6:$N$"&$AA$1))-1)))))+(SOMPRODUCT(--($Y$3<MAAND(INDIRECT("$M6:$M$"&$AA$1))))))+(SOMPRODUCT((MAAND($M$6:$M$297)>$Y$3)*($P$6:$P$297="ZONDER"))))

Die $Z$1 kan je nog eventueel vervangen door de achterliggende formule.
Er mankeerde nog een zoekitem in de somproduct formule nl : (MAAND($M$6:$M$297)<$Y$3)
Dat had je tot nu toe nergens vermeld, het zoeken groter dan EN kleiner dan.

 

[Scallebe]

Cobbe,

Je was mij voor...

Ik had de fout ook gezien en had mijn laatste bericht aangepast.

Ik ga zo dadelijk proberen.


Ik hou je op de hoogte


Greetz

 

[Scallebe]

Cobbe,

Als ik het goed heb begrepen dan heb je in uw laatste formule de drie voorwaarden geplaatst, een beetje eenvoudiger dan mijn versie, maar het eindresultaat geeft terug -33/2016, maar...

Ik denk het probleem te hebben gevonden.

Wanneer ik de sheet sorteer op Prestatiecode dan zijn er 49 "Zonders" waarvan 16 die ook geel zijn en dus blanco zijn en blijven. De overige 33 "Zonders" hebben de waarde 0 wat de gebruikers ook wensen in te geven. Daar komt dus de fameuze "33" van. Wanneer ik deze delete dan klopt de rekening wel. Zowel bij een lege kolom als bij een volledig ingevulde.

Als dit niet formulegewijs op te lossen is dan kan ik de cellen een voorwaardelijk opmaak geven die deze cellen een kleurtje geeft en dan moeten ze de cel maar leeg laten. Bij de gele cellen vullen ze ook niets in maar door de kleur is dit ook duidelijk dat er niets moet ingevuld worden. Wanneer de cellen "Zonders" een kleurtje zullen hebben dan is het ook duidelijk dat ze daar geen input moeten invoeren en dus blanco zal blijven. Maar vooral het problem zou opgelost zijn.

Tenzij..... de formule kan aangepast worden

Alvast bedankt voor de moeite he... :thumb:


Greetz

Pascal

 

[Cobbe]

Splits uw formule per berekening en zet die in een cel onder elkaar.
Dan zie je of de berekening klopt per onderdeel, dan kom je er wel zeker uit.
Aantalarg
aantal.als
Somproduct