Heat Loss From An Insulated Pipe

This Excel spreadsheet models heat loss from an insulated pipe. This is a very common system in the process industries - insulated pipes are everywhere, and engineers need a sound grasp of heat transfer principles to model their effects. Although the model in the spreadsheet is simplified to aid understanding, complexity can be easily added.

Liquid flows through the pipe, with heat exchanged with the insulation. Heat is lost from the insulation to the environment via convection (no radiation losses are considered). The thermal effects of the pipe wall are ignored (although this can be easily implemented).

Cross-Section of Insulated Pipe

These equations are used in the spreadsheet to define the heat transfer process.

  • q is the heat flowrate through the pipe and insulation (W m-1)
  • Ts is the temperature at the surface of the insulation (K)
  • Ta is the ambient air temperature (K)
  • Tf is the fluid temperature inside the pipe (K)
  • DO is the pipe diameter (m)
  • DS is the outside diameter of the insulated pipe (i.e. the pipe diameter plus two times the insulation thickness) (m)
  • k is the insulation thermal conductivity (W m-1 K-1)
  • ΔT is the temperature difference between the insulation surface and ambient air Ts-T(K)
  • hs is the insulation-to-air heat surface heat transfer coefficient (W m2 K-1)
The equation for the surface heat transfer hs coefficient is a correlation; any other valid relationship can be substituted.

The equations are implicit - the heat transfer coefficient is a function of the surface temperature Ts, but the surface temperature is a function of the heat transfer coefficient. 

