(T.B.Co 10/29/2001)

Introduction

From raw data obtained from the laboratory, a model can be usually obtained by adjusting the parameters until the model fits the data closely. The models can then be used in other analysis or simulation programs. More recently, the SOLVER packages existing in several spreadsheet programs can aid in the determination of optimal values of the parameters.

Procedure

( In the example below, we show how one could obtain the Antoine coefficients given a set of temperature and vapor pressure data. )

1. Create the cells for model parameters.
2. Set up the columns for raw data.

 

 
 







3. Calculate the estimated values using the model. For this example, we use the Antoine equation to estimate the vapor pressure

 

 
 




First, input some initial guesses for A, B and C in the appropriate cells. Then use the model to estimate vapor pressure.






4. Generate a column containing the squares of estimate errors.

 

 
 








5. Create a cell for RMS (root mean squared error). The RMS is simply the square of the average of the squared errors.

 

 
 









6. Use SOLVER to minimize the RMS by changing the model parameters. Under the TOOLS menu, select SOLVER. A window should appear that allows the choice of operations. Choose to minimize the RMS value by changing the values of A, B and C. Then click on the SOLVE button. (Sometimes you may need to use SOLVER more than once until the RMS values do not change significantly anymore.)

 

 
 









After you accept the values obtained by SOLVER, the entries in the estimates should have changed accordingly.








Thus the Antoine equation model is determined to be






7. Generate a plot to show how close the estimated values fit the data.