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

Compressibility Factor for Natural Gas

This Excel spreadsheet calculates the compressibility factor of natural gas using the Brill and Beggs correlation. Just enter the pressure, temperature, specific gravity and the mole fractions of N₂, CO₂ and H₂S.

The calculated value of the compressibility factor can be used to size natural gas pipelines (see my previous posts about the Weymouth, Panhandle A & B, IGT Distribution and Spitzglass spreadsheets for the sizing of natural gas pipelines).

The Beggs and Brill correlation was designed to calculate the compressibility of multiphase flow lines, and is essentially a best-fit equation to the standard Standing and Katz Z-factor chart. These are the equations as implemented in the Excel spreadsheet.

P_r and T_r are the critical temperatures and pressures and are calcualted from the mole fractions of N₂, CO₂ and H₂S specified in the spreadsheet. The correlation can't be used if the reduced temperature is below 0.92.

The compressibility factor models non-ideality in real gasses and is important when sizing long-distance natural gas pipelines.

Download Excel spreadsheet to calculate the Compressibility Factor.