Extending Microsoft excel and Hansen solubility parameters relationship to double Hansen’s sphere calculation

Previous studies have shown the feasibility of using the Microsoft Excel "Solver" add-in to determine the Hansen solubility parameters and the radius of the Hansen sphere. Compounds such as di-block copolymer or ionic liquids are best represented by a Hansen double sphere, calculated with the HSPiP software; the development of other tools for evaluating this type of case is not reported in the literature. This communication provides the steps for the determination of a Hansen double sphere with the help of an evolutionary algorithm of Microsoft Excel, validated with, five study cases reported in the literature, with the HSPiP software itself. Other improvements for Microsoft Excel 365 version are also described. The workbook updated and posted in Hansen Web page is a useful tool available to the research community interesting in Hansen solubility parameters experimental determination and fit either single and double Hansen’ sphere. In some cases, sphere radius is better reduced than with the use of HSPiP. The "Chemicals” sheet has been included with more than 1200 Hansen Solubility Parameters (HSPs) of solvents and compounds. The worksheet named “HSP Solvents Blends and Chi” has been updated with more functionalities, while the worksheet named “Find a Similar Substance” allows the user to search the entire database to find HSPs of compounds that match the constraints declared. Two-dimensional graphics of the HSPs is also available The workbook updated and posted in Hansen Web page is a useful tool available to the research community interesting in Hansen solubility parameters experimental determination and fit either single and double Hansen’ sphere. In some cases, sphere radius is better reduced than with the use of HSPiP. The "Chemicals” sheet has been included with more than 1200 Hansen Solubility Parameters (HSPs) of solvents and compounds. The worksheet named “HSP Solvents Blends and Chi” has been updated with more functionalities, while the worksheet named “Find a Similar Substance” allows the user to search the entire database to find HSPs of compounds that match the constraints declared. Two-dimensional graphics of the HSPs is also available


Introduction
Hansen's solubility parameters (HSPs) allow us to express the affinity between two or more substances and have a thermodynamic nature. The theoretical foundation, estimation methods and experimental determination have been extensively treated in the literature [1,2]. This affinity can be defined as the degree of similarity between the solute and solvent molecules in terms of the solute-solvent interactions at infinite dilution and other theories are reported to express the solvent-solute relationship, such as the determination of the Kamlet-Taf parameters [3], and methods for the selection of solvents such as the solvent similarity index [4], although this article is focused on a particular case of the HSP determination and is beyond the comparison of solvent selection methods.
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 to determine Hansen's parameters of the solute. There are some softwares and algorithms for HSPs calculation, as was previously explained [5], but HSPiP software is the most complete and extended tool for this purpose [6].
HSPiP software allows not only to determine the HSPs of the target substance, but also to define the region in which other related solvents can be found, called a Hansen sphere. Usually, a single sphere is in-off to represent the solute's solubility region, but there are some particular cases, such as di-block copolymer, which are better described with two spheres [6]. Other substances, such as ionic liquids, which have an ionic domain and a nonpolar side that representing a neutral domain, may be also better represented by a Hansen double sphere; Agata and Yamamoto [7] calculated the HSPs of eight ionic liquids with different combinations of cations and anions using the double sphere methodology. Ionic liquids, as the family of 1-Alkyl-3-methylimidazolium Bromide ([CnC1im]Br, n = 5, 6,7,8) were also represented by a double Hansen' s sphere [8]. Nonionic solid compounds, as surfactants, were well fitted to a Hansen' s double sphere, because one sphere represents the hydrophilic group of the surfactant and the second one the hydrophobic group [8].
Yamamoto in his web page [10], showed the case of the swelling grade of a Fluoro-Polymer, which seems a hydrophobic polymer, because swelling very well to gasolineethanol hydrophilic solvents, indicating its double nature.
In previous work, the Microsoft Excel (ME) Workbook developed by Steven Abbott for HSPs determination using the Excel Solver add-in with the ability of Hansen's sphere radius determination was improved. HSPiP software is probably the best and the only tool available for double Hansen's sphere calculation. This communication provides the necessary information to extend the potentialities of the evolutionary algorithm of ME solver to the calculation of double Hansen's sphere and other improvements available for ME 365 version.

Materials and methods
Microsoft Excel 2010 or higher can be used to determine both the coordinates and radius of systems characterized by a double Hansen solubility sphere. For the optimization of these parameters, the evolutionary algorithm has been used, whose adjustment values were: population size of 100, a random seed of 0, mutation rate of 0.75 and convergence of 0.0001. The [7], polyoxyethylene(23)lauryl ether (PEL) [9] and a Fluoro-polymer [10]. The results were also verified with HSPiP software [6].

