Design of Experiment for Excel 2003

**gordonjh** · 04-01-2011, 09:06 AM

I need to create a DOE for a project I am working on at work. Due to program security and accessability, I cannot download any programs, so I need to be able to do this all on Excel.

Essentially, I have a target number to reach. There are three variables which affect this number. Based on the amounts of each of these variables, my target number changes. Therefore, I will need to experiment by changing one variable at a time while leaving the other two variables constant. Once I have established several data points which result in various numbers, I need to be able to create an equation from this data which allows me to plug in an amount for one of the variables and returns the amounts of the other two variables necessary to reach my target number. Thus, regardless of what amount I choose for variable 1, my equation will produce the other amounts of variables 2 and 3 such that my target number is achieved.

Any ideas of how I might begin this process? I know Excel has these capabilities, but I am stumped. Help is greatly appreciated!!

**col12345** · 04-01-2011, 10:43 AM

Assuming the problem is similar to having a box of volume 1000cc and you have 3 sets of smaller boxes
volumes 10cc, 20cc and 30cc the problem is how to fully fill the large box with a combination of A, B and C
Ax+By+Cz=1000

or 10x+20y+30z=1000

In this scenario max x is 100 max y is 50 and max z is 33
Therefore

for x = 0 to 100
for y = 0 to 50
for z= 0 to 33
if (x*10)+(y*20)+(z*30)=1000 then (save the values x y z in cols 1 2 3 : increment row) Doh!
next z
next y
next x

Can't quite remember the syntax for addressing worksheet cells, so rather not guess, sorry.
But what you will have is a worksheet populated with all the combinations of xyz that hit your target.

I hope this is of use and good luck

cheers

col

**gordonjh** · 04-01-2011, 11:41 AM

Perhaps I should've just explained it exactly as it is.

So for this particular experiment, I am actually mixing three components, each with a particular viscosity, in order to obtain a final viscosity. Let's call these three materials A, B, and C. I know the viscosities of B and C; however, the viscosity of A, mass of A, mass of B, and mass of C are all unknowns. I also know that the sum of the masses of A, B, and C must be 50g. So we can remove mass of C as a variable since it can be calculated later on. Thus, by changing visc. of A, mass of A, and mass of B, I need to reach my target viscosity. The viscosities have a range rather than set points.

In some fashion, I will need to run a number of trials in the lab with different ratios that will allow me to calculate which ratios give me the specific necessary final viscosity. Once I do so, I need to be able to use Excel to find a correlation or equation of the combinations of these three variables which will always give me the desired final viscosity. With this, one should be able to enter the viscosity of material A, and the masses to be used of A, B, and C will then be given to them. This is the problem as specifically as I can give it. Let me know if you have suggestions with this new information.

**col12345** · 04-02-2011, 01:23 AM

Hi again

I think I have got my head around part of your problem.

Although I have made an uneducated assumption that the viscosity of a material is the average of its parts.

eg 10g of A at viscosity 10 plus 40g of B at viscosity 5 = 50g of viscosity 6

((10 x10)+(40*5))/50 = 6

The sheet attached will calculate the mass of substance A as B increases while C is fixed
while maintaining the goal of a fixed viscosity for the resultant mixture.
Mass of C as well as viscosities for B,C and resultant mix are user definable.

i hope this is a start in the right direction

cheers

col