Hello sir i have a problem in the optimization of my data by genetic algorithm is it possible in sover xl. I have three variables these have data in tabular form i want to optimize these ?
i have attached a my data file here .the formula is .
Leq=A+B*LogQ+C*LogV+D*H
Is it possible in solver xl to find the value of A,B,C,D from these data.
RAHI.xlsx

There are 4 variables.
There are 10 instances of values.
If the instances of values were consistent with the formula Leq=A+B*LogQ+C*LogV+D*H then any 4 of them should yield the same values for A,B,C,D.
But, unfortunately, they aren't consistent. Any 4 instances yield a differerent values for A,B,C,D  suggesting that this is measured data and not derived values for Leq.
The Solver can't fix that.....
How was this done?
Create a list for A,B,C,D in B14:B18 and set the values to "1".
Extend the table to include Approximation, Error and Error^2 on columns E,F,G.
In the Approximation column E, put: B$14+B$15*LOG(A2,10)+B$16*LOG(B2,10)+B$17*D2 (this for row 2)
Copy the cell in the Approximation column from row 2 to row 11
In the error column F, put $E2$C2
In the error^2 column G, put F2^2
Sum the error^2 column and take the square root ..in cell G12.
Run solver to minimize this sum with the values in for A,B,C,D in B14:B17 the variables.
Now, extend the spreadsheet so that only 4 of the instances are used and do the same minimization.
You will find that rows 7:10 generate the lowest errors  and errors that may be quite acceptable.
If so, you might be motivated to throw out all of the other instances because at least some of them appear to correlate well.
0
Please sign in to leave a comment.
Comments
1 comment