- Email: [email protected]
Tutorial: Simulating chromatography with Microsoft Excel Macros
TUTORIAL: Simulating Chromatography with Microsoft Excel Macros Akinde Kadjo and Purnendu K. Dasgupta* Department of Chemistry and Biochemistry The University of Texas at Arlington Arlington, TX 76019-0065, USA
SUPPORTING INFORMATION
S1
1. Isocratic elution. This case is already discussed in detail in the main text. 2. Gradient elution We are simulating three analytes X, Y, and Z. The analytes partition constant respectively KX, KY and KZ are dependent of the temperature T and the eluent concentration Phi. Figure S1 is a representation of the specific set up of our gradient elution
Figure S1. Simulation setup for gradient elution
.
S2
The formula view would be identical to the isocratic case except that there are three analytes. Figure S2 represents the macro for a linear gradient.
Figure S2. linear gradient macro module.
The macro executes 750 iterations when activated. The iteration number is stored in the variable i. Initial and final values for the methanol fraction in the eluent concentration are stated as the variables initial and final. M and N are the values of i at which the gradient begins and ends. Lines 3-5 from the bottom keep the methanol fraction at initial before M and make it final after N iterations. From iterations M to N, it linearly increases the methanol fraction from initial to final. Cells(20,2).value connotes the value of the cell in row 20 column 2 (where the value of , the methanol fraction is stored). These statements essentially change the value of as the gradient progresses, the dependent formulae present in cells D3:D5 then calculate KX, KY and KZ, respectively as a function of . The three statements in the macro beginning with Cells are for plotting the detector output. Note that plate 200 is row 203. When the mobile phase contents reach this row, next it will proceed to the detector. These statements serve to write the values of X, Y and Z sequentially and respectively in columns AH, AI and AJ (columns 34-36) just as they are about to enter the detector, the statement Cells(i,34).value for example ensures that at each iteration the value is written in a corresponding new row.
S3
Figure S3. Step gradient macro module
This is very similar to the linear gradient and gives a specified value for the six distinct steps of 1-M, M+1 to N-1, N to O-1, O to P-1, P to Q-1 and Q upwards. 3. Langmuir adsorption isotherm This spreadsheet is essentially the same as that for isocratic elution except that Cm and Cs are computed according to equations 8 and 7 respectively in columns L and K.
4. Freundlich adsorption isotherm The Freundlich isotherm spreadsheet shown in Figure S4 (formula view in Figure S5) must vary from the previous ones as the partition equation must be iteratively solved. Goal seek sequentially executes for all of the rows in the column to solve for Cm. The “equilibrate” columns J and K contain the values of Cs and Cm. Cm is calculated iteratively from Goal Seek and Cs. is computed therefrom. To implement Goal Seek, we sum the computed values of Cs and Cm in column P (labeled ‘add”) and calculate the difference from the value in P to what was originally taken in the stationary and mobile phases (in columns G and H); this difference is listed in column Q (labeled “zero”). Goal seek attempts to make Q as close to zero as it possibly can by varying Cm in cell K. . S4
Figure S4. Freundlich adsorption isotherm spreadsheet, normal view
Figure S5. Freundlich adsorption isotherm, formula view
S5
In addition, the macro (like the other previous macros) copies and pastes the values in columns M and N in columns G and H after each iteration. Calculating all rows with Goal Seek for every value of I is slow. Therefore goal seek will only be applied to the cells involved in the equilibrations. In the present macro only cells (6, 11) and (7, 11) will continuously have they value changed using goal seek. The rest of cells have their values changed by inserting another condition. For every ith iteration goal seek will be applied to all the cells from (7+ 1, 11) to (7+i, 11) by inserting another variable j.
Figure S6. Freundlich isotherm macro module
5. Presence of active sites The set-up in this particular case as presented in Figure S7. As before, we use a total initial analyte input quantity of 100 units. These can be entirely held on one plate but the plate contains 5 active site and they are filled first. As a result when analyte amount on any plate becomes <5, the analyte entirely stays with the active sites which exhibits a higher affinity for the analyte (higher partition constant). Figure S8 shows the formula view. We basically set up the spreadsheet as if there are two different analytes. The stationary and mobile phases corresponding to the regular site are in columns K and L, respectively, while those in the active sites are in columns M and N. Note that the IF statements check how much is there at any time in each plate and thence how it should be distributed before equilibration.
S6
Figure S7. Active sites spreadsheet
Figure S8. Active sites spreadsheet, Formula View
S7
Before mobile phase movement, the contents of the two hypothetical stationary phases and hypothetical mobile phases are separately added up in columns P and Q. As before, the macro copies values from column P and Q then paste them in column H and I. 9. Column Overloading The cells set up for this particular case are represented in Figure S9. Initial conditions are already inserted in the spreadsheet where the plates can only hold a maximum of 5 units of the analyte. X has overall 5 units while Y has 100 units. The cell’s formulas are similar to the isocratic elution case as it can be seen in Figure S10.
Figure S9. Column overloading spreadsheet
Figure S10. Column overloading spreadsheet, Formula View
10. On-column conversion/degradation of analyte. This scenario is only described here in the Supporting Information. We consider a case where Pure A is injected on-column. A constant fraction 0.01 (first order process) of analyte A with a partition constant KA= 0.4 is converted to analyte B with partition constant KB=0.6 on each S8
equilibration/iteration. This type of conversion (e.g., oxidation of sulfite to sulfate, Fe(II) to Fe(III)) are quite common. We implement this by making two different columns for A and B as it can be seen in Figure S11 with A as the only analyte present initially. A fraction of A is transformed into B after each iteration, that fraction is dictated by the constant fraction renamed cfc. Figure S12 gives a better insight of the formulas applied to the cells. The outcome of the peak will depend mainly on the constant conversion fraction (cfc). A high cfc value (such as 0.9) will degrade analyte A so quickly into B that it would be similar to initially injecting analyte B (See Figure S13 for the detector view of that particular case). On the other side a low cfc value (such as 0.0001) would have an insignificant impact on the analyte A to the point that it will not show any analyte B at the detector (See Figure S14 for the detector view of that particular case). Furthermore a reasonable cfc value (such as 0.01) will lead to significant presence of both analytes both during chromatography and as viewed by the detector. The detector is often unable to distinguish between A and B; depending on the K values and the cfc values chosen, this will lead to overlapping, or more commonly, a single asymmetric peak (See Figure S15 for the detector view of that specific case). In the previous cases listed A and B had respectively partition constant of 0.4 and 0.6, however when the analytes have a significant difference in their partition constant’s value the overlap of peaks is highly noticeable as it can be seen in Figure S16 where the partition constant for A and B were respectively 0.2 and 0.6.
Figure S11. Degradation of analyte spreadsheet
S9
Figure S12. Degradation of analyte, Formula View
10
Detector view
signal intensity , arbitrary units
8
6 A B 4
A+B
2
0 0
50
100
150
200
number of iteration or time (arbitrary units)
Figure S13. Degradation of Analyte A with cfc value of 0.9
S10
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S14. Analyte degradation with cfc value of 0.0001
S11
200
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S15. Analyte degradation with cfc value of 0.01
S12
200
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S16. Analyte degradation with KA=0.3 and KB=0.6 and cfc=0.01
S13
200
250
SUPPORTING INFORMATION
S1
1. Isocratic elution. This case is already discussed in detail in the main text. 2. Gradient elution We are simulating three analytes X, Y, and Z. The analytes partition constant respectively KX, KY and KZ are dependent of the temperature T and the eluent concentration Phi. Figure S1 is a representation of the specific set up of our gradient elution
Figure S1. Simulation setup for gradient elution
.
S2
The formula view would be identical to the isocratic case except that there are three analytes. Figure S2 represents the macro for a linear gradient.
Figure S2. linear gradient macro module.
The macro executes 750 iterations when activated. The iteration number is stored in the variable i. Initial and final values for the methanol fraction in the eluent concentration are stated as the variables initial and final. M and N are the values of i at which the gradient begins and ends. Lines 3-5 from the bottom keep the methanol fraction at initial before M and make it final after N iterations. From iterations M to N, it linearly increases the methanol fraction from initial to final. Cells(20,2).value connotes the value of the cell in row 20 column 2 (where the value of , the methanol fraction is stored). These statements essentially change the value of as the gradient progresses, the dependent formulae present in cells D3:D5 then calculate KX, KY and KZ, respectively as a function of . The three statements in the macro beginning with Cells are for plotting the detector output. Note that plate 200 is row 203. When the mobile phase contents reach this row, next it will proceed to the detector. These statements serve to write the values of X, Y and Z sequentially and respectively in columns AH, AI and AJ (columns 34-36) just as they are about to enter the detector, the statement Cells(i,34).value for example ensures that at each iteration the value is written in a corresponding new row.
S3
Figure S3. Step gradient macro module
This is very similar to the linear gradient and gives a specified value for the six distinct steps of 1-M, M+1 to N-1, N to O-1, O to P-1, P to Q-1 and Q upwards. 3. Langmuir adsorption isotherm This spreadsheet is essentially the same as that for isocratic elution except that Cm and Cs are computed according to equations 8 and 7 respectively in columns L and K.
4. Freundlich adsorption isotherm The Freundlich isotherm spreadsheet shown in Figure S4 (formula view in Figure S5) must vary from the previous ones as the partition equation must be iteratively solved. Goal seek sequentially executes for all of the rows in the column to solve for Cm. The “equilibrate” columns J and K contain the values of Cs and Cm. Cm is calculated iteratively from Goal Seek and Cs. is computed therefrom. To implement Goal Seek, we sum the computed values of Cs and Cm in column P (labeled ‘add”) and calculate the difference from the value in P to what was originally taken in the stationary and mobile phases (in columns G and H); this difference is listed in column Q (labeled “zero”). Goal seek attempts to make Q as close to zero as it possibly can by varying Cm in cell K. . S4
Figure S4. Freundlich adsorption isotherm spreadsheet, normal view
Figure S5. Freundlich adsorption isotherm, formula view
S5
In addition, the macro (like the other previous macros) copies and pastes the values in columns M and N in columns G and H after each iteration. Calculating all rows with Goal Seek for every value of I is slow. Therefore goal seek will only be applied to the cells involved in the equilibrations. In the present macro only cells (6, 11) and (7, 11) will continuously have they value changed using goal seek. The rest of cells have their values changed by inserting another condition. For every ith iteration goal seek will be applied to all the cells from (7+ 1, 11) to (7+i, 11) by inserting another variable j.
Figure S6. Freundlich isotherm macro module
5. Presence of active sites The set-up in this particular case as presented in Figure S7. As before, we use a total initial analyte input quantity of 100 units. These can be entirely held on one plate but the plate contains 5 active site and they are filled first. As a result when analyte amount on any plate becomes <5, the analyte entirely stays with the active sites which exhibits a higher affinity for the analyte (higher partition constant). Figure S8 shows the formula view. We basically set up the spreadsheet as if there are two different analytes. The stationary and mobile phases corresponding to the regular site are in columns K and L, respectively, while those in the active sites are in columns M and N. Note that the IF statements check how much is there at any time in each plate and thence how it should be distributed before equilibration.
S6
Figure S7. Active sites spreadsheet
Figure S8. Active sites spreadsheet, Formula View
S7
Before mobile phase movement, the contents of the two hypothetical stationary phases and hypothetical mobile phases are separately added up in columns P and Q. As before, the macro copies values from column P and Q then paste them in column H and I. 9. Column Overloading The cells set up for this particular case are represented in Figure S9. Initial conditions are already inserted in the spreadsheet where the plates can only hold a maximum of 5 units of the analyte. X has overall 5 units while Y has 100 units. The cell’s formulas are similar to the isocratic elution case as it can be seen in Figure S10.
Figure S9. Column overloading spreadsheet
Figure S10. Column overloading spreadsheet, Formula View
10. On-column conversion/degradation of analyte. This scenario is only described here in the Supporting Information. We consider a case where Pure A is injected on-column. A constant fraction 0.01 (first order process) of analyte A with a partition constant KA= 0.4 is converted to analyte B with partition constant KB=0.6 on each S8
equilibration/iteration. This type of conversion (e.g., oxidation of sulfite to sulfate, Fe(II) to Fe(III)) are quite common. We implement this by making two different columns for A and B as it can be seen in Figure S11 with A as the only analyte present initially. A fraction of A is transformed into B after each iteration, that fraction is dictated by the constant fraction renamed cfc. Figure S12 gives a better insight of the formulas applied to the cells. The outcome of the peak will depend mainly on the constant conversion fraction (cfc). A high cfc value (such as 0.9) will degrade analyte A so quickly into B that it would be similar to initially injecting analyte B (See Figure S13 for the detector view of that particular case). On the other side a low cfc value (such as 0.0001) would have an insignificant impact on the analyte A to the point that it will not show any analyte B at the detector (See Figure S14 for the detector view of that particular case). Furthermore a reasonable cfc value (such as 0.01) will lead to significant presence of both analytes both during chromatography and as viewed by the detector. The detector is often unable to distinguish between A and B; depending on the K values and the cfc values chosen, this will lead to overlapping, or more commonly, a single asymmetric peak (See Figure S15 for the detector view of that specific case). In the previous cases listed A and B had respectively partition constant of 0.4 and 0.6, however when the analytes have a significant difference in their partition constant’s value the overlap of peaks is highly noticeable as it can be seen in Figure S16 where the partition constant for A and B were respectively 0.2 and 0.6.
Figure S11. Degradation of analyte spreadsheet
S9
Figure S12. Degradation of analyte, Formula View
10
Detector view
signal intensity , arbitrary units
8
6 A B 4
A+B
2
0 0
50
100
150
200
number of iteration or time (arbitrary units)
Figure S13. Degradation of Analyte A with cfc value of 0.9
S10
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S14. Analyte degradation with cfc value of 0.0001
S11
200
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S15. Analyte degradation with cfc value of 0.01
S12
200
250
10
Detector view
signal intensity , arbitrary units
8
6
A B A+B
4
2
0 0
50
100
150
number of iteration or time (arbitrary units)
Figure S16. Analyte degradation with KA=0.3 and KB=0.6 and cfc=0.01
S13
200
250