Puzzel: combinatie van uitkomsten moet leiden tot 1 uitkomst

[Termaat]

Hallo,

 

In een excel document wil ik graag wat uitkomsten bij elkaar 'optellen'. De uitkomst daarvan leidt tot een van 5 tekstuele uitkomsten. De moeilijkheid zit hem in het combineren van ranges en formules. Mijn uitleg:

in kolom a kan ik een score invoeren van 55-120. Idem in kolom b. In kolom c,d,e,f kan een score komen van 0 tot 10. Een combinatie van de scores in deze kolommen kunnen dus leiden naar 5 verschillende tekstuele uitkomsten, die middels een formule in de kolom advies terecht zouden moeten komen.

 

Dan de moeilijkheid:

• Als in kolom a een score is van 55-75 EN in de kolommen c t/m f twee kolommen zijn waarbij de score tussen de 0,25 en 0,5 valt, waarbij tenminste 1 van de kolommen kolom c of d moet zijn dan levert dat de tekst ok op. Als er een score is in kolom b, dan moet die ook binnen dezelfde range van a vallen om dezelfde tekst te genereren. Als er in kolom b geen score staat, moet deze niet meegeteld worden. Als de score in kolom b buiten de range 55-75 valt, dan moet de uitkomst in advies 'niet ok' worden. 
• Als in kolom a een score is van 55-75 EN in de kolommen c t/m f twee kolommen zijn waarbij de score hoger is dan 0,5, waarbij tenminste 1 van de kolommen kolom c of d moet zijn dan levert dat de tekst prima op. Als er een score is in kolom b, dan moet die ook binnen dezelfde range van a vallen om dezelfde tekst te genereren. Als er in kolom b geen score staat, moet deze niet meegeteld worden. Als de score in kolom b buiten de range 55-75 valt, dan moet de uitkomst in advies 'niet ok' worden. 
• Als in kolom a een score is van 75-90 EN in de kolommen c t/m f twee kolommen zijn waarbij de score tussen de 0,25 en 0,5 valt, waarbij tenminste 1 van de kolommen kolom c of d moet zijn dan levert dat de tekst uitstekend op. Als er een score is in kolom b, dan moet die ook binnen dezelfde range van a vallen om dezelfde tekst te genereren. Als er in kolom b geen score staat, moet deze niet meegeteld worden. Als de score in kolom b buiten de range 55-75 valt, dan moet de uitkomst in advies 'niet ok' worden.
• Als in kolom a een score is van 90-120 EN in de kolommen c t/m f twee kolommen zijn waarbij de score tussen de 0,25 en 0,5 valt, waarbij tenminste 1 van de kolommen kolom c of d moet zijn dan levert dat de tekst grandioos op. Als er een score is in kolom b, dan moet die ook binnen dezelfde range van a vallen om dezelfde tekst te genereren. Als er in kolom b geen score staat, moet deze niet meegeteld worden. Als de score in kolom b buiten de range 55-75 valt, dan moet de uitkomst in advies 'niet ok' worden.  
• Als in kolom a een score is van 75-90 EN in de kolommen c t/m f twee kolommen zijn waarbij de score hoger is dan 0,5, waarbij tenminste 1 van de kolommen kolom c of d moet zijn dan levert dat de tekst enorm op. Als er een score is in kolom b, dan moet die ook binnen dezelfde range van a vallen om dezelfde tekst te genereren. Als er in kolom b geen score staat, moet deze niet meegeteld worden. Als de score in kolom b buiten de range 55-75 valt, dan moet de uitkomst in advies 'niet ok' worden. 

Dit zijn de combinaties die een tekst opleveren. Ik ben op zoek naar de mogelijkheid om middels 1 formule één van deze 5 uitkomsten automatisch te generen in de kolom advies. Ik heb zelf al e.e.a. met de als-functie geprobeerd en zo ook de 'en' en 'of' functie, maar ik krijg niet alle voorwaarden in 1 formule. 

 

Wie is er in voor een puzzel?!

 

Alvast hartelijk dank voor je reactie en meedenken!

voorbeeld.xlsx

[bucky]

Termaat,

Ik ben maar een simpele jongen, maar:

Als ik naar de eerste uitslag kijk kom ik voor A en B al tot 12 mogelijke resultaten. Voor C t/m F kom ik op 5 mogelijke resultaten. Dat zijn dus 62 mogelijkheden om tot het resultaat "ok" of "niet ok" te komen. Dus voor alle 5 uitslagen in één formule is dat dus 62 maal 5 is 320 mogelijkheden.

Als ik het niet goed zie vertel dan maar hoe het wel zit.

[alpha]

Typ in G2 en kopieer naar beneden:

