1 Introduction

Substance solubility is a very important task for many industrial applications. One of these industries is the paints and varnish industry, where the correct selection of a solvent system for polymers and pigments is essential for a quality assurance of a paint and its application. Although, “trial and error methods” continues to be a widely adopted methodology for solvent selection, which is time-consuming, intensive, and increases time-to-market, the right solvent may be found by the determination of the Hansen Solubility parameters (HSPs). HSPs were developed by Hansen [1] in his Ph.D. thesis. His theory is based in the idea that likes dissolve like, where one molecule defined as being “like” another if it bonds to itself in a similar way.

Today Hansen Solubility theory is of interest not only for the coating industry and has been found to be useful in other sectors, such as use of vegetable oils for biodiesel formulation [2], in pharmaceutical dosage form design [3], for understanding plant surface-agrochemical interactions [4] and even for solvent selection in artwork conservation materials [5].

Experimental method to determine the Hansen solubility parameters (HSPs) for a polymer is to evaluate whether or not it dissolves in selected solvents. Those solvents dissolving the polymer will have HSPs closer to those of the polymer, and should be placed, in a tridimensional space, inside a hypotheticals sphere, which coordinates center are the target HSPs. Computational determination of HSPs of that polymers it is do it by the means of a multiresponse optimization algorithms which include both an optimization routine and a Derringer’s desirability function [6]. Although today a professional software HSPiP [7] is available for the solution of this kind of problem, including the optimal dimension of the Hansen solubility sphere, sphere drawing and many other options, different software platforms had been used for this purpose.

Gharagheizi et al. [8] concluded that optimization method for calculation of HSPs is an important factor in the precision of obtained values of these parameters. Later he proposed the Nelder–Mead optimization algorithm for determination of HSPs and implemented the method in MatLab [9]. Vebber et al. [10] by using an evolutionary strategy as an alternative optimization procedure, showed that it is possible to improve the fitting of solubility spheres while applying Hansen’s determination method. Simplex algorithm was evaluated by Jabbari et al. [11]. Currently, the genetic algorithm optimization approach is included in HSPiP program.

A hybrid algorithm featured with the capability of locating multiple optimal solutions was developed and used to solve two optimization problems: the first maximizes model fitting (F), and the second minimized both sphere radii (R0) and outlier points [12]. Different computational platforms have been used for HSPs determination, such as MatLab [9] and Maple [13].

Microsoft Excel is a powerful spreadsheet with a Solver add-in. Researchers and engineers can define and solve optimization problems in an Excel workbook without the need of any other particular software.

Several solving methods and options are available in Excel Solver, such as Simplex LP, GRG Nonlinear and Evolutionary Solver, including problem size limits of 200 decision variables and 100 constraints in addition to bounds on the variables.

2 Multiresponse optimization problem of Hansen solubility parameters determination

2.1 Fundamental principles of Hansen solubility theory (HSP)

Hildebrand [14] announced the first definition of a “solubility parameter”, where his solubility parameter (δ) was defined as the square root of the cohesive energy density, according with the following expression:

$${\updelta = } \sqrt c = \sqrt {E /V_{m} }$$

where Vm is the molar volume of the pure solvent and E is its energy of vaporization. Therefore, when a liquid evaporates, all the bonds holding it together are broken. This parameter is a summation of the cohesion energy of volatile liquids into three parts, HSPs. The three Hansen solubility parameters quantitatively represent the nonpolar (atomic) bonding, the permanent dipole (molecular) bonding, and the hydrogen (molecular) bonding. Hansen parameters are related to Hildebrand parameters (shown as δT) through the following expression:

$$\begin{aligned} & E /V_{m} = E_{D} /V_{m} + E_{P} /V_{m} + E_{H} /V_{m} \\ \updelta_{T}^{2}& = \updelta_{D}^{2} + \updelta_{P}^{2} + \updelta_{H}^{2} \\ \end{aligned}$$

where δD, δP and δH are the HSPs corresponding to dispersion, polar and hydrogen bonding forces respectively. Nonpolar liquids, such as the aliphatic hydrocarbons, have weak intermolecular attractions, but no dipole moment. The source of their electromagnetic interactions is function of the random movement of the electron cloud surrounding every molecule, producing random changes in electron cloud distribution cause temporary dipoles. That is the atomic origin of the dispersion forces.

The polar forces are characteristics of molecules that have permanent dipole moments, called polar; while hydrogen bonding is particularly strong type of polar interaction, which occurs in molecules where a hydrogen atom is attached to an extremely electron-hungry atom such as oxygen, nitrogen, or fluorine.

