Astm Table 54b Excel Instant

Create a custom VBA function VCF_54B(ObsTemp, Density15) that applies the polynomial.

Example pseudocode:

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 ASTM standard uses a series of coefficients ($K_0, K_1, K_2$) to calculate the VCF.

Step A: Calculate the Thermal Expansion Coefficient ($\alpha$) The coefficient of thermal expansion describes how much the oil expands per degree of temperature change. $$ \alpha = \fracK_0 + K_1 \times \rho_15\rho_15^2 $$ (Note: The specific coefficients $K_0$ and $K_1$ are proprietary to API/ASTM, but historically, generalized values are used for approximation in custom software.) Astm Table 54b Excel

Step B: Calculate the Delta Temperature ($\Delta t$) $$ \Delta t = T_obs - 15 $$

Step C: Calculate the Volume Correction Factor (VCF) The formula generally takes the exponential form: $$ VCF = \exp\left[-\alpha \times \Delta t \times (1 + K_2 \times \Delta t)\right] $$

Step D: Rounding ASTM standards require the VCF to be rounded to 4 decimal places. The ASTM standard uses a series of coefficients

Using Table 54B for crude oil will yield errors of 0.5% or more. Crude oil contains dissolved gas and behaves differently. Crude oil must use Table 54A.

For engineers who hate typing long formulas, Visual Basic for Applications (VBA) is the answer.

Function ASTM_54B_VCF(Density60 As Double, ObsTempF As Double) As Double
    'Constants for Table 54B (Generalized Products)
    Const K0 As Double = 341.0977
    Const K1 As Double = -0.69859
    Const K2 As Double = 0.001373
Dim Alpha As Double
Dim DeltaT As Double
Dim VCF As Double
'Calculate thermal expansion coefficient
Alpha = (K0 / (Density60 ^ 2)) + (K1 / Density60) + K2
'Calculate temperature difference from base (60°F)
DeltaT = ObsTempF - 60
'Calculate VCF
VCF = Exp(-Alpha * DeltaT * (1 + 0.8 * Alpha * DeltaT))
ASTM_54B_VCF = Round(VCF, 6)

End Function

Usage in a cell: =ASTM_54B_VCF(0.85, 95) -> Returns the VCF for diesel at 95°F.

Even with a perfect Excel formula, users make mistakes. Here are the top three. End Function