Connecting Microsoft Excel as a solver to optiSLang

1. Connecting Microsoft Excel as a solver to optiSLang Tutorial:Using MS Excel as a solver to fit true stress / true strain curves of metallic materials

2. Background • Material Parameters can be determined in practical tensile tests. • The result of these tests is a true stress / true strain curve of the material. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

3. Background • To perform FE-Analyses it is often necessary to define material properties that are fittet to a tensile test measurement. • As a result of the measurement, you get the data points of a σ-ε-plot. • You can fit a generated curve according to the Gosh-Equation: σY(εp) = k • (ε0 + εp)n – p • At first, you have to determine the start values of the parameters k,n,p and ε0. • These parameters can be taken out of the given reference curve. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

4. Take a look at a given reference curve • You get a reference curve usually as an Excel sheet. • To determine the start valuesof the gosh parameters, you can take out: k = the Value of εP = 1 • calculate n = ln(σY/k) / ln(εP) • Notice: to get a good result, take an average of n between εP = 0 and εP = 0.2! • and finally ε0 = (σY/k)1/n • Set the parameter p = 0 Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

5. Calculating the start value of n • Sum up the desired values for „n“ • Divide it by the number of considered values. • This leads to your first approximation of „n“ Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

6. Generating a first approximation curve • Generate the input section with the four parameters • Create the new true stress true strain curve with the Gosh-Equation and set the reference of the values for k,n,p, ε0 to the input section! • To check the accurance of the fitting, create a new column: the quadratic error: (σapprox. – σY)2 • Finally sum up the quadratic errors and calculate an average value Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

7. Preparing the worksheet for optiSLang • Input Variables are read in in rows • Output Variables are read out in columns • The used macro needs some references. So you have to mark thefirst input variable als „inputParams“, the first output variable as „outputParams“ and the second output variable as „outputParams2“ Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

8. Working with the macro • To work with the extraction macro, you have to allow using macros in your Excel settings! • Start the MS Visual Basic-Editor to have a look at the macro Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

9. The macro • In the macro you find: • The input file names • The output file names • The Names of the references,that you have to set in Excel(see sheet 7) • Some settings • The extraction routine(as shown beside) • Note:The macro is pre-definedfor one input file and twooutput files for each onecolumn. If you want toextract more, please adaptthe macro to your needs. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

10. Creating the input deck • Close and save your completed Excel Worksheet! • Define an input.txt where you define the input variables: • Open the given jscript file and modify the name of your worksheet! n ε0 k p Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

11. Start the reference run • You start the calculation and extraction of the both outputs inthe following way: • Write a „start.bat“ file with the command line: cscript G:\Projekte\Excel_Solver\test\kurve_optislang.js //B  modify this according to your system  • Make sure that Java-Script is not deactivated on your computer! • You‘ll get now the following structure in your directory: Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

12. Create the optiSLang project • Create a new optiSLang project in your working directory Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

13. Parametrize the input variables • Start a new parametrization of the 4 input variables n, ε0 , k, p • Fill in the lower and upper bounds and the formats as shown below Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

14. Parametrize the outputs • First open the output2.txt to parametrize the average quadraticerror (last value at the end of the file) as an output variable. • You need at least one output variable to finish a parametrization. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

15. Preparing the signal parameters • The fitting of the curves will be done with signals in optiSLang. • Therefore, you have to create the reference curve in two text files by copying the values from the given excel sheet.The textfile „epsilon.txt“ is also needed to get the values for εY! Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

16. Creating signal parameters • At first, you have to define the block markers. These markers represent the array of values that you allocate to a vector. • Create the markers as shown below • For the output2.txt, you have to define an end in the marker, because after the single values, the sum and average of the quadratic error follows and this should not be extracted here! Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

17. Creating signal parameters • Repeat this for the additional textfiles sigma.txt and epsilon.txt • Notice: Here you have to set the start line as „2“ because the values begin in row 2. • Now you have to define the parameter vectors Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

18. Creating parameter vectors • Mark the first value of the output files and add them as vectors • Set the reference to the output-file-marker and insert a reasonable name (examples are shown below). Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

19. Defining the reference vectors • Repeat this for all the desired output vectors. • The Sigma.txt contains the reference value of σY. • The vector in Epsilon.txt has to be defined twice – one of themis used as a reference vector. • To make a vector a reference vector, you have to set them as constant: • Double-click on the vector element and mark it as constant Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

20. Defining signal objects • After you have now defined all the needed vectors, you can now create the signal objects to start the analysis. • Do this for the Sigma_Epsilon, the quadratic error and the reference curve. Mark the reference curve as constant so that it is treated as the reference by optiSLang. • Save the parametrization and close it. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

21. Design of Experiments • To get an overview of the variation space, it can be useful to perform a sensitivity study at first. • Here you can see the output variation and wheather your target value is located inside of the variation space. • For the DoE, first add two lines to your start.bat file: copy "G:\Projekte\Referenzkurve\optislang\sigma.txt" .copy "G:\Projekte\Referenzkurve\optislang\epsilon.txt" . Modify the path when it differs from your settings! • Use the predefined problem specification • Use the start.bat as starting script • Calculate about 100 designs – it doesn‘t take long (~ 1.6 s /design) Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

22. DoE Postprocessing • You see that the reference curve is within the variation space. So it shall be possible to fit the four Gosh-parameters for an optimal fitting of the calculated curve. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

23. Defining the objective in optiSLang • To start a curve fitting, you have to define an objective in optiSLang. • Just re-open the parametrization to add a signal function at first • Choose then as function the SIG_DIF_EUCLID_SLOT functionThis functions determines the euclidian norm between two curves in a defined slot. The slot is chosen because for large strain values (ε > 0.4), the curve fitting has not to be verry accurate. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

24. Defining the objective in optiSLang • Enter a reasonable name for the signal function. This name will be used for the objective. • Define the objective function just by inserting the signal function name. The effect of the optimization is that the value of this function will be minimized. • As an alternative, you may use the integral-difference function Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

25. Starting an optimization run (ARSM) • Because we have only 4 parameters, a ARSM-Optimization would be a fast method to create a best design. • Define a new ARSM run with the modified problem-specification file and the start script. • To scan a wider design space, set the start range up to 75%. • To avoid endless calculations, set a maximum ARSM iteration number of 75. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

26. Postprocessing ARSM • After 75 Iterations, it was possible to achieve an excellent curve fitting. • Get the design values for the Gosh-equation as a result • See the very small difference betweenthe curves (Kurvenfitting) Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

27. Signal data of the ARSM • The best design is hardly to distinguish from the reference curve. Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

28. Excel Sheet • Comparision of the two curves in MS Excel – See the accurate fitting Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

29. Excel Sheet • With the optimization it was possible to reduce the quadratic error significantly! Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

30. Conclusion • Performing a parametric optimization with MS Excel as a solver,it was now possible to determine the parameters that have tofill in into a Gosh-Equation for calculating true stress true strain curves. • Because of the fast calculation speed it is possible to get good results in a short time. • The determined parameters can now transfered to a FE-Analysis programm like ANSYS or LS-Dyna. • For this case, the Gosh-Equation has the following values: σY(εp) = k • (ε0 + εp)n – p σY(εp) = 1109 • (0.0099 + εp)0.0766 – 34.8 Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang

31. Contact Information For further informations according to optiSLang or the used Excel macro, please contact us. DYNARDO GmbH Luthergasse 1D D-99423 Weimar Fon: +49 (0)3643 900830 Fax: +49 (0)3643 900839 support@dynardo.de Tutorial: Using MS Excel as a solver for fitting stress/strain curves with optiSLang