HSPs of more than a thousand molecules are reported in the literature, as well as for many polymers, liquids and solid substances. HSPs of different solutes and solvents may be plotted in a tridimensional plot x,y,z (δD, δP, δH). Solute–Solvent miscible are those that are close in the space. The distance between the solvent (a) and solute (b) is usually called Ra and could be determined by the following expression:

$$R_{a} = \left[ {4 \cdot \left( {\updelta_{Da} - \updelta_{Db} } \right)^{2} + \left( {\updelta_{Pa} - \updelta_{Pb} } \right)^{2} + \left( {\updelta_{Ha} - \updelta_{Hb} } \right)^{2} } \right]^{1/2} .$$

Experimental evaluation of a solute with several solvents of known HSPs, usually between 20 and 30 different solvents, and the classification of them in either “good” or “bad” solvents by visual inspection or any other method, help us determine the Hansen parameters of the solute. Good solvents are enclosing inside a hypothetical sphere of diameter R0, called Hansen sphere; so “good solvents” will get a relative energy difference (RED), defined as the ratio Ra/R0 with values lower than 1 and bad solvents have a RED value higher than 1.

$$RED = R_{a} /R_{0} .$$

2.2 The multiresponse optimization problem of HSPs and radius of Hansen sphere determination

Hansen Solubility Parameters determination is a problem of multiresponse optimization, which could be solved in Microsoft Excel by the combination of a powerful Solver add-in and the use of desirability function as target [15].

Certainly there it is an error associated to both each good solvent outside the Hansen sphere and each bad solvent inside this sphere. The objective of the optimization problems is to minimize the error (fi) related to each solvent in a way that all good solvents fall inside the Hansen sphere and bad solvents outside. In this kind of problem minimize each fi function is an optimization problem, which are solved simultaneously by the use of the desirability function [6]:

$$DATAFIT = \sqrt[n]{{\prod\limits_{i = 1}^{n} {f_{i} } }}$$

where n is the number of solvents tested and fi is given by: \(f_{i} = e^{{ + \left( {R_{0} - R_{a} } \right)}}\) for good solvents outside the sphere (Ra > R0); \(f_{i} = e^{{ + \left( {R_{a} - R_{0} } \right)}}\) for bad solvents inside the sphere (Ra < R0); \(f_{i} = 1\) for bad solvents outside the sphere and good solvents inside

The smaller the errors for any solvents placed in a wrong position with respect to the Hansen sphere the function fi approximates to 1.00. In other words, when the value of such differences decreases to zero, the fit accuracy increases to 1.00. A good fit is given by a DATAFIT value close to 1.00. In order to include the sphere radius minimization in the multiresponse problem it is considered the function called “Size factor” [10], which multiplies the function DATAFIT.

$$Size\,factor = \left( {R_{0} } \right)^{{ - \frac{1}{n}}} .$$

3 Determination of Hansen solubility parameters and sphere radius through excel’s solver

The Excel workbook, developed by Steven Abbott, 2013, has as limitation that it does not allow to optimize simultaneously both, the HSPs and the minimum radius of the Hansen sphere by the use of Solver add-in, so it is necessary to execute “Solver” for each Hansen sphere radius and decrease it gradually until the DATAFIT function deteriorates.

The current authors add the ability to optimize simultaneously the HSPs and the sphere radius. In the showed example (Fig. 1), the Hansen Solubility Parameters of a group of solvents that may be used in the evaluation of the solute of interest (Table 1) are tabulated in cells B9: E94, while in column G are showed the results of the experimental solubility evaluation in a simple way (values of “1” or “0”). The values of “1” are given to those solvents evaluated as good and “0” for bad solvents; the cells corresponding to those solvents not use in the evaluation are left blank. The number of solvents evaluated is summarized at the end of the column (G95 cell).

Fig. 1
figure 1

Workbook for HSP determination using solver add-in

Full size image
Table 1 Solvents for the solubility test
Full size table

In the table located in the Excel Worksheet range A96:G101, the maximum and minimum values of the Hansen parameters for the good solvents are determined, as well as the guess values for the optimization process (cells B101: D101). Subsequently the values of Ra are calculated in column H for each i-solvent evaluated according to the expression:

$${\text{IF}}({\text{G}}i < >{}^{\prime\prime\prime\prime} ;{\text{SQRT}}(4*({\text{B}}i - {\text{dD}})^{2} + ({\text{C}}i - {\text{dP}})^{2} + ({\text{D}}i - {\text{dH}})^{2} );0)$$

where i represents the row number corresponding to each solvent in the table

While in column J are reported the values of Relative Energy Difference (RED) for each solvent evaluated.