=ALS(EN(EN(A2>=55;A2<75);EN(OF(B2=0;EN(B2>=55;B2<75));EN(AANTALLEN.ALS(C2:F2;">0,25";C2:F2;"<0,5")>1;OF(EN(C2>0,25;C2<0,5);EN(D2>0,25;D2<0,5)))));"Ok";ALS(EN(EN(A2>=55;A2<75);EN(OF(B2=0;EN(B2>=55;B2<75));EN(AANTALLEN.ALS(C2:F2;">0,5")>1;OF(EN(C2>0,5);EN(D2>0,5)))));"Prima";ALS(EN(EN(A2>=75;A2<90);EN(OF(B2=0;EN(B2>=75;B2<90));EN(AANTALLEN.ALS(C2:F2;">0,25";C2:F2;"<0,5")>1;OF(EN(C2>0,25;C2<0,5);EN(D2>0,25;D2<0,5)))));"Uitstekend";ALS(EN(EN(A2>=75;A2<90);EN(OF(B2=0;EN(B2>=75;B2<90));EN(AANTALLEN.ALS(C2:F2;">0,5")>1;OF(EN(C2>0,5);EN(D2>0,5)))));"Enorm";ALS(EN(EN(A2>=90;A2<=120);EN(OF(B2=0;EN(B2>=90;B2<=120));EN(AANTALLEN.ALS(C2:F2;">0,25";C2:F2;"<0,5")>1;OF(EN(C2>0,25;C2<0,5);EN(D2>0,25;D2<0,5)))));"Grandioos";"Niet Ok")))))

[Termaat]

Alpha,

 

Dank je wel voor je reactie! Na enige kleine modificaties kan ik de formule gebruiken zoals ik het beoogd had.

 

Vriendelijke groet,

 

S.Termaat

[alpha]

En wat zijn die modificaties ?

[Termaat]

De uiteindelijke formule ziet er zo uit (eigenlijke antwoorden weggelaten en vervangen door nummers):

 

=ALS(EN(AANTAL.ALS(T2:W2;">=0,5")>1;OF(EN(U2>=0,5);EN(W2>=0,5));EN(O2>=55;O2<=74));"1";ALS(EN(AANTAL.ALS(T2:W2;"0,25")>2;EN(O2>=91;O2<120));"2";ALS(EN(AANTALLEN.ALS(U2;"0,25";W2;"0,25")>1;EN(O2>=50;O2<145));"2";ALS(EN(AANTAL.ALS(T2:W2;">=0,5")>1;OF(EN(U2>=0,5);EN(W2>=0,5));EN(O2>=75;O2<=80));"1";ALS(EN(AANTALLEN.ALS(T2:W2;">0,249";T2:W2;"0,5")>1;OF(EN(U2>0,249;U2<0,5);EN(W2>0,249;W2<0,5));EN(O2>=75;O2<=90));"3";ALS(EN(AANTALLEN.ALS(T2:W2;">=0,5")>1;OF(EN(U2>=0,5);EN(W2>=0,5));EN(O2>=81;O2<=90));"4";ALS(EN(AANTALLEN.ALS(T2:W2;">0,249";T2:W2;"0,5")>1;OF(EN(U2>0,249;U2<0,5);EN(W2>0,249;W2<0,5));EN(O2>=55;O2<=74));"4";ALS(EN(AANTALLEN.ALS(T2:W2;">0,249";T2:W2;"0,5")>1;OF(EN(U2>0,249;U2<0,5);EN(W2>0,249;W2<0,5));EN(O2>=91;O2<=120));"5";ALS(EN(AANTAL.ALS(T2:W2;">=0,5")>1;OF(EN(U2>=0,5);EN(W2>=0,5));EN(O2>=91;O2<120));"5";"niets")))))))))

 

Gr,

 

S.Termaat

23 november 2015 aangepast door Termaat

[alpha]

Jouw wijzigen zijn niet allemaal juist.

Dit gedeelte:

