somproduct werkt niet meer

[missye82]

Goedemiddag allen,

Ik heb een vrij simpele berekening in
Kolom J: G2-F2
Kolom K: H2-G2
Kolom L: H2-F2

Op deze manier krijg ik ook een hoop negatieve waarden en onterechte 0 waarden. Daarom heb ik de lege cellen willen uitsluiten door deze formule:

=ALS(H2="";"";ALS(G2="";"";SOM(H2-G2))).

Kolom J t/m L zijn geen probleem meer. Nu doet de somproduct (kolom O en P ) niet meer wat het wel deed zonder alle lege datum cellen in kolom A t/m H.
Ik heb ook geprobeerd de formule te wijzigen als hieronder zodat de lege cellen wederom niet worden meegenomen. Maar dat werkt niet.

=SOMPRODUCT(--((DATUM(JAAR($F$2:$F$2700);MAAND($F$2:$F$2700);1)=N2)*($J$2:$J$2700)* )*($J$2:$J$2700)<>””))/SOMPRODUCT(((DATUM(JAAR($F$2:$F$2700);MAAND($F$2:$F$2700);1)=N2)*1))

Ook heb ik bedacht de negatieve waarden te voorkomen door overal (dus in de oorspronkelijk lege cellen) een datum in de toekomst in te voeren. Maardoor kolom K + L ook soms leeg zijn in 1 rij, geeft het als resultaat een onterechte 0 waarde.

Zien jullie misschien een manier om de somproduct te laten werken?

Alvast bedankt,

MissyE

Bekijk bijlage helpmij_Jim.xlsm

 

[gelens]

Hallo

Ik heb een vrij simpele berekening in
Kolom J: G2-F2
Kolom K: H2-G2
Kolom L: H2-F2

De formule in kolom L komt niet overeen met jouw tekst hierboven. In L staat nl. F2-H2
Daar komen dus de negatieve waarde vandaan.
Wat is het probleem met de 0 als resultaat in de kolommen J,K en L ?

 

[robdgr]

Wijzig de somproduct in kolom O in

=ALS.FOUT(SOMPRODUCT((JAAR($F$2:$F$2700)=JAAR($N2))*(MAAND($F$2:$F$2700)=MAAND($N2))*($J$2:$J$2700>0)*($J$2:$J$2700))/SOMPRODUCT((JAAR($F$2:$F$2700)=JAAR($N2))*(MAAND($F$2:$F$2700)=MAAND($N2))*($J$2:$J$2700>0));"")

en in kolom P

=ALS.FOUT(SOMPRODUCT((JAAR($H$2:$H$2700)=JAAR($N2))*(MAAND($H$2:$H$2700)=MAAND($N2))*($L$2:$L$2700>0)*($L$2:$L$2700))/SOMPRODUCT((JAAR($H$2:$H$2700)=JAAR($N2))*(MAAND($H$2:$H$2700)=MAAND($N2))*($L$2:$L$2700>0));"")

 

[missye82]

Wanneer ik deze formule 1 op 1 overneem, geeft het overal blanko cellen, wanneer ik in kolom J tm L de "als"-functie gebruik. Dat zou betekenen dat het overal ergens een fout ziet, voor zover ik het begrijp.
Wanneer ik in kolom J tm L de "als"-functie niet gebruik werkt de voorgestelde formule(s) prima.

Alleen heb ik een kleine correctie moeten doorvoeren, aangezien sommige handelingen dezelfde dag worden afgeronden (dus 0 dagen). Hierdoor is de formule gewijzigd in:

=ALS.FOUT(SOMPRODUCT((JAAR($F$2:$F$2700)=JAAR($N2))*(MAAND($F$2:$F$2700)=MAAND($N2))*($J$2:$J$2700>[B]=[/B]0)*($J$2:$J$2700))/SOMPRODUCT((JAAR($F$2:$F$2700)=JAAR($N2))*(MAAND($F$2:$F$2700)=MAAND($N2))*($J$2:$J$2700>[B]=[/B]0));"")

Maar nu zijn er doordat er in kolom J tm L de "als"-functie niet meer wordt gebruikt ook "onterechte 0-waarden" zie bv K5 -11 en de terechte
0-waarde K4.


Is hiervoor ook een oplossing?

 

[robdgr]

Tel bij het aantal dagen standaard 1 op, dan vermijd je een deel van de nullen.

 

[missye82]

Beste Rob,

Dan worden de onterechte nullen toch ook gewoon +1.

 

[robdgr]

Als iets op dezelfde datum plaatsvindt, mag je daar een 1 zien staan.