Omgaan met cellen met nulwaarden, "delen door 0"

[Nootgeval]

Ik zit met een probleem met nulwaarden, veroorzaakt door een klein stukje van een uitgebreide tabel. In het vervolg van de tabel wordt de laatste kolom in de bijlage gebruikt als noemer bij een deling. Daardoor krijg ik dan de melding "delen door 0". Dat is niet de bedoeling. In de bijlage zit een voorbeeldje op basis van mijn tabel.

Ik heb twee producenten A en B, en die maken de producten X en Y. Producent A heeft recht op 40% van X en Y tezamen, producent B heeft recht op 60% van X en Y tezamen. Op hun productie worden kosten in rekening gebracht in precies dezelfde verhouding. Maar ze zijn vrij de aantallen te kiezen, dus A mag ook alle X'en produceren. Alleen in dat geval wordt komt er voor B's "0" X'en, 27% aan kostenaandeel in de tabel. In het vervolg van de tabel leidt dat tot "delen door 0" problemen.

De bedoeling is nu dat die 27% opgeteld wordt bij de wel door B te produceren Y'en (33%+27%). Dan krijgt dus A 18% toegerekend aan X, 22% aan Y, samen weer 40%. En B krijgt dan 0% aan X maar 60% aan Y. De verhouding blijft 40%-60%.

Als hiervoor een formule is, of eventueel extra kolommen handiger zijn, zie ik het graag. Het is een stukje van de tabel, dus het kunnen ook producenten A-G en producten X tot Z en verder zijn. Het moet dus wel "handig" zijn en niet steeds groter worden. Ik ben me al een ongeluk aan het puzzelen geweest met ALS-formules, veel verder kom ik nog niet met Excel. Alvast dank!

 

[MarcelBeug]

Volgens mij klopt er iets niet in de logica. Uit de formules lees ik (met de voorbeeldgetallen):
X is 45% van de totale productie. Daar krijgt A 40% van --> 18% en B 60% --> 27%.
Y is 55% van de totale productie. Daar krijgt A 40% van --> 22% en B 60% --> 33%.

Het lijkt erop dat je zowel wilt uitkomen op totaal 40% voor A en 60% voor B als 45% voor X en 55% voor Y.
In de formules blijft daardoor ieders aandeel in de productie buiten beschouwing, dus vandaar dat X/B 27% blijft, ook als hij geen productie heeft.

Volgens mij is het geen kwestie van 0-waarden voorkomen, maar om de logica aan te passen.
Ter illustratie: stel dat B ervoor kiest om 1 exemplaar X te produceren, zou 27% dan wel goed zijn?

Ik zou verwachten dat je de percentages zou verdelen over de productie-aantallen:
Voor A bij X 45.000 en Y 5.000 zou dit 36% resp. 4% zijn.
Voor B bij X 0 en Y 50.000: 0% resp. 60%.

Kortom het is mij niet duidelijk wat die percentages nu eigenlijk voorstellen.

 

[Nootgeval]

Hallo Marcel,

Bedankt voor het meedenken, toch is de opzet goed volgens mij.

Inderdaad, 40% en 60% zijn de aandelen waar ze recht op hebben en die ook hun aandeel in de kosten blijven. In totaal wordt X / Y in de verhouding 45 / 55 geproduceerd. A en B zijn gebonden aan een vastgelegde productie op basis van 40% en 60%, en ze betalen daarover een kostenaandeel van een vastgelegde 40 en 60%.

De productie in totaal is 45.000 X'en en 55.000 Y'en en de totale productie van X en Y samen, blijft 100.000. Dus Producent B kan 1 exemplaar X of 10.000 produceren, maar het percentage bij Kostenaandeel van B zal niet veranderen. Dat zie je wel als B bijvoorbeeld 5000 X maakt. Daaruit volgt dat A er 5000 minder maakt, dus 40.000 X voor producent A. De verdeling van Y blijft dezelfde tussen A en B.