ALS(EN(AANTALLEN.ALS(U2;"0,25";W2;"0,25")>1;EN(O2>=50;O2<145));"2"

heeft geen enkel nut en kun je verwijderen. AANTALLEN.ALS(U2;"0,25";W2;"0,25") kan nl. nooit groter worden dan 1, dus wat er na die 1 staat, daar wordt al niet meer naar gekeken. Je moet aantallen.als (en aantal.als) ook niet voor 1 cel gebruiken.

 

Verder kun je best alles wat hetzelfde resultaat (getal 1 t/m 5) moet opleveren in 1 opdracht onderbrengen, dan wordt je formule ook niet zo lang.

Ook andere dingen kunnen nog wel korter denk ik, maar daarvoor zou ik een preciese omschrijving van het gewenste resultaat in gewoon Nederlands moeten zien (zoals in je eerste bericht).

[Termaat]

Hallo alpha,

 

Inmiddels ziet de formule er zo uit. Niet helemaal volgens jouw aanwijzingen, maar vooralsnog lijkt het te doen wat het moet doen:

=ALS(EN(AANTAL.ALS(S2:V2;">=0,5")>1;OF(EN(T2>=0,5);EN(V2>=0,5));EN(N2>=55;N2<=74));"a";ALS(EN(AANTAL.ALS(S2:V2;"0,25")>2;EN(N2>=91;N2<120));"b";ALS(EN(AANTALLEN.ALS(T2;"0,25";V2;"0,25")>1;EN(N2>=50;N2<145));"b";ALS(EN(AANTAL.ALS(S2:V2;">=0,5")>1;OF(EN(T2>=0,5);EN(V2>=0,5));EN(N2>=75;N2<=80));"a";ALS(EN(AANTALLEN.ALS(S2:V2;">0,249";S2:V2;"0,5")>1;OF(EN(T2>0,249;T2<0,5);EN(V2>0,249;V2<0,5));EN(N2>=75;N2<=90));"c";ALS(EN(AANTALLEN.ALS(S2:V2;">=0,5")>1;OF(EN(T2>=0,5);EN(V2>=0,5));EN(N2>=81;N2<=90));"d";ALS(EN(AANTALLEN.ALS(S2:V2;">0,249";S2:V2;"0,5")>1;OF(EN(T2>0,249;T2<0,5);EN(V2>0,249;V2<0,5));EN(N2>=55;N2<=74));"d";ALS(EN(OF(AANTALLEN.ALS(S2:V2;">=0,5")=1;AANTALLEN.ALS(S2:V2;">0,249";S2:V2;"0,5")=1);EN(N2>=55;N2<=80));"d";ALS(EN(AANTALLEN.ALS(S2:V2;">0,249";S2:V2;"0,5")>1;OF(EN(T2>0,249;T2<0,5);EN(V2>0,249;V2<0,5));EN(N2>=91;N2<=120));"e";ALS(EN(AANTAL.ALS(S2:V2;">=0,5")>1;OF(EN(T2>=0,5);EN(V2>=0,5));EN(N2>=91;N2<120));"d";"f"))))))))))

 

Is het overigens niet zo dat >1 betekent: 'komt meer dan 1 keer voor'?

[alpha]

 

Inmiddels ziet de formule er zo uit. Niet helemaal volgens jouw aanwijzingen, maar vooralsnog lijkt het te doen wat het moet doen....

Als jij er tevreden mee bent, dan is dat natuurlijk prima, maar ik zie dat je de twee aanwijzingen die ik in mijn vorig bericht gaf niet ter harte hebt genomen. Dat is je goed recht natuurlijk: vrijheid blijheid.

Er staan wederom overtollige zaken in je formule en een aantal dingen kunnen korter. Ik geef twee voorbeelden:

 

1. Dit gedeelte:

=ALS(EN(AANTALLEN.ALS(T2;"0,25";V2;"0,25")>1;EN(N2>=50;N2<145));"b";"")

kun je volledig verwijderen, dat zal geen enkel effect op de uitkomst hebben.

Waarom? Omdat =AANTALLEN.ALS(T2;"0,25";V2;"0,25") NOOIT groter dan 1 kan worden. 

Test het maar eens door om het even welke getallen in T2 en V2 in te vullen.

Advies: bestudeer goed de functie AANTALLEN.ALS, die wordt in de helpfile van Excel prima uitgelegd.

 

2. Dit gedeelte :

=ALS(EN(AANTAL.ALS(S2:V2;">=0,5")>1;OF(EN(T2>=0,5);EN(V2>=0,5));EN(N2>=55;N2<=74));"a";"")

kun je inkorten tot

=ALS(EN(AANTAL.ALS(S2:V2;">=0,5")>1;N2>=55;N2<=74);"a";"")

enz, enz....

 

Is het overigens niet zo dat >1 betekent: 'komt meer dan 1 keer voor'?

Dat hangt ervan af hoe je het gebruikt.

In bv. =A1>1 kijk je of de cel-WAARDE van A1 groter is dan 1, dat kan waar of onwaar zijn.

In =AANTAL.ALS(A1:D1;">1") kijk je naar het aantal cellen in A1:D1 dat groter is dan 1.

Het heeft natuurlijk geen zin om aantal.als of aantallen.als op 1 cel toe te passen, want wat er dan ook in die ene cel staat, het aantal kan nooit groter zijn dan 1.