Is solver the right way?

Hi,

Are you able to offer some typical results for this example and perhaps one or two others. I'm not clear how your variables are related on to another and which variables you are trying to minimise or maximise.

All the Porosity and Bending strength numbers are greater than your required minimums so trivially if you want thermal conductivity to be maximised then that would be the C1 value in column B or if you want the conductivity minimised it would be A6.

Hi,

It doesn't seem a particularly good example since there is no bending strength lower than 2.5 so if I understand you correctly it's a trivial answer to look for the lowest conductivity and get the answer .0692.

If I understand the request correctly then the following array formula, i.e. you MUST enter it with Ctrl-Shift-Enter not just the Enter key will give you the lowest conductivity that meets the requirement that the bending strength be > 2.5

In E18

Formula:

Please Login or Register  to view this content.

and for the associated porosity in E19

Formula:

Please Login or Register  to view this content.

See attached

After thinking about your question, my first impression is that this is (at least at first) more of a material sciences question than an Excel question. I don't know of anyone on this forum who would be conversant with material science type questions, though I could easily be wrong.

I put something like "relationship porosity thermal conductivity bending strength" into my favorite search engine and found several journal articles, theses, and patent applications that described similar studies on various materials (concretes, wood, ceramics, etc.). My first suggestion would be to browse some of these sources, and any other literature or textbook sources you have access to, until you are more conversant with the science and engineering behind this problem.

Once you have that background, then I would expect you to come back to Excel where there will likely be some kind of data correlation/regression step to empirically determine the relationship(s) between these three variables and any other variables that seem important. The details of this analysis will depend greatly on what you learn about the math and engineering equations behind these problems. I will mention in passing that Excel has two main utilities for performing regressions: The LINEST() function can perform "linear" regressions. Solver can be used to optimize both linear and non-linear regression algorithms. If you need help with these when you get to this step, let us know and we can help with the syntax of these tools.

Once you have a believable mathematical model describing your material, then you can extrapolate that model to estimate properties at the desired thermal conductivity. Of course, there are usually concerns with extrapolating from know values into unknown regions (one usually prefers to interpolate). Your uncertainty in these extrapolations will depend greatly on the accuracy/uncertainty/noise in your raw data and the suitability of your mathematical equation to the problem.