Multiple Goal seek

[BramBlue]

Hello y'all,

I have a particularly nasty assignment. In where I need to find out the weight in grams of 3 mixes (Mix A - Mix B - Mix C) Who together make up a sample. Mix a and Mix B are both made of specific amounts of different materials.

I want to calculate the weight of these materials.

I know the weight and weight percentage of Mix C, And to combined weight and weight percentage of Mix A and Mix B. I also know the volume percentage and the density (g/ml) of each material.

The things unknown to me are the Weight of Mix a and Mix B, and the volume of the sample, the mixes or any of the materials. 

I need the Gray-filled cel to be the same value as the red-filled cel, by changing the value of the Red-text cells, while the blue-text cells remain the same value as the green-text cells next to it.

The whole idea is that even after changing a density (g/ml) or a volume percentage (% ml), all the red-text cells change with it to make sure the value in the gray-filled cel stays the same as the red-filled cel.

There is a exel file with this post if needed. I am pretty certain that this is possible with multiple What if / Goal seek (macro or not) functions. If anybody knows how to make such a thing, or a other way this can be solved. I would really appreciate that.

With kind regards,

Bram Bennink

[#2]

@BramBlue 

Bram, 

I looked at this earlier today but couldn't figure out what exactly you are trying to achieve. Many have viewed your question but nobody has replied, so far.

What is the meaning of the columns headed "% to be"? Could you indicate in your schedule what values you would like to see where? For instance, if you change the density of the first element in Mix A, what should be the outcome of the other calculations. Then, perhaps I or someone else can figure it out for you.

[#3]

@Riny_van_Eekelen 

Hello Riny,

Sorry I made thinks unclear. I will try to clear things up:

- The Blue-text cells (% ml) need to be the same value as the Green-text cells (% to be).

- If the density (g/ml) is changed, the Red-filled cells (g) will change to a value so the Blue-text cells will once again be the same as the Green-text cells.

- If the Green-text cells is changed, the Red-filled cells (g) will change to a value so the Blue-text cells will once again be the same as the Green-text cells.

I hope this will help

[#4]

@BramBlue 

You have a mix A and a mix B that together should weigh 28.323gr (your cell D16), since the total weight and the weight of mix C are fixed. Correct? Each mix (A and B) consists of multiple elements of different densities and weights. And now you want to work out the composition of each of mix A and B, based on target %/ml, at varying densities and weights. This goes beyond my mathematical imagination. Sorry!

[#5]

@Riny_van_Eekelen 

That is not a problem. Thank you for taking the time for me.

[#6 · Accepted solution]

@BramBlue See attached, Macro buttons are alongside each entry you need to click the button after each change in (g) Red text . Also remember each time you make a change in next row your previous row entries will change as well because you are using sum of all in formula and change in any entry will change the sum value which will then change calculated goal seek. so you have to run macro a few times to get to right numbers. also remember that for some value macro will calculate very nearest value not exactly same as blue text.

hope it will help.

[#7]

@Kashibaba 

While this isn't exactly what I was thinking of, I've combined all of them in one sub and by dividing the total of mix a+b by the wanted value of mix a+b, and the value that comes from it times every red-text cell. This will get a roughly correct values