Is solver the right way?

[chrissharma]

Hi everyone,

I'm new to this forum and also pretty new to excel. That is, when it comes to more complicated stuff.

The thing is working with a certain product that has several properties. My goal is to improve one of those properties by lowering the value. In the sheet this is know as thermal conductivity.
The other property is bending strength. Both bending strength and thermal conductivity are related to porosity. The porosity can't be lower too high so the bending strength will be lower than 2.5

My question is if anybody has any idea about how to work with this?
I've formulated the constraints in the sheet in the attachment.
Someone told me to use solver, but thus far haven't been able to figure it out.
I had one idea, which was joining a forum regarding excel, which I successfully executed

Any thoughts are welcome.

Thanks in advanced

[Richard Buttrey]

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.

[chrissharma]

Ehm.. Yea the thermal conductivity can be as low as possible. With the restriction of bending strength not going lower than 2.5. Porosity depends on that.

[Richard Buttrey]

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

=MIN((IF(F5:F13>E16,G5:G13,"")))

and for the associated porosity in E19

=INDEX(E5:E13,MATCH(E18,G5:G13,FALSE),1)

See attached

[MrShorty]

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.

[Alf]

Using solver for blending you need to know how things "react" when blending them i.e the products must blend linearly or you need to use indices.

For example motor spirit blending can be done by solver but octane number and vapour must be converted to indices before they can be used in blending. Distillation points T10 (temperature when 10 % is distilled off), T50, T90 can not be calculated properly due to the physical properties of the components.

You must also know if the properties blend linearly by weight or volume and adjust you model accordingly.

I’ve set up a small solver model for you. Perhaps this could be of help to you.

Alf