The Ultimate Guide to Implementing ASTM Table 54B in Excel ASTM Table 54B (Generalized Products, Correction of Volume to 15°C) is a cornerstone standard in the oil, gas, and petrochemical industries. It provides the Volume Correction Factors (VCF) needed to convert petroleum liquid volumes measured at observed temperatures to standard volumes at 15°C.
If you want to refine this spreadsheet further, let me know:
ASTM Table 54B is part of the Petroleum Measurement Tables jointly issued by the American Society for Testing and Materials (ASTM), the American Petroleum Institute (API), and the Institute of Petroleum (London) (IP). Specifically, Table 54B belongs to . Astm Table 54b Excel
Method 1: Building a Table 54B Calculator Using Excel Formulas
=ROUND(EXP(-((613.97226/(A2^2)) + (0.00000)/A2) * (B2 - 15) * (1 + 0.8 * (B2 - 15) / 6300)), 4) The Ultimate Guide to Implementing ASTM Table 54B
Master Guide to ASTM Table 54B in Excel: Calculations and Implementation
The VCF itself is calculated using the thermal expansion coefficient ( Specifically, Table 54B belongs to
These coefficients are non-public for licensing reasons, but many legitimate sources (including some national metrology institutes) offer them under license.
Function VCF_Table54B(Density15 As Double, TempC As Double) As Variant Dim K0 As Double, K1 As Double Dim Alpha15 As Double, DeltaT As Double Dim VCF As Double ' Constants for Table 54B (Generalized Products) K0 = 186.9696 K1 = 0.4862 ' Validate density limits for Table 54B (typically 610 to 1076 kg/m3) If Density15 < 610 Or Density15 > 1076 Then VCF_Table54B = CVErr(xlErrNum) Exit Function End If ' Calculate Delta T DeltaT = TempC - 15 ' Calculate Thermal Expansion Coefficient (Alpha at 15C) Alpha15 = (K0 + (K1 * Density15)) / (Density15 ^ 2) ' Calculate Volume Correction Factor VCF = Exp(-Alpha15 * DeltaT * (1 + 0.8 * Alpha15 * DeltaT)) ' Round to standard 4 or 5 decimal places as per ASTM guidelines VCF_Table54B = Round(VCF, 5) End Function Use code with caution. Close the VBA window and return to your worksheet. How to use it in your sheet:
For accurate results, it's crucial to ensure the API gravity values are within the specified ranges: for products with densities 653–778 kg/m³, temperatures from -18°C to 95°C; for 778–824 kg/m³, -18°C to 125°C; and for 824–1,075 kg/m³, -18°C to 150°C.
The final volume correction factor uses an exponential function to account for non-linear thermal expansion: