How to Minimize Squared Sum of Error Using Solver on Excel

Step-by-Step Guide

1. 1

   Step 1: Count how many equations you have.

   For this tutorial to be effective, one must first calculate the degrees of freedom to determine whether or not this is a feasible solution to solve for these unknowns.
2. 2

   Step 2: Count how many unknown variables you have.

   , If this is equal to zero, the following instruction are applicable.
   
   If this does not equal zero, then a revision of the problem statement and/or determination of additional specifications must be found., Using the Sample Problem, the degrees of freedom are determined to be zero:
   Number of Equations:
   Three (3) equations total.
   
   Equation 1: 4𝑥+2𝑦+𝑧=5 Equation 2: 7𝑦+5𝑧=17 Equation 3: 15𝑥+25𝑦+8𝑧=11 Number of Unknowns:
   Three (3) unknowns total.
   
   Unknown 1: x Unknown 2: y Unknown 3: z Unknowns total. 𝐷𝑒𝑔𝑟𝑒𝑒𝑠 𝑜𝑓 𝐹𝑟𝑒𝑒𝑑𝑜𝑚=𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝐸𝑞𝑢𝑎𝑡𝑖𝑜𝑛𝑠−𝑁𝑢𝑚𝑏𝑒𝑟 𝑜𝑓 𝑈𝑛𝑘𝑜𝑤𝑛𝑠 𝐷𝑒𝑔𝑟𝑒𝑒𝑠 𝑜𝑓 𝐹𝑟𝑒𝑒𝑑𝑜𝑚=3−3=0 , SSE is defined as: Σ(𝑌𝑖−𝑓(𝑥𝑖))2𝑛𝑖=1 Where Y = the known value of the equation f(x) = the value of the equation calculated as a function of the variables. ,, Can be found towards the top of the page., If the Excel programming being used has the Solver Add-in, skip this part. , A separate window labelled Excel Options should appear,, Click on Analysis ToolPak and Solver Add-in.,,, Labels, borders, and placement are personal preferences ,,,,, The set objective should be the cell dedicated to the sum of squared error.
   
   Click the “Min” bubble.
   
   For the “By changing cells section” highlight the three cells containing the unknown variable values.
   
   Make sure the “make unconstrained variables non-negative” box is un-checked.,,, If solution was not found, try entering different sample values and make sure your equations were formulated correctly.
3. 3

   Step 3: Subtract number of equations to number of unknowns.

4. 4

   Step 4: Consider the sample as an example.

5. 5

   Step 5: To determine the unknown variables

6. 6

   Step 6: solver will minimize the squared sum of error (SSE).

7. 7

   Step 7: Open Microsoft Excel

8. 8

   Step 8: Locate and click on the Data Tab.

9. 9

   Step 9: Locate to see if the Excel program you are using has the Analysis Section under the Data tab.

10. 10

    Step 10: Go to File and click Options.

11. 11

    Step 11: Click the Add-Ins Section and Locate the Solver Add-In.

12. 12

    Step 12: At the bottom

13. 13

    Step 13: click Go

14. 14

    Step 14: A Small window labelled Add-Ins should pop up.

15. 15

    Step 15: Click Ok.

16. 16

    Step 16: Check back the Data tab to see if the Analysis section and Solver function has been added in.

17. 17

    Step 17: Set up three cells dedicated to the unknowns

18. 18

    Step 18: and three cells dedicated to the equations.

19. 19

    Step 19: Set up three cells dedicated to the known values of the equation

20. 20

    Step 20: Set up three cells dedicated to the calculation of the squared error.

21. 21

    Step 21: Set up one cell dedicated to the sum of the squared error.

22. 22

    Step 22: Put in sample values in the cells dedicated to the unknowns

23. 23

    Step 23: Click Data

24. 24

    Step 24: then Solver.

25. 25

    Step 25: Press Solve

26. 26

    Step 26: to see if solution was found.

27. 27

    Step 27: Press Ok.

28. 28

    Step 28: Look at your results: The three cells dedicated to the unknowns should now display the correct numerical values for the unknown variables.

Detailed Guide

About the Author