Minimum mean square error with Solver of Excel

　　 　　　　　



Let's make the approximation function of measured data


It is assumed that data (X,Y) including the error margin like the figure below was measured.
The minimum means quare error is often used to make the approximation function, and to take out data other than the measurement point.

Outline of minimum mean square error



Generally It approximates by the polynomial function. Now, it will approximate by the third function.

The third function  f(X) = aX3 + bX2 + cX + d

The minimum mean square error makes a smooth function that passes the vicinity of data. Therefore, the method of approximating data including the error margin.
It is a good method when making it.



Explanation of minimum mean square error


With Y when the measurement point is X  Function value  Difference of f(X)  (Y-f(X)) Is calculated.
If the square of difference  (Y-f(x) )2 is small, Y and f(X) reach a close value.

It is useless only only the measurement point in one place reaches a close value. It is necessary to bring the function close to all the measurement points.

Therefore, the sruare of the difference is totaled in all the points.

  T(total)  =  Σ  ( Y - f(X) ) 2       Σ(sigma):  Sign of totaling it in all points

Coefficient (a,b,c,d) of the function to which this T(total) is minimized is requested.

When the coefficient is obtained, We get　f(x) in arbitrary X.
  
　f(X) = aX3 + bX2 + cX + d


Square total of error is requested with EXCEL


Then, let's request square total of error with the approximation function.

The area of coefficient (a,b,c,d) of the function is secured, and the square error is calculated.       
Please calculate T(total) at last.

Even if coefficient (a,b,c,d) is changed by the trial and error, it is difficult to bring the function close to data.








Using Solver of EXCEL


Solver of EXCEL is specifies the cell that changes it, and a function minimum (maximum) as for a target cell.

Please do an additional installation when you do not install Solver.

Here, the cell that changes it is assumed to be coefficient (a,b,c,d) of the function, and a target cell becomes T(total).

Please select tool Solver of the menu bar.



Please select a target cell and the changed cell area, and select minimum value about the target value.
When the execution button is clicked, the calculation result is reflected on EXCEL.



Result of execution of Solver of EXCEL


An approximation curve near measured data was obtained.

When You find coefficient (a,b,c,d) of the function that reaches a value that is smaller than T(total value).
Please put out the complaint to Microsoft Corporation.








Science and technology calculation with EXCEL



〒673-0036  Japan Hyogo Prefecture Akeshi City Matsue 62-14
God foot co Ltd




Please let me hear the impression