I am creating an excel sheet of the problem I have. A tank is to hold a required volume of 32000L of chemicals and I must minimise the surface area and obtain the radius and height of the tank. I have done the problem numerically, however I am stuck with an IF statement on excel.
I am using the golden section search optimisation method.
How should I implement an IF statement where, f(x2)>f(x1) but put in f(x1)>f(x2) at the same time. So it's either one or the others. I can obtain one IF statement using =IF(G2<C2,D2,B3) but I dont know how to implement an (otherwise use this value) instead.
I hope I explained that correct, any help will go a long way and thanks in advance.
Using the notation of http://en.wikipedia.org/wiki/Golden_section_search, your search procedure should be based on something like an iterative step which
Although it appears as though there are two cases you have to consider [according to whether f(x2) is greater or less than f(x4)] there are in fact 4 cases to consider.
The additional cases arise because there are two ways that the values x1, x2 and x3 can be arranged and still meet the conditions set out in 1., above. The diagram in the Wikipedia article shows that x2 is closer to x1 than x3. I'll call this case RIGHT since the longer of the two line segments between x1 and x3 occurs to the right. There is a second case, where x2 is closer to x3 so that the longer segment is to the left, which I'll call LEFT. The calculation of x4 differs in these two cases - for the RIGHT case x4 is greater than x2, whereas in the LEFT case x4 should be less than x2. There are 4 cases or mappings for determining the next set of x1, x2, x3 from the current x1, x2, x3, x4 values dependent on whether the current x1, x2 and x3 values fall in a RIGHT or LEFT arrangement and whether f(x2) is greater or less than f(x4) - 2 possibilities (RIGHT or LEFT) multiplied by 2 possibilities (f(x2) greater or less thah f(x4)) gives 4 possible cases in total. In addition, 2 of these cases result in the new x1, x2 and x3 values falling in a RIGHT arrangement and 2 result in a LEFT arrangement.
To manage the iterative steps properly you need to recognise these 4 cases correctly. Using an Excel-like notation, the formula for calculating x4 (part 2. of each iterative step) is
The formula for calculating the new x1 value (part of part 3. of each iterative step) is
The first formula uses an IF() to deliver either x1 or x3 according to whether the current arrangement of x1, x2 and x3 conforms to the RIGHT or LEFT case respectively and then uses this delivered value as part of the expression for calculating x4. The second formula uses nested IF()'s and you should be able to see how the formula represents the 4 cases discussed above. I'll leave it to you as an exercise to work out the formulae for the new values of x2 and x3 but they both use the the same nested IF() structure as the second formula above. You can also use this same structure as a formula to deliver the new case arrangement of the new x1, x2 and x3 values. Drawing out each case using pencil and paper is a good starting point for working out the formulae you'll require.
If you can figure out all the formulae correctly then you could construct a list with 7 columns
case, x1, x2, x3, x4, f(x2), f(x4)in which successive rows represent successive iterative steps. The fifth value in each row is calculated using the first formula above from the first 4 values in the same row and the sixth and seventh values are respectively calculated from the third and fifth values from the same row. The second value is calculated using the second formula above using the values from the preceding row. Similarly, the first, third and fourth values are calculated using formulae I've left you to work out for yourself but again each formula will refer to values from the preceding row. You could add an 8th column to tell you when the iterative process has sufficiently converged - perhaps based on the absolute difference between the seventh and eighth columns.
As a final comment, unless you have a specific reason for using an iterative approach for finding your function minimum, it would be much easier to solve your problem exactly using calculus - perhaps that is what you meant by your statement that you had "done the problem numerically".