Extending the spreadsheet to a double Hansen's sphere calculation
Details of the ME spreadsheet for HSPs optimization for a single Hansen sphere was described in detail in previous work [5]. As it was explained, this kind of problem does not try to find an absolute optimum (maximum or minimum), but rather to define a region, as reduced as possible, which satisfies a multiresponse function, where all good solvents must be within one of the two spheres and all bad solvents remain outside both. Therefore, the probability of convergence to different solutions, each time the algorithm is executed, is higher in this case than when a simple sphere is fitted, even when HSPiP is used. This uncertainty can be discerned by the experimental evaluation of new solvents or solvent mixtures, whose HSPs are in the border of spheres. The Excel spreadsheet is similar to the Steven Abbott spreadsheet, where the implementation of the calculation of HSPs in ME is extensively detailed, with the differences that two Hansen's spheres are evaluated for each solvent, and the fit (f i,j ) related to them is given by: where: i: solvent tested (for n solvents), j: sphere number (for m sphere; 2 in this case), R 0j : Radius of sphere j, R a i,j : Distances from center of j sphere to i solvent.
Good solvents have a bad fit (fit less than 1) when they fall outside of both Hansen's spheres; that is, a good solvent must remain within one of the two spheres, while bad solvents must remain outside both spheres. This condition represents the total fit (f i,t ) and is calculated in column "L" using the following Excel formula for each solvent tested: where in "G" column are records the score of the solvents, "i" is the row number, while in "J" and "K" columns the f i,j values are calculated. The identification of good solvents outside the spheres (WO) and bad solvents inside (WI) is highlighted in the column "O" with the following function: The objective function (OF) results from a multiplication of two product functions (P 1 , P 2 ), one aimed at adjusting the good and bad solvents and the second one at minimizing the radius of the spheres R 0j .
where "n" is the number of solvents tested.
The Excel model has been validated with the literature results mentioned above, which are summarized in Table 1. It shows the results of the adjustment in Excel and their comparison with the values reported in the literature and the verification of the adjustment with the HSPiP software. A tutorial is supplied as supplementary information.
As it can be seen, in all cases, the solution with ME provides a good fit; only for PEL and Fluoro-polymer, the adjustments with HSPiP (Table 1) were slightly higher. However, for most of the cases evaluated, the Hansen spheres obtained through optimization with ME report a smaller radius. Figures 1 and 2 show the Hansen double spheres for each case evaluated, obtained by the HSPiP program and according to the results of ME.
In Figs. 1 and 2 is shown that all cases have similar behavior; however, the optimization with ME reports spheres with a smaller radius, without affecting the n adjustment of WO and WI. The objective function used by the HSPiP program is not known, but the one presented in this work has two components; a product function (P1) aimed at treating that all the good solvents fit inside the spheres and the bad solvents outside, while the second product function (P2) is focused on minimizing the radii of the spheres. The optimization algorithm used and its search strategy for the optimum determine the relative weight of P1 and P2 during the optimization process, and in the evolutionary method of Excel's Solver seems to be more effective in reducing the radius of the spheres than that used by the HSPiP program.
A slightly lower fit with ME is obtained for PEL and Fluoro-polymer examples. As has been explained in previous works [3], the optimization problem that concerns does not seek an absolute optimum (maximum or minimum) but rather to determine a region, as reduced as possible (spheres of minimum radius) that satisfies multiple answers, where to include or exclude each solvent in the sphere is an objective to be satisfied. Therefore, there are multiple solutions with small variations between them that satisfy the multi-response optimization. For this reason, it is not possible to conclude that one method is better than other; both tools, HSPiP and ME allow to solve this type of problem and provide valid solutions.

Improvements for ME 365
The new workbook, developed for ME 365, has other facilities for users. The "Chemicals" sheet has been included with more than 1200 HSPs of solvents and compounds, which can be incorporated into the calculation spreadsheets only by entering the Chemical Abstract number in column A. The management of matrixes and the use of the new FILTER function, only available in this ME version, facilitates the rendering of two-dimensional graphics from HSP.
The worksheet named "HSP Solvents Blends and Chi" has been updated with more functionalities such as: graphing the HSP coordinates and radius up to 5 substances (the reference and 4 more-selecting them in the N10:N14 range), calculation of the spheres overlap and the calculation of the proportions, up to a quaternary blend, to obtain the minimum distance possible through SOLVER optimization plug-in (substance constraints can also be included in the W25:X28 range). Finally, a second worksheet named "Find a Similar Substance" allows the user to search the entire database to find HSP compounds that match the constraints included in the J3: L6 range table.

Conclusion
The evolutionary algorithm of the solver add-in from Microsoft Excel allows solving optimization problems that need to be represented by a double Hansen solubility sphere. The validation of the sheet, enabled for this purpose, shows that it is feasible to obtain adjustments similar to those obtained with the HSPiP software, despite the complexity of this type of multiobjective optimization, where the optimization process focuses on obtaining an optimal region and not an absolute optimal. Obtaining spheres with smaller radii with ME results from the combination of the elaborated objective function and the optimization algorithm used. A full solvents database and two dimensional graphic illustrations of the results were included in this new ME version. (2022) 4:185 | https://doi.org/10.1007/s42452-022-04959-4 Short Communication

Declaration
Conflict of interest Authors declare there is not conflict of interests.

Competing interest Authors declare they have no financial interests.
Open Access This article is licensed under a Creative Commons Attribution 4.0 International License, which permits use, sharing, adaptation, distribution and reproduction in any medium or format, as long as you give appropriate credit to the original author(s) and the source, provide a link to the Creative Commons licence, and indicate if changes were made. The images or other third party material in this article are included in the article's Creative Commons licence, unless indicated otherwise in a credit line to the material. If material is not included in the article's Creative Commons licence and your intended use is not permitted by statutory regulation or exceeds the permitted use, you will need to obtain permission directly from the copyright holder. To view a copy of this licence, visit http:// creat iveco mmons. org/ licen ses/ by/4. 0/.