Compressibility Factor Calculator for Excel

This Excel spreadsheet calculates the compressibility factor for natural gas using the Dranchuk-Abou-Kassem correlation.  Engineers need the compressibility factor to calculate natural gas flowrates (e.g., using the Weymouth Equation), viscosities (e.g. using the Lee, Gonzalez and Eakin correlation) or other properties

T_r is the reduced temperature, P_r is the reduced pressure, and ρ_r is the reduced density. The correlation is only valid for 0.20 ≤ P_r < 30, and 1.0 < T_r ≤ 30. The constants A1 to A11 were found by a best−fit to the Standing and Katz chart with an average absolute error of 0.585%.

The Dranchuk-Abou-Kassem equation is implicit; solving for Z requires an iterative solution method.  Several several methods exist in Excel, and the spreadsheet demonstrates two of these methods.

• A circular reference.  Make sure File>Options>Formulas>Enable iterative calculation is checked)
• Goal Seek.  Here we 
• guess a value for Z 
• use this to calculate ρ_r
• use ρ_r calculate Z
• use Goal Seek to minimize the difference between the guess and calculated values of Z by varying the guess value of Z

I've found that the result given via Goal Seek is more numerically stable (the circular reference often gives unrealistic results).  There's a button in the spreadsheet to initiate Goal Seek.

If you want Goal Seek to automatically recalculate the compressibility factor whenever the reduced temperature or pressure are changed, add this VBA to Sheet1 in the spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bSuccess As Boolean
    On Error Resume Next
    bSuccess = Range("diff").GoalSeek(0, Range("guessz"))
    On Error GoTo 0
    If Not bSuccess Then
        MsgBox "Goal Seek Failed for Compressibility Factor"
    End If
End Sub

You may also want to check out this Excel spreadsheet which uses the Beggs and Brill correlation to calculate the compressibility factor of natural gas.

Download Excel spreadsheet to calculate the compressibility factor with the Dranchuk-Abou-Kassem correlation