This involves manually typing the entire 54B matrix into an Excel sheet and using VLOOKUP or INDEX-MATCH .
'Calculate thermal expansion coefficient Alpha = (K0 / (Density60 ^ 2)) + (K1 / Density60) + K2
=LAMBDA(Density,Temp, LET(Alpha,(341.0977/(Density^2)) + (-0.69859/Density) + 0.001373,EXP(-Alpha*(Temp-60)*(1+0.8*Alpha*(Temp-60))))) Astm Table 54b Excel
Ensure you are using the correct ASTM Table 54b relevant to your product and conditions.
ASTM Table 54B is the industry-standard reference used to determine the Volume Correction Factor (VCF) for crude oils and refined petroleum products. Specifically, this table applies to commodities with a defined density at 15°C, allowing users to convert Gross Observed Volume (GOV) to Gross Standard Volume (GSV) at standard temperature conditions. This involves manually typing the entire 54B matrix
Function VCF_54B(t As Double, dens15 As Double) As Double ' t in °C, dens15 in kg/m³ ' Use fitted polynomial coefficients for product group B Dim K0, K1, K2... as Double '... apply equation VCF_54B = Exp(-alpha * (t - 15)) ' simplified End Function
The generalized formula is:
Fill the matrix with the static VCF values from your ASTM documentation.
standards. For commercial or legal custody transfer, ensure your Excel tool is validated against the official ASTM International : Official tables typically round the VCF to 5 decimal places . You can use =ROUND(cell, 5) in Excel to match standard outputs. Density vs. Relative Density : Table 54B specifically uses Density @ 15°C ( Specifically, this table applies to commodities with a