Design software using excel equations

[on123]

New user here in need of help. I am creating a mixture design using 3rd party software and needhelp setting up the constraints. I’m using Excel to do the math and willput it into DOE software. What the DOE softwaredoes is it looks at the high and low ranges that you enter, and it designsseveral experiments for you to make.

So here are the ranges for myvariables in gallons, but I suppose the unit of measurement doesn’tmatter.
Now each of thesematerials has a certain percent solids by volume, which means if something is100 gallons and it’s 50% solids by volume, then there is really only 50 “solidgallons” worth of material. Picture a 100gallon vessel where 50 gallons of it is water and 50 gallons of it ispowder. When you mix them together, thematerial is only “50% solids”
To put this intoperspective lets focus on the “LOW” limit in the following example.
"G" is aBINDER and is 33.74 GAL. Of that 33.74GAL, only 17.52 of it is SOLID GAL. Why? Because it is 51.92% solids by volume. The math is 33.74 * (51.92/100) = 17.52


[TABLE="width: 487"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="105" style="width: 79pt; mso-width-source: userset; mso-width-alt: 3840;"> <col width="64" style="width: 48pt;" span="2"> <col width="21" style="width: 16pt; mso-width-source: userset; mso-width-alt: 768;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <col width="24" style="width: 18pt; mso-width-source: userset; mso-width-alt: 877;"> <col width="64" style="width: 48pt;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3657;"> <tbody>[TR]
[TD="width: 107, bgcolor: transparent"] [/TD]
[TD="width: 105, bgcolor: transparent"] [/TD]
[TD="width: 128, bgcolor: transparent, colspan: 2"]GALLONS[/TD]
[TD="width: 21, bgcolor: transparent"] [/TD]
[TD="width: 100, bgcolor: transparent"]% SOLIDS[/TD]
[TD="width: 24, bgcolor: transparent"] [/TD]
[TD="width: 164, bgcolor: transparent, colspan: 2"]SOLID GALLONS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]LOW[/TD]
[TD="bgcolor: transparent"]HIGH[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]LOW[/TD]
[TD="bgcolor: transparent"]HIGH[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]4.5[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4.5[/TD]
[TD="bgcolor: transparent"]6.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]2.28[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]2.28[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4.43[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]G[/TD]
[TD="bgcolor: transparent"]BINDER[/TD]
[TD="bgcolor: transparent"]33.74[/TD]
[TD="bgcolor: transparent"]44.94[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]51.92[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]17.52[/TD]
[TD="bgcolor: transparent"]23.33[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H[/TD]
[TD="bgcolor: transparent"]PIGMENT[/TD]
[TD="bgcolor: transparent"]2.92[/TD]
[TD="bgcolor: transparent"]8.76[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]28.06[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0.82[/TD]
[TD="bgcolor: transparent"]2.46[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I[/TD]
[TD="bgcolor: transparent"]WATER[/TD]
[TD="bgcolor: transparent"]24.01[/TD]
[TD="bgcolor: transparent"]27.61[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TOTAL[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]65.17[/TD]
[TD="bgcolor: transparent"]107.31[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]22.84[/TD]
[TD="bgcolor: transparent"]51.79[/TD]
[/TR]
</tbody>[/TABLE]


So to compute the TOTAL SOLIDS BYVOLUME for the LOW, you take the SOLID GALLONS and DIVIDE by GALLONS. So For the LOW it’s (22.84/65.17) * 100

And for the HIGH it’s (51.79/107.31)*100

To further this idea, if we wantedto determine the absolute lowest solid volume limit based on all these LOW andHIGH values, we can do so by this equation:

[ 22.84 / (4.5 + 0 + 0 + 0 + 0 + 0 +33.74 +2.92 +27.61) ] * 100

This equates to 33.21%

If we wanted to determine theabsolute highest solid volume limit based on all these LOW and HIGH values, wecan do so by this equation:

[ 51.79 / (6 + 2.28 + 4.43 + 4.43 + 4.43+ 4.43 + 44.94 +8.76 +24.01) ] * 100

This equates to 49.94%

Now what I’m struggling with isentering in the constraints. While the rangefor the volume solids is 33.21 – 49.94%, I don’t want to have experimentsgenerated that will be that high in solids. I want the volume solids range to be 34% - 40%.

You can onlyenter in constraints in the form of:

Low limit ≤ CONSTRAINT≥ High Limit

You can onlyadd a number in front of the variables. For example:

0.25 ≤ 0.05A+ 0.94B + 32C ≥ 45

So I need toplace some constraints so that no experiments are made that fall out side theranges of the volume solids. How would Igo about doing this?

Now the 2^ndpart of this problem is that I also need to set up the constraints such that the“pigment volume concentration” or “PVC” falls in range also and no experiment isgenerated that falls outside my desired range regardless of what the theoreticalMAX/MIN values are. The formula forpigment volume concentration is (SOLID GAL PIGMENT / SOLID GAL PIGMENT + SOLIDGAL BINDER) *100

As seen inthe table above:

VARIABLES A,B, C, D, E, F, H are all PIGMENTS
VARIABLE G is BINDER
VARIABLE “I” is WATER

I need toexpress the following formula such that the software doesn’t design any experimentsthat are lower than a pigment volume concentration of 25 or higher than 45.

Basically, Ineed a formula that I can put in the constraint section of the software suchthat this formula (A+B+C+D+E+F+H) / [(A+B+C+D+E+F+H) + G] falls between 0.25and 0.45

I have noidea how to express this such that the pigment volume concentration of the experimentsfalls between 0.25 and 0.45 but I know it can be done. If I were to not have any constraints, then allthe experiments would fall within the calculated low and high limits seen herebased on the original design inputs.

[TABLE="width: 153"]
<colgroup><col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 140, bgcolor: transparent"]PVC LOW[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]18.57[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PVC HIGH[/TD]
[TD="bgcolor: transparent, align: right"]45.75[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]VOL SOLID LOW[/TD]
[TD="bgcolor: transparent, align: right"]33.21[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]VOL SOLID HIGH[/TD]
[TD="bgcolor: transparent, align: right"]49.94[/TD]
[/TR]
</tbody>[/TABLE]


Since the softwareis flexible enough to allow you to put in constraints to further optimize youroutputs, I want to take advantage of it. Any ideas?

 

[on123]

I figured out how to do it. Simply cross multiply to remove what is under the numerator, then flip the sign when going across an equal/less than symbol.