α=K0+(K1⋅D15)D152alpha equals the fraction with numerator cap K sub 0 plus open paren cap K sub 1 center dot cap D sub 15 close paren and denominator cap D sub 15 squared end-fraction K0cap K sub 0 K1cap K sub 1 Constants by Density Range The constants used in the calculation change depending on the product's density: Product Type Density Range ( K0cap K sub 0 K1cap K sub 1 346.42278346.42278 0.438840.43884 Transition Zone Use Special Formula: Middle Distillates 594.5418594.5418 Fuel Oils 186.9696186.9696 0.486180.48618 3. Implementation Resources
These implementations are designed to minimize selection bias, creating a reliable, structured approach for petroleum volume calculations.
The formula generally takes the exponential form: $$ VCF = \exp\left[-\alpha \times \Delta t \times (1 + K_2 \times \Delta t)\right] $$
Close the VBA Editor. You can now use =VCF_Table54B(A2, B2) directly in your cells. Method 2: Native Excel Formulas Astm Table 54b Excel
The historical ASTM IP standard requires specific rounding steps for intermediate values (like
Transitioning these complex tables into an Excel environment significantly reduces human error and speeds up cargo calculations on tankers and in refineries. Key Components of an ASTM 54B Excel Tool
It provides a multiplier known as the VCF or CTL (Correction for the effect of Temperature on Liquid). Why Use Excel for Table 54B? You can now use =VCF_Table54B(A2, B2) directly in your cells
Function VCF_54B(DEN15 As Double, T_OBS As Double) As Double Dim dT As Double, Alpha As Double, K0 As Double, K1 As Double, A As Double, B As Double Const e = 2.71828182845904 dT = T_OBS - 15 ' Determine Alpha based on density region If DEN15 <= 770 Then K0 = 346.42278: K1 = 0.43884 Alpha = (K0 + K1 * DEN15) / (DEN15 ^ 2) ElseIf DEN15 < 778 Then A = -0.0033612: B = 2680.32 Alpha = A + B / (DEN15 ^ 2) ElseIf DEN15 < 839 Then K0 = 594.5418: K1 = 0 Alpha = (K0 + K1 * DEN15) / (DEN15 ^ 2) Else ' DEN15 >= 839 K0 = 186.9696: K1 = 0.48618 Alpha = (K0 + K1 * DEN15) / (DEN15 ^ 2) End If
Should we include to convert your observed density first?
For Generalized Crude Oils (Table 54A) and Generalized Products (Table 54B), the API standard dictates distinct sets of K0cap K sub 0 K1cap K sub 1 constants. Method 1: Using VBA (Visual Basic for Applications) Why Use Excel for Table 54B
at an observed temperature of 15°C; the VCF must equal exactly 1.0000 .
=IF(AND(B3>=610, B3<=770), 0.4388, IF(AND(B3>770, B3<=787.5), 2680.3206, IF(AND(B3>787.5, B3<=838.5), 0, IF(AND(B3>838.5, B3<=1075), 0.4862, "Out of Range")))) Use code with caution. Step B: Calculate VCF (Cell B6)
Implementing ASTM Table 54B in Excel is a straightforward process that requires some basic knowledge of Excel formulas and functions. Here's a step-by-step guide to get you started:
This public link is valid for 7 days and shares a thread, including any personal information you added. This link or copies made by others cannot be deleted. If you share with third parties, their policies apply. Can’t copy the link right now. Try again later.