Hence the equations need to be solved iteratively with Goal Seek in Excel. Simply 
  • break the loop by estimating a value of Ts
  • use this to calculate all other properties (including the heat transfer rate)
  • use the heat transfer rate to backcalculate Ts
  • use Goal Seek to make the two values of Ts equal by varying the estimated value of Ts (or any other parameter
You can easily modify the heat transfer equations to include more complex effects, such as effect of fouling on the pipe surface, multiple layers of different insulation, radiative losses, thick large pipe walls (which act as a heat sink) etc.

Calculate d-Exponent to Predict Pore Pressure Trends

This Excel spreadsheet calculates the d-exponent, a parameter used by drilling engineers to investigate ppore pressure trends when drilling into over-pressurized zones.

Normally, formation density increases with the drilling depth. But if the formation contains sand below the surface, then the drilling rate may increases with drilling depth. The d-exponent is used in several calculations to investigate these effects.

Calculate D-Exponent in Excel

Jorden and Shirley (1966) suggested the d-exponent method, basing it on the Bingham equation. A later adjustment to the equation by Rehm and Mcledon (1971) included the effect of mud weight

The modified equation to calculate the d-exponent is

d exponent
  • c is the shale compactibility coefficient
  • ρn is the mud weight equivalent (lb gal-1)
  • ρm is the mud weight used (lb gal-1)
  • D is drill bit diameter (in)
  • W is the weight on the bit (x 1000 lb)
  • R is the penetration rate (rpm)
  • N is the rotary speed.
The d-exponent is often used in Eaton's equation

Eaton's equation
  • P is the formation pore pressure (psi)
  • Pob is the overburden pressure (ft)
  • Z is the vertical depth (ft)
  • d is the d exponent
  • dn is the normal compaction trend of the d exponent
  • Pp is the formation pore pressure (psi)
  • Pp/Z is the normal pore pressure gradient (psi ft-1)

Modelling Reaction Kinetics in Excel

This spreadsheet demonstrates how you can model reaction kinetics in Excel with the solution of differential equations.

Consider this reaction scheme

where A and B are reactants, X and Y are catalysts, and k1 and k2 are rate constants.

The following coupled differential equations can be derived from the stoichiometry of the chemical reaction scheme.

where a(t), b(t), x(t) and y(t) are the concentrations of A, B, X and Y at time t. This is an initial value problem, where the concentrations x(t), y(t), a(t) and b(t) must be specified at time t = 0.

These differential equations can be easily solved in Excel using a simple forward-stepping finite difference scheme. The differential equations are discretized as follows

where Δt is the chosen time step. As long as Δt is sufficiently small, the solution will be accurate.

This is an initial value problem, where a(0), b(0), x(0) and y(0) need to be specified. The following picture illustrates the implementation in Excel.

These are typical results for the concentration profile of the reactants.

reaction kinetics in excel

The concentrations of the catalysts X and Y return to the values specified at the start of the simulation, as the reaction proceeds.

The principles illustrated here can be used to solve any other initial value differential equations arising from reaction kinetics in Excel.

Souders-Brown Equation Calculator

This Excel spreadsheet calculates the maximum allowable vapor velocity in a vapor-liquid separator with the Souders-Brown equation. The Souders-Brown equation is commonly used to size separators, scrubbers and knock-out drums.

The operating vapor velocity is specified to minimize the entrainment of liquid drops in the exiting vapor.

Souders Brown Equation in Excel

A wire mesh or de-entrainment pad is commonly used to force the separation of very small drops that cannot be removed by gravity alone. These coalesce the smaller droplets into larger ones fall due to the force of gravity.

Vapor-liquid separators are commonly used in the oil & gas, petrochemical industries, paper mills

The Souders-Brown equation was derived from a force balance on a droplet suspended in a gas flowing upwards. It was originally derived to size fractionating columns.

This is the equation.

Souders-Brown Equation
  • ρL is the liquid density (kg m-3)
  • ρL is the gas density (kg m-3)
  • K is the vapor or gas load factor (m s-1)
  • V is the maximum allowable vapor velocity (m s-1)
K has a value of 0.107 m s-1 for a separator with a mesh pad. However, the right value of K is largely dependent on the operating conditions and physical properties of the system. The GPSA Engineering Data Handbook tabulates values of K for various operating conditions.

Once you have the maximum allowable vapor velocity, you can then calculate the drum diameter by dividing the vapor flowrate by the allowable vapor velocity.

Inflow Performance Relationship of Vertical & Slanted Solution Gas-Drive Wells

This Excel spreadsheet calculates the Inflow Performance Relationship, or IPR, for vertical and slanted solution-gas drive wells.

The IPR of a well determines the relationship between its flowing bottom pressure, and the well production rate (or deliverability). IPR also helps engineers investigate the economics of a well, and is critical in optimizing the well, artificial lift design and determining the nature of the surface equipment.

For single-phase fluids, the IPR relationship is linear. However, when two-phase liquid and gas below its bubble-point pressure are produced, the relationship is non-linear.

Chang and Vogel relationships for IPR

Several researchers have studied this process, most notably Vogal (1968) and Cheng (1990). Cheng's semi-empirical correlation is applied to slanted wells, while Vogal's work applies to vertical wells.

Vogel and Cheng's equations are

  • q0 is the flow rate in bbl/day
  • q0,max is the oil flow rate at a flowing bottom hole pressure of 0 in bbl/day
  • pwf is the flowing bottom hole pressure in psi
  • pr is the reservoir pressure in psi
  • a0, a1 and a2 are empirical parameters that vary with the slant angle. In the spreadsheet these parameters are listed again several values of the slant angle. Intermediate values are linearly interpolated.

Download Excel Spreadsheet to Calculate Inflow Performance Relationship for Solution-Drive Gas Well

Ergun Equation Calculator

This Excel spreadsheet helps you calculate the pressure drop through packed and fluidizeds bed with the Ergun equation.

Fluidized beds are a commonly unit operation in the process industries. Their nature makes them particularly suitable venues for gas-solid catalyzed reaction, often in the petroleum industry to produce gasoline and other chenicals. They have also found use in the polymer industry, and bioreactors (in the form of liquid fluidized beds)

Ergun Equation

In 1952, Sabri Ergun derived the following equation to predict the pressure drop in packed beds.

  • ΔP is the pressure drop
  • L is the height of the bed
  • Dp is the particle diameter
  • ε is the porosity of the bed
  • μ is the gas viscosity
  • V0 is the superficial velocity (the volumetric gas flowrate divided by the cross-sectional area of the bed)
  • ρg is the gas density
The pressure drop required for minimum fluidization is given by

  • ρp is the particle density
  • εM is the porosity of the bed at minimum fluidization
By comparing the pressure drop given by the Ergun equation to the pressure drop for minimum fluidization, you can calculate the superficial velocity necessary for fluidization. At this point, bubbles of gas form and rise through the bed. This increases the effective volume of the bed.

Download Excel Spreadsheet to Calculate Ergun Equation

Liquid Volume in Partially Filled Horizontal Tanks

This Excel spreadsheet helps you calculate the liquid volume in partially filled horizontal tanks.

Tanks are often used in storing chemicals. Often, only the liquid height from a level indicator is known. This means you need a method of calculating the liquid volume based on the liquid height and the tank dimensions.

The tank ends can be

  • flat (so the tank is just a horizontal cylinder)#
  • dished (ASME F&D, or Flanged & Dished)
  • 2:1 elliptical 
  • hemispherical
The liquid volumes in a horizontal cylinder, and ASME F&D, 2:1 elliptical and hemispherical ends are given by these equations.

partially filled horizontal tank equations

  • D is the diameter of the cylinder and the heads
  • L is the length of the cylindrical shell
  • h is the height of liquid in the tank
The diagram at the top illustrates the notation.

Hence the total volume of liquid in the tank is simply the liquid volume in the cylinder plus 2 times the liquid volume in the heads.

The dimensions can be specified in any length unit. The unit of the volumes will simply be the length unit cubed.

This spreadsheet can be easily modified to created a Dip chart (i.e. a chart giving the liquid volume as a function of liquid height).

Fetkovich Decline Curve Analysis

This Excel spreadsheet plots Fetkovich decline curves for gas wells. Decline curve analysis is an empirical procedure that predicts the decline in production rates of gas and oil wells. 

Fetkovich (1968) improved on earlier work by Arps in predicting the declining production rate of oil and gas wells. He suggested that experimental production rate data could be overlaid on a graph and matched to a series of type curves. The production rate is then extrapolated into the future with the assistance of the curves.

The Excel spreadsheet reproduces the plot given in Figure 8 of the following reference.

Fetkovich, M.J., "Decline Curve Analysis Using Type Curves", Journal of Petroleum Technology (June 1980) 1065-77.

The decline curve rate-time equations are given below.

  • n is a factor that governs the shape of the back pressure curve
  • qDd is the decline curve dimensionless production rate
  • tDd is the decline curve dimensionless time

Water Breakthrough Time in a Vertical Oil Well

Consider an oil reservoir with an underlying water zone. An oil well that is producing from this reservoir will experience the phenomenon of water breakthrough if the well produces above its critical rate. Since economic considerations often demand high flowrates, this can be a real risk.

This Excel spreadsheet uses the Sobocinski-Cornelius method to calculate the water breakthrough time.  This method was published in 1965, and was derivied from laboratory tests. It uses these equations.


  • Bo is the oil formation volume factor
  • μo is the oil viscosity (cp)
  • μw is the water viscosity (cp)
  • φ is the porosity
  • h is the oil column thickness (ft)
  • hp is the perforated interval (ft)
  • Qo is the oil production rate (STB day-1)
  • α is 0.5 if M <=1, or 0.6 otherwise
  • M is the water-oil mobility ratio
  • tBT is the time to breakthrough
  • tD is the dimensionless time (Sobocinski-Cornelius method)
  • Z is the dimensionless cone height
  • ρw is the water viscosity (lb ft-3)
  • ρo is the oil density (lb ft-3)

Binary Distillation with McCabe-Thiele Method

This Excel spreadsheet uses the McCabe-Thiele method to calculate the number of theoretical stages needed for binary distillation.

Binary distillation is a common unit operation that separates two liquids (with one being more volatile, or "lighter"). The concepts are encountered in many branches of chemical and petroleum engineering.

Simply enter your parameters at the top of the spreadsheet, including the
  • feed flowrate,
  • mole fraction of the light component (or more volatile component) in the feed,
  • mole fraction of light component in the top product,
  • mole fraction of light component in the bottom product,
  • reflux ratio,
  • relative volatility of the light component
  • and the q-line value.
The spreadsheet will automatically calculate flowrates throughout the column, the number of theoretical plates, the feed plate position, the minimum reflux ratio (from the Underwood equation), and the minimum number of theoretical plates (from the Fenske equation)

The spreadsheet also automatically draw the traditional McCabe-Thiele plot. This is a classic Chemical Engineering diagram and shows the number of theoretical stages, the position and slope of the q-line, and the relative volatility curve, and the purity of the top and bottom products.

Flow Regime Map for Two-Phase Flow

This Excel spreadsheet plots a flow regime map for the two-phase flow of liquid and gas in horizontal and vertical pipes.

Determining flow regime is important when calculating pressure drops and pumping requirements in the oil & gas industry, and chemical production. Additionally, some flow regimes are undesirable (such as slug flow, which can damage pipes through excessive vibration). Engineers will change pipe diameters and other process parameters so the flow regime is more favorable.

In the spreadsheet, simply enter the liquid and gas flowrates.

The spreadsheet will then plot the location of the flow on a flow regime map for horizontal and vertical pipes. The horizontal and vertical axes are the Gas Froude Number and Liquid Froude Number.

The spreadsheet uses the flow regime maps digitized from Shell DEP (the "DEP" is short for Design and Engineering Practice")

Many flow regime maps have been developed by different researchers. For example, 
  • Mandhane et al (1974) published a map for two-phase horizontal flow
  • Hewitt and Roberts (1969) published a map for two-phase vertical flow
  • Taitel and Dukel (1976) published a map for two-phase horizontal flow
There are, however, inconsistencies in the definition of flow regimes in these different maps. For example, some maps give different flow regimes at the same conditions.

The horizontal and vertical maps given in Shell DEP were developed by a single source, and have a consistent definition of flow regimes.  Therefore, engineers can compare potential flow regimes in horizontal and vertical pipes.

Natural Gas Viscosity with Carr, Kobayashi, and Burrows Correlation

This Excel spreadsheet uses the Carr, Kobayashi, and Burrows correlation to calculate the viscosity of natural gas. The correlation is valid for sweet and sour gas.

The Carr, Kobayashi, and Burrows correlation was published in 1954. It is valid between temperatures of 32 F to 400 F, reduced pressures below 20, and CO2, N2 and H2S concentrations below 15%.

The correlation was originally presented in graphs. However, this spreadsheet contains polynomial curve fits to the original graphs given by Dempsey (1965).

This spreadsheet also corrects calculates the viscosity (Standing & Katz, 1977) and pseudo-critical temperature/pressure of natural gas, but corrects for the presence of CO2, N2 and H2S.

A spreadsheet to calculate the viscosity of natural gas based on the Lee, Gonzalez and Eakin correlation is also available; this method, however, is only valid for sweet natural gas.

Download Excel Spreadsheet to Calculate Natural Gas Viscosity using Carr, Kobayashi and Burrows