Column I records the results of errors determination for the i-solvent, so that it express either “the error” or “lack of adjustment” of the model. Column I shows values lower than 1 for good solvents outside the sphere of Hansen or bad solvents inside it. In case of a suitable adjustment of the solvent with respect to the sphere, the corresponding cell adopts the value of 1.

The expression in Microsoft Excel for this condition can be written as:

$${\text{IF}}\left( {{\text{G}}i = ;{\text{ IF}}\left( {{\text{H}}i < {\text{R}}\_;1;{\text{EXP}}\left( {{\text{R}}\_{\text{ - H}}i} \right)} \right);{\text{IF}}\left( {{\text{G}}i < >{}^{ \prime \prime \prime \prime} ;{\text{IF}}\left( {{\text{H}}i < {\text{R}}\_;{\text{EXP}}\left( {{\text{H}}i - {\text{R}}}\_ \right);1} \right);1} \right)} \right)$$

The maximum adjustment of each solvent is the solution to the proposed multiresponse problem or of the desirability function represented in cell I95, which is affected by, what Vebber et al., 2013 calls “size factor”, to generate the objective function to be maximized in the cell I6.

In Fig. 1 an illustration of the worksheet for this calculation is provided.

This problem may be solved with any of the algorithms available in Microsoft Excel for the solution of nonlinear optimization problems; the non-linear GRG method (Generalized Reduced Gradient) and the Evolutionary method. As illustrated in Fig. 2 the problem is to maximize the function located in cell I6 by changing cells B3: E3, subject to constraints of independent variables and the condition R0 ≥ Ramax (E4 ≥ L95).

$$\begin{aligned} & {\text{B}}3:{\text{D}}3 \le 20;\quad {\text{B}}3 \ge 13;\quad {\text{C}}3:{\text{D}}3 \ge 0 \\ & 12 \ge R0 \ge 1 \\ \end{aligned}$$
Fig. 2
figure 2

Solver configurations for HSPs and sphere radius optimization

Full size image

The Solver configuration is showed in Fig. 2 and the optimization results with the use of both methods are illustrated in the following Table 2.

Table 2 Optimization results obtained with the use of solver
Full size table

GRG solver method looks at the gradient or slope of the objective function as the input values (or decision variables) change and determines that it has reached an optimum solution when the partial derivatives equal zero.

Of the two nonlinear solving methods available, GRG Nonlinear is the fastest. That speed comes with a compromise though. The downside is that the solution you obtain with GRG algorithm is highly dependent on the initial conditions and may not be the global optimum solution.

The Evolutionary algorithm is more robust than GRG Nonlinear because it is more likely to find a globally optimum solution. However, this solver method is also slower.

A nice compromise between the speed of the GRG Nonlinear algorithm and the robustness of the Evolutionary algorithm is GRG Nonlinear Multistart. You can activate this option through the Solver Options window, under the GRG Nonlinear tab.

As can be seen, there is a slight difference between the results obtained by one optimization method or another. Of course, our problem does not try to find an absolute optimum (maximum or minimum), but to define a region, as reduced as possible, that satisfies the multiresponse desirability function; hence, the location of an “optimal” point within that region may vary depending on the specificities of the optimization method employed. The results obtained by several researchers in their studies and their comparison with those obtained in Microsoft Excel with the use of the two optimization methods are showed in Table 3 for a better illustration of potential of Microsoft Excel Solver for the solution of this kind of problem.

Table 3 Results from literature compared with the obtained in excel worksheet
Full size table

Microsoft Excel do not have direct option either for 3D data scattering nor sphere drawing, but solvents and solute HSPs may be plotted in Statgraphics Centurion, Minitab or any other computational tool available. The Hansen solubility sphere generated in HSPiP program is showed in Fig. 3.

Fig. 3
figure 3

Hansen solubility sphere generated in HSPiP program

Full size image

The Microsoft Excel Workbook with this example may be downloaded from the following Website (16): https://www.hansen-solubility.com/HSP-science/basics.php.

The workbook can be improved if a solvent database is included and Excel VBA programming facilities are used to provide users with a search interface for the selection of solvents to be used in the evaluation, as well as alternatives to save and load files, as illustrated in Fig. 4. These improvements can be understood by the authors.

Fig. 4
figure 4

User forms of excel VBA to improve excel workbook for HSPs determination

Full size image

4 Conclusions

Simultaneous determination of HSPs and radius of Hansen sphere with the aid of the Solver add-in of Microsoft Excel is shown in this paper. The solution involves the use of desirability function to solve a Multiresponse optimization problem and the results are compared with the solution reported by either HSPiP software and other computer platforms, such as Matlab. Several studies from literature are also compared with the solver solution showing that the answer changes depending on the optimization method used and its search procedure because the answer is an optimal region and not a point.