Eventuele lege cellen overslaan in berekening

[JMatrix]

Hallo allemaal,

Ik ben bezig met een ietswat gecompliceerde Excel-berekening voor mijn onderzoek. Hieronder de situatieschets.

Ik heb een klein model gemaakt met de volgende cellen: Sale1, Sale2, Sale3, Sale4, Sale5 en TotalSale (som S1 t/m S5).

Nu maak ik gebruik van een iets wat gecompliceerde, samenhangende berekening (voor een Entropy-measure) waarbij alle cellen (na/met een LN-functie) door zichzelf worden gedeeld. Dit gaat prima zolang alle cellen een waarde hebben anders dan nul. Nu komt het echter voor dat een Sale-cel (bijvoorbeeld Sale4) de waarde 'nul' heeft. Op dat moment loopt mijn gehele formule vast met de melding 'deel/0'.

Mijn vraag is hoe ik deze 'nul'-cel kan overslaan in mijn formule om vervolgens door te werken met de andere cellen. Is hier een algemene oplossing voor?

Alvast bedankt!

 

[bandito bob]

En hoe ziet de formule die je nu hebt er dan uit?
Of zou je een voorbeeldje kunnen posten?

 

[JMatrix]

Uiteraard. Zie bijlage.

Ik heb overigens 'ontdekt' dat als ik de waarde oneindig klein maak in plaats van 'nul' dit ook hetzelfde resultaat geeft. Echter, dit is m.i. geen nette oplossing.

Alvast bedankt!

 

[bandito bob]

De formule kan simpel als volgt:

=ALS(A5<1;0;(A5/G5)*LN(1/(A5/G5)))+ALS(B5<1;0;(B5/G5)*LN(1/(B5/G5)))+ALS(C5<1;0;(C5/G5)*LN(1/(C5/G5)))+ALS(D5<1;0;(D5/G5)*LN(1/(D5/G5)))+ALS(E5<1;0;(E5/(G5)*LN(1/(E5/G5))))

Dus simpel met overal een ALS functie die kijkt of het getal >=1 is. Ik heb de grens op 1 gezet, omdat anders de noemer in dit gedeelte: 1/(A5/G5) te klein wordt. Excel zal als het te klein wordt afronden naar 0, zodat je een fout melding krijgt.

Je kan ook volgende matrix (ik doe je naam eer aan) formule gebruiken. Deze is veel korter.

=SOM(ALS(A5:E5>1;(A5:E5/G5)*LN(1/(A5:E5/G5))))

Let op!! matrix formule, bevestigen met CTRL-SHIFT-ENTER.

 

[JMatrix]

Bedankt Paul, beide formules werken perfect.

Mijn feitelijk model ziet er gecompliceerder uit, maar hier moet ik zeker mee verder kunnen.

 

[bandito bob]

Dan graag de vraag nog even op opgelost zetten:thumb:

 

[JMatrix]

Hoi Paul (en anderen),

Toch nog even een vervolgvraag. Misschien is het makkelijker om deze hier te stellen.

Als bijlage een vervolg op mijn model. Nu wordt er gewerkt met twee groepen, waarbinnen iedere groep drie 'sales-groepen' heeft. Nu heb ik het een en ander met matrixen kunnen optimaliseren, alleen de rechtertabel lukt me niet goed.

Is het mogelijk om de berekeningen door midel van een matrix hier ook te verbeteren/inzichtelijk te kunnen maken? Nu bestaat dit model uit twee groepen, maar dit zal waarschijnlijk uitgebreid gaan worden met nog twee stuks (met weer allemaal drie sales); de formules worden zo wel erg lang.

Graag weer wat hulp, want met matrixen werkt het inderdaad overzichtelijker.

Alvast bedankt!

 

[bandito bob]

Voor cel N6 de matrix formule::

=SOM(ALS(C6:E6>1;(C6:E6/A6)*LN(1/(C6:E6/A6)));ALS(H6:J6>1;(H6:J6/A6)*LN(1/(H6:J6/A6))))

Die 2 andere zou ik gewoon zo houden.

 

[JMatrix]

Hallo,

Misschien is het niet raadzaam om dit topic na een week weer op te halen, maar naar mijn mening blijven de juiste zaken op deze manier wel bij elkaar staan. Anders, mijn excuses.

Ik ben weer aan de slag gegaan met het model en probeer nu de berekeningen iets anders uit te werken. Ik heb wederom het model bijgesloten en hieronder volgt een toelichting.

Het bovenste schema (lopend tot en met rij 12) is het model zoals voorheen gebruikt, dit werkt prima. Hierbij functioneren de kolommen G en L als tussenberekeningen.

Nu heb ik dit model gekopieerd naar de onderste rijen en nu probeer ik de berekeningen van kolom O (O21 t/m O27) uit te voeren, zonder gebruik te maken van de kolommen G en L.

Ik stuit nu op het probleem dat de uitkomst van cel O24 niet gelijk is aan cel O9, hetgeen wel zou moeten. Ik ben erachter gekomen dat dit komt doordat vanaf deze observatie GROUP2 waarden begint te krijgen.

Ik heb heb al vanalles nagelopen, maar blijkbaar klopt er iets nu in mijn matrix-berekening van GROUP2. Iemand die hierbij kan helpen?

Alvast bedankt!

 

[JMatrix]

Het openen van de bijlage levert bij mij problemen op. Hierbij nogmaals, indien noodzakelijk.

 

[bandito bob]

Probeer voor cel O21 de volgende matrix formule eens:

=SOM(ALS(C21:E21>1;(C21:E21/F21)*LN(1/(C21:E21/F21))))*(F21/A21)+SOM(ALS(H21:J21>1;(H21:J21/K21)*LN(1/(H21:J21/K21))))*(K21/A21)

bevestigen met CTRL-SHIFT-ENTER.

 

[JMatrix]

Hoi Paul, dit lijkt inderdaad te werken. Bedankt!

Ik zie alleen dat je de 'basis-forumule' hebt veranderd. Klopt dit? Was het niet mogelijk om mijn gegeven formule aan te houden? Deze was namelijk gebaseerd op een wetenschappelijke, wiskundige formule.

Alvast bedankt!

 

[bandito bob]

Volgens mij heb ik alleen de 2 formules samengevoegd tot 1 grotere.