Formule samenstelling product

[jesper2019]

Goedemorgen allemaal,

Ik ben op zoek een formule om de juiste samenstelling van een product te berekenen.

De gebruiker zoekt in tab1 op de elementen G, B en P. Na het invullen van deze waarden dient de formule op zoek te gaan in het tabblad database naar de juiste samenstelling.

In de database zijn de belangrijkste elementen van verschillende producten opgenomen. De elementen (kolom G, B en P) zijn uitgedrukt in percentages.

Na het invullen van de zoekwaarden G, B en P in tabblad 1(percentages) dient de formule te retourneren welke samenstelling van producten nodig is om aan de zoekwaarden te voldoen. Bijv. 10% product A, 80% product B en 10% product C.

Hopelijk is het zo duidelijk.

 

[Mde]

geef in het voorbeeld eens aan wat de verwachte uitkomst is.
ik snap er nog niks van

 

[sylvester-ponte]

hier een gokje: wel alles op 1 blad gezet en je bestand een beetje sleepbaar gemaakt.
ik weet niet of je de afwijkingen moet optellen of vermenigvuldigen of....
voor mijn gemak het ik hulp kolommen gebruikt het kan ook zonder.

 

[roeljongman]

Het is voor mij ook een gok.

Ik heb een draaitabel gebruikt met slicers en telkens als je G B of P kiest muteert het lijstje producten die de geselecteerde combinatie(s) bevat.
Voordeel van deze optie kan zijn dat je ook kan filteren op 1 ingredient van een bepaalde hoeveelheid.

 

[jesper2019]

Hierbij even aan nadere toelichting. Mijn excuses, ik heb het wellicht verkeerd omschreven.

Mijn vraag is als volgt: stel ik hanteer als zoekwaarde bij de letter B: 14,87, dan zou ik graag willen weten welke combinatie van producten dit mogelijk maakt. De totale som de van producten (om aan de 14,87 te komen) moet 100% zijn.

Doordat de gebruiker bij de letter B een waarde invult dient de formule op basis van de bovenstaande combinatie automatisch de waardes van G en P te genereren. Deze worden niet door de gebruiker ingevuld.

 

[sylvester-ponte]

jesper, 4 vragen:
1) welke excel gebruik je
2) is vba een optie
3) vul eens wat getallen in in je voorbeeld bestand, graag met uitleg er bij.
4) als je bij B 12 invuld zijn er 2 oplossingen klopt dat?

 

[xps350]

Ik denk dat je er met een formule niet uit gaat komen. Naar mijn idee zijn er altijd meerdere combinaties mogelijk. Alleen al het feit dat de producten B en C hetzelfde percentage B bevatten betekent dat je oneindig kan variëren. Als je even uitgaat van gekozen waardes voor de producten A, D en E, dan levert elke combinatie van B en C waarvan de som gelijk is aan 100 - A - D - E hetzelfde resultaat op.

Bijkomende vraag is of alle producten gebruikt moeten worden en of er minimumpercentage per product is.

 

[jesper2019]

Ik maak gebruik van Office365.

Om het geheel wat duidelijk te maken heb ik het Excel bestand aangepast.

In dit geval is er dus sprake van 3 producten, namelijk: A, B & C. Deze producten bevatten allemaal G, B en P.

In het voorbeeld is mijn zoekwaarde voor G: 5,25. Nu wil ik graag weten welke combinatie van de producten A, B en C er mogelijk is om tot een G waarde van 5,25 te komen.

In het geval van het Excel-bestand is het mogelijk door 75% van product A te nemen en 25% van product B. Product A heeft 3G en product B 6G. Het totaal van de producten tezamen dient 100% te zijn. De soms is in dit geval als volgt:

Product A
3*75% = 2,25

Product B
6*25 = 1,50

2,25 + 1,50 = 3,75

Doordat de samenstelling van de producten duidelijk is kunnen ook de bijbehorende B en P waardes berekend worden. In dit geval gaat het om:
B = 11,25 (11*0,75+12*0,25)
P = 37,5 (38*0,75+ 36*,025)

Hopelijk is het zo wat duidelijker.

 

[xps350]

Correctie: je berekening klopt niet. Je zegt dat G uit moet komen op 5,25 en tovert een berekening tevoorschijn die op 3,75 uitkomt.

Het echte probleem blijft echter dat als je C er bij betrekt er meer combinaties mogelijk zijn. Om nog maar te zwijgen van de situatie in je oorspronkelijke vraag met vijf basisproducten.

 

[sylvester-ponte]

jesper , als je steeds oplossingen met 2 producten wil is het eenvoudig op te lossen. 2 vergelijkingen met 2 onbekenden.
maar als je ook oplosingen met meer dan 2 producten wil zijn er te veel oplossingen. 2 vergelijkingen met meer dan 2 onbekenden.
als jij denkt dat dat niet klopt, geef dan eens een voorbeeld met 3 producten als oplossing. misschien snap ik het dan ook.

 

[sylvester-ponte]

hier misschien een oplossing met een udfje zie grijze cel (hij kijkt slechts naar 2 mogelijke producten tegelijk)

 

[sylvester-ponte]

Jesper, deze udf is maar een principe ,
als je hem wil gebruiken kan ik of iemand hier de spannende punten,
bvb delen door nul of 0% van een product,
er wel uit halen.

 

[jesper2019]

Beste Sylvester,

Hartelijk bedankt voor het Excel-bestand. Dit is precies wat ik nodig heb. Is het ook mogelijk met de UDF of om alle mogelijke combinaties zichtbaar te maken? De bedoeling is namelijk dat er meerdere producten aan toegevoegd worden (in het totaal 9, zie voorbeeld).

Momenteel is het alleen mogelijk om op een G-waarde te zoeken. Zou het ook mogelijk zijn om op twee zoekwaarden te hanteren, zoals bijvoorbeeld G & B?

Een combinatie waarbij er 0% van een product gebruikt wordt mag inderdaad gefilterd worden.

 

[sylvester-ponte]

jesper, je schrijft:

Zou het ook mogelijk zijn om op twee zoekwaarden te hanteren, zoals bijvoorbeeld G & B?

geef eens een voorbeeldje met de juiste antwoorden

 

[sylvester-ponte]

jesper, graag een reactie op post 14
hier de udf met minder spannende punten (delen door 0 is afgevangen en de dubbelen en de 0%jes zijn er uit gehaald)

 

[jesper2019]

Heel erg bedankt voor de aanpassing. Ik heb geprobeerd het geheel wat duidelijker te maken in het Excel overzicht (zie bijlage). Hopelijk is het zodoende duidelijker wat er berekend moet worden.

 

[sylvester-ponte]

jesper, kun rij de formule geven? dan zet ik hem wel in excel.

 

[sylvester-ponte]

jesper, ik snap jouw berekening niet ik heb er naast je berekening proberen te herhalen. maar ik kom op andere waarden.
doe het nog eens duidelijk voor.
als ik de heenweg van jouw berekening niet eens snap dan kan ik de terug weg niet voor je maken.

 

[sylvester-ponte]

hier een eventuele oplossing ( hij is niet geoptimaliseerd en de "0" oplossingen staan er ook nog in)

zelf narekenen of het klopt (als het niet klopt hoor ik het graag)
als je hem wil gaan gebruiken kan ik of een ander hem wel optimaliseren. ontdubbelen e.d.

 

[sylvester-ponte]

vreemd dat goud en beige de zelfde GBP hebben