Alleen als ik "0" invul in de kolom Productie X van B (en dus deze bij A zijn X'en optel) blijft de 27% weliswaar staan, maar omdat ik verderop in de tabel niet "delen door 0" wil krijgen, wil ik dan het percentage 27% laten bijtellen bij de 33% zodat B op 60% Kostenaandeel zit (met alleen Y!).

Dus zo dat B's X'en "0" zijn en het percentage van B's X'en ook op "0" staat. B produceert namelijk dan geen X en dus hoort er geen apart percentage voor X voor B bij. Het gaat me dus om een slimme functie in Excel die ook met meer dan alleen A en B als producent en X en Y als productie kan werken. Zodra bij B "0" productie ingevuld wordt, wordt het percentage Kostenaandeel over wat wel geproduceerd herverdeeld over de productie die B wel doet. Voor nu alleen met Y.
Kortom: als ik het heb over Producent B: ik krijg niet voor elkaar in cel D13 een formule te maken, die als in C13 "0" (idem) staat, in D13 ook "0" verschijnt en die dan de 27% in D13 optelt bij D14, en andersom, en hetzelfde voor Producent A (cellen D11, D12).
Hoe die herverdeling plaatsvindt bij meer Types, zoals Z, Delta en Gamma, daar puzzel ik dan wel weer verder op, maar de bedoeling is dat het geen bladen vol wordt maar dat het liefst met een gewone Functie in Excel gebeurt.

Nou dat was een puzzel om op te schrijven, ik hoop zo wat duidelijkheid te hebben gegeven.

 

[sylvester-ponte]

hallo Noot Geval, kun je ook aangeven wat er bij producent B moet komen als hij niets produceert? dus geen product A en ook geen product B

 

[Nootgeval]

Hallo,
Bedankt voor je reactie, de bedoeling is dat zowel producent A en producent B altijd produceren. Namelijk het product X en Y (de poet moet verdeeld worden bij wijze van spreken). A altijd 40% en B altijd 60% bij Kostenaandeel (18%+22%= 40% voor Producent A en 27%+33%=60% voor producent B), en altijd in totaal 45.000 X en 55.000 Y.

Het probleem ontstaat bij "0" productie (het tweede vak, kolom Productie) en dat zou ik graag opgelost zien als in het derde vak (kolom Kostenaandeel A, B). De gedachte daarbij is dan: er worden geen X'en door B gemaakt. Daarom is het bij "0" productie van X ook 0% Kostenaandeel, de 27% wordt toegevoegd bij de 33% van product Y van producent B).

Het is een onderdeeltje van een veel uitgebreider blad, waarin nu alsmaar "Deel/0" staat en waarbij bij "0" productie X van producent B toch een percentage staat (nu 27%). Dat moet daar 0% worden en daarom moet de 27% bij de productie Y van producent B opgeteld worden. Dat dan met een functie van Excel, zodat ook producent C en D en type Z, Delta en Gamma erin kunnen. Zie ook na "Kortom" in bovenstaande bericht. Dank voor het meedenken.

 

[MarcelBeug]

Eerlijk gezegd heb ik nog steeds een onderbuikgevoel dat er iets niet klopt. Waarom vang je verderop het delen door 0 niet af?

Maar goed, ik heb in de bijlage gedaan wat je vraagt, met de formules in de groene vakjes van Blad1.
De oorspronkelijke berekening kun je het beste handhaven als tussenresultaat, waarna de definitieve percentages worden berekend door per producent alle percentages met een nul-productie te verdelen over - en op te tellen bij - de percentages met productie > 0.
De formule in E12 is gekopieerd van E11; de formule in E14 is gekopieerd van E13.

Met de layout die jij gekozen hebt, is het verschrikelijk omslachtig om de formules goed te krijgen. Met een hele grote kans op fouten.
Daarom heb ik Blad2 toegevoegd met een wat andere layout, zodat je in ieder geval uniforme formules hebt: de basisformules staan in G2 en H2 en deze zijn gekopieerd naar beneden.

Hier is-tie:
Bekijk bijlage Uitsnede tabel MB.xlsx

Edit: de formules in Blad1!D4: D7 niet gebruiken. Dat was een poging om in 1 keer op de juiste percentages uit te komen, maar dat gaat niet goed.

 

[Nootgeval]

Bij later afvangen, bijvoorbeeld met de "Als" functie zoals ik aangaf, verschijnt eerst "0" productie met een >"0" percentage, maar pas een kolom of wat verder verschijnt "Delen/0". Gelijk naast de kolom met de oorspronkelijke percentages, zoals in de basisformule die je meegeeft, toon ik gelijk aan hoe het "0" probleem opgelost wordt.

Volgens mij doet je Blad2 precies wat het moet doen, althans voor 2 producenten (A, B) en 2 typen (X, Y). Maar in dit soort functies ben ik echt niet thuis (veel verder dan ALS kom ik niet ). Dus deze moet ik uitpluizen. Zou je hem nog met 3 x 3 kunnen maken, bijvoorbeeld A 40, B 30, C 30 en X, Y en Z? Dan kan ik het verschil in opbouw zien en dat helpt mij bij het doorgronden van de formule. Laten we zeggen dat als voor B X dan "0" is, dat de helft aan B's Y'en en de andere helft aan B's Z'en toegerekend wordt.

FYI Als je het leuk vindt, kan ik een veel grotere uitsnede PM'en, in het kader van een artikel dat ik aan het schrijven ben moet ik het model toch al beschrijven, dan zie je wat beter waarover het gaat en waarom. Alvast bedankt voor je hulp tot nu toe.

 

[MarcelBeug]

...Zou je hem nog met 3 x 3 kunnen maken, bijvoorbeeld A 40, B 30, C 30 en X, Y en Z?...

Deze vraag betekent dat je nog "hangt" in de layout van Blad1, waarbij je telkens formules moet aanpassen als je gegevens wijzigt.
In Blad2 kun je gewoon de betreffende gegevens invoeren en ervoor zorgen dat de formules in kolom G en H doorlopen t/m de laatst gevulde regel in de kolommen D t/m F.
Als er extra regels bij moeten komen in G en H (na het invullen van de gegevens in kolom D t/m F), selecteer dan de laatste gevulde regel (in mijn voorbeeld G5 en H5) en dubbelklik het kleine zwarte plusje in de hoek rechtsonder.
Dan worden de formules doorgetrokken t/m de laatst gevulde regel in kolom D t/m F.
Alternatief: je kunt ook het zwarte plusje naar beneden slepen.

Zodoende werkt het gewoon met elk aantal producenten en typen.
Probeer maar!

De formule in G2 (berekening van de basis percentages):

=VERT.ZOEKEN(D2;A:B;2;0)*SOM.ALS(E:E;E2;F:F)/SOM(F:F)

De VERT.ZOEKEN functie zoekt het percentage op in kolom B bij de producent in kolom A met code D2.
SOM.ALS(E:E;E2;F:F) berekent de som van de waarden in kolom F van de regels waarvan het type in kolom E gelijk is aan het type in E2.
SOM(F/F) levert het totaal van kolom F op.

Als je deze formule naar beneden sleept, dan veranderen de D2 en E2 vanzelf in D3 en E3 etcetera.

De formule in H2 (berekenen van de uiteindelijke percentages):

=ALS(F2=0;0;G2+SOMMEN.ALS(G:G;D:D;D2;F:F;0)/AANTALLEN.ALS(D:D;D2;F:F;"<>0"))

Als de productie in F2 = 0, dan 0, anders:
het basispercentage in G2 plus de som van de basispercentages in kolom G, waarbij de producent gelijk is aan de producent in D2, EN waarbij de productie gelijk is aan 0,
gedeeld door het aantal percentages van de betreffende producent met productie > 0.

Ook hier veranderen de celreferenties weer, als je de formule kopieert (F2 wordt F3 etcetera).

 

[Nootgeval]

Volgens mij is het hem wel inderdaad, maar ik moet zowel VERT.ZOEKEN, als SOM, als SOMMEN, als AANTALLEN nog doorgronden... Weet je wellicht een handige website/ publicatie waarin het niet-standaardwerk in EXCEL, maar juist dit soort functies nader worden toegelicht, want in de help van EXCEL kom ik niet veel verder, en zoekacties in Google leveren honderden resultaten. En een cursus EXCEL is vaak weer alleen het basiswerk. Ik zou juist het soort functies dat je hier gemaakt hebt willen kunnen doorgronden en maken.

 

[MarcelBeug]

Het handigste is om in de formulebalk op de naam van de functie te gaan staan, dan zie je al een popup met de functienaam en de betekenis van de argumenten.
Die argumenten kun je aanklikken, zodat je precies ziet welk deel van de formule bij welk argument hoort.

Verder kun je nog de fx links van de formule aanklikken, dan krijg je de argumenten van de functie met toelichting.
Last but not least kun je ook nog de helpinformatie bij de functie opvragen vanuit deze popup of vanuit de eerdergenoemde popup door daarin de functienaam aan te klikken.

Voor basis- tot zeer geavanceerde cursussen kun je bijvoorbeeld zoeken op YouTube.

En het staat me bij dat een gewaardeerd mede-forummer nog wel een linkje weet naar een Belgische site...

 

[Ad Becude]

Kijk hier maar eens:
www.gratiscursus.be

 

[MarcelBeug]

Kijk hier maar eens:
www.gratiscursus.be

Dat bedoelde ik nou! :thumb:

 

[Nootgeval]

Heel veel dank voor jullie aanwijzingen, ik markeer de vraag als opgelost.