Power law correlation between with three parameters

Question

I made a CFD model of the Centrifugal Atomization Process. Studies show that the mean particle size $d_{32}$ can be expressed as a function of the three operating dimensionless numbers (Reynolds, Ekman and Ohnesorge).

Previous works made up correlations in power law as

$$d_{32}^* = \frac{d_{32}}{D} = \mathrm{const.} \times \mathrm{Ek}^{\alpha_1} \times \mathrm{Re}^{\alpha_2} \times \mathrm{Oh}^{\alpha_3} $$

from parametric studies where $d_{32}^*$ is calculated with two dimensionless numbers fixed and varying one.

I have the data from these parametric studies and I would like to create one of this correlation but I have no idea how to do it. The following tab is an example of (fake) data where the first row in the initial state ; row 2 and 3 are data for the Reynolds number parametric study ; row 4 and 5 are data for the Ekman number parametric study ; and row 6 and 7 are data for the Ohnesorge number parametric study.

Edit: I wrote the real data.

I guess the answer is easy to find on internet but I guess I don't have the right vocabulary I don't find anything.

Thank you for your help. Best.

Answer 1

Solver is an absolute BEAST when it comes to solving least squares problems. As an example, if you want to do a least squares curve fit on a linear equation, you would take your desired equation of $y=mx+b$, and modify it such that it reads $y-mx-b=0$. This modified equation can be slightly changed such that it becomes a least squares problem by squaring it thusly: $(y-mx-b)^2 = 0$. The left hand side of this equation represents the error squared term of the equation if starting guesses for m and b are incorrect. This equation ONLY converges to a minimum value when the best values of m and b are selected. This means that for this particular problem, the objective function for the linear curve fit is that last equation, and Solver should minimize this equation by manipulating the values of m and b.

As practice, I have given the following two problems to high school students in order to teach them the technique. After those two problems, I posted how I set up your problem. The two practice problems are:

Sample Solver Problem 1 (Inseparable variables)

1. Open a new Excel spreadsheet.
2. At spreadsheet cell A1, enter “x”.
3. Go down one cell to A2, and enter an arbitrary starting value (e.g., 2).
4. Assume that you are solving the equation: $x^3 + x^{1/2} =100$ (1)
5. Rewrite equation (1) to make this a least squares problem, which Solver is an absolute expert at solving: $(x^3 + x^{1/2} - 100)^2 = 0$ (2)
6. Enter “err^2” as a title in cell A5.
7. Enter equation (2) in cell A6, using the Excel format “=(a2^3+a2^0.5-100)^2”. When finished with this equation hit the “enter” key to enter it. Note that “a2” refers to cell a2, which is where your starting guess for the correct value of “x” is located.
8. Select the Data tab on the menu bar, and start Solver.
9. For Solver Parameters (in the pop-up screen), choose “Set Objective”, clear out what is initially in the box, and click on cell A6 in the spreadsheet, which is the cell that you are trying to minimize.
10. Since this is a minimization problem, select “Min” in the Solver Parameters “Equal To” area
11. Click in the “By Changing Cells” box of the Solver Parameters pop-up
12. Click on cell A2 of the spreadsheet to select the value that you are manipulating in order to minimize equation (2).
13. Select “Solve” in the Solver Parameters pop-up screen.
14. Your answer should be 4.608135.

Sample Solver Problem 3 (Curve Fitting)

1. Open an Excel spreadsheet
2. Assume that you are trying to perform a least squares regression on a linear equation. Further assume that this problem has 5 data points. Thus, y = mx + b for this example, but note that the actual equation that you can fit with this method is limited only by your imagination.
3. At cell A1, enter label “m”, and at cell B1, enter label “b”.
4. At cell A2, enter a starting guess for m (e.g., 2).
5. At cell B2, enter a starting guess for b (e.g., 2).
6. At cell A5, enter label “x”, to designate the column that will be used for independent variable values.
7. At cell B5, enter label “y”, to designate the column that will be used for dependent variable values.
8. Starting at cell A6, enter 5 x values into the spreadsheet, going down in column A.
9. Starting at cell B6, enter 5 y values into the spreadsheet, going down in column B.
10. At cell C5, enter label “y-pred”, to designate the column that will be used for the predicted value of y values
11. At cell C6, enter the equation that predicts the y value for the given x value. This equation is “=$A$2*A6+$b$2”.
12. Copy cell C6 into cells C7 through C10.
13. At cell D5, enter label “err^2”.
14. At cell D6, enter the equation for the error squared term, based on the chosen values of m and b. This equation is “=(B6-C6)^2”.
15. Copy cell D6 into cells D7 through D10.
16. Go to cell D11, and sum all of the error squared terms in column D by entering the equation “=SUM(D6:D10)”.
17. Select the Data tab from the top menu bar and start Solver.
18. In the Solver Parameters pop-up screen, select “Set Objective” and clear the contents of the box.
19. Go to the spreadsheet and click on cell D11, which is the sum of all of the error squared terms.
20. In the Solver Parameters pop-up screen, select “Min” on the “Equal To:” line.
21. In the Solver Parameters pop-up screen, go to the box “By Changing Cells:” and click in the box.
22. Go to the spreadsheet, and highlight cells A2 and B2 by selecting cell A2, holding down the left mouse button, and dragging the mouse to cell B2.
23. Once cells A2 and B2 have been selected, choose “Solve” in the Solver Parameters pop-up screen.
24. When Solver converges, your answers for m and b will be in cells A2 and B2.
25. Verify “m” and “b” by using Excel’s plotting and trend line functions

Your problem:

1) Set up your problem as a curve fitting problem

2) Your objective function should be $(d_{32} - const * E_k^{alpha1} * R_e^{alpha2} * O_h^{alpha3})^2$

3) Your 4 manipulated variables should be const, alpha1, alpha2, and alpha3. Choose a value other than 0 or 1 as starting guesses. Note that I chose a value of 2 for all of my starting guesses for these four unknown values.

4) Tell solver to minimize the sum of the error squared terms that the objective function is calculating, by manipulating the stated manipulated variables. When you run Solver, it should give you the values that I previously posted.