News & Comment / Research Resources

ANALYSIS OF EXPIRED AIR WITH GASCALC: an automated spreadsheet

David J Egan BSc

National Coaching and Training Centre, University of Limerick, Ireland. Email:

Sportscience 3(3),, 1999 (1549 words)

Reviewed by Stephen Seiler PhD, Institute for Sport, Agder College, 4604 Kristiansand, Norway


Analysis of expired air collected in bags is the reference standard for determination of oxygen uptake. For aerobic steady-state exercise, it is also possible to determine energy expenditure and substrate utilization. I present here an automated Excel spreadsheet for the analyses. Reprint · Help


KEYWORDS: Douglas bag, fuel utilization, indirect calorimetry, oxygen uptake


Download GasCalc: automated (Excel 5, 690 K) · simple (Excel 4, 23 K)



Measurement of the rate of oxygen uptake (VO2) and the rate of carbon dioxide production (VCO2) during exercise is often useful. For example, determination of the maximal oxygen uptake (VO2max) provides a guide to fitness for particular activities and may be used as a marker during training programs. In addition, VO2 and VCO2 may be used to calculate energy consumption and substrate utilization during exercise. The procedure, known as indirect calorimetry, is reasonably accurate for aerobic steady-state exercise.

One of the oldest methods of sampling expired gas involves the subject breathing ambient air through a one way valve connected to an airtight container known as a Douglas bag. Expired gas may thus be sampled, and the relative concentrations of O2 and CO2 in the sample may be measured (typically by means of electronic gas analyzers). Comparison can then be made between the composition of inspired and expired air, allowing VO2 and VCO2 to be determined. For good descriptions of the Douglas bag method and the associated calculations, see Consolazio et al (1963), McArdle et al (1994), and Wasserman et al (1994). The Douglas bag method is generally considered to be the most accurate method of expired gas analysis.


GasCalc is a Microsoft Excel spreadsheet I have devised to automatically calculate metabolic variables from Douglas-bag data. GasCalc is written in Visual Basic for Applications, which according to Microsoft is "…the powerful and easy-to-use programming language used in Microsoft Excel". From a subjective viewpoint, Visual Basic does seem to be fairly useful, if a little cumbersome. The control structures are very similar to the BASIC computer language and include For-Next loops, Do-Loops, and If-Then-Else statements. One of the big advantages of GasCalc is that results end up in an Excel spreadsheet, enabling easy data management and statistical analysis.

What Does GasCalc Do?

GasCalc prompts the user for input of ambient conditions and Douglas bag sample data. Minute ventilation (VE), VO2, and VCO2 are then automatically calculated and displayed. These data are used to estimate the following variables:

  • Rate of energy expenditure.
  • Rates of absolute and proportional carbohydrate and fat utilization.
  • Energy expended during each stage.
  • Fat and carbohydrate utilized during each stage.
  • Whole-trial energy expenditure.
  • Whole-trial fat and carbohydrate utilization.

The user is prompted to input ambient pressure, temperature and humidity. Then, for each sample the user must input:

  • Volume and temperature of the expired gas sample.
  • Duration over which the sample was collected.
  • Relative concentration of O2 and CO2 (FEO2 and FECO2 respectively) of the sample.

The inspired volume (VI) is estimated from VE by means of the Haldane transformation, a good description of which can be found in McArdle, Katch and Katch (1994), Appendix 2. The concentrations of O2 and CO2 in inspired air (FIO2 and FICO2 respectively) are assumed by default to be 20.93 % and 0.04 % respectively, although the program allows this setting to be changed.

How Does GasCalc Work?

On opening GasCalc within Excel v5.0, a series of prompts are displayed for entry of ambient conditions. A further series of prompts are then displayed for entry of each set of sample data. After data entry, the user is given a series of options, including further data entry and/or creation of charts. Input data are automatically assigned to particular cell addresses, and results are automatically calculated and displayed in appropriately labeled columns. The custom charting routine within GasCalc enables the user to create an xy type chart for selected variables with less rigmarole than the usual Excel chart wizard.

The calculation of VE, VO2, VCO2 and other variables is achieved by means of custom functions that are based upon the formulas shown at the end of this document. These functions are similar to those built into Excel (e.g. "SUM" and "AVERAGE" functions), and perform an operation on some variables that are entered (either directly as numbers, or as cell addresses). For example, if the formula =SUM(2,2) is entered into a cell within Excel, the number displayed is "4" (i.e. the sum of 2 and 2). If the formula =SUM(A1,B1) is entered into a cell, the number displayed is the sum of whatever numeric value is in cell A1 and whatever numeric value is in cell B1.

Within GasCalc, if the formula =VO2STPD(a,b,c,d,e) is entered (where: a = VE, b = FEO2, c = FECO2, d = FIO2, and e = FICO2) the variables a,b,c,d and e are input into the formula for VO2 (STPD) and the result is displayed. Instead of entering actual numbers, the cell addresses that contain the relevant variables may be entered. If any of the values contained in the cells that contain variables for the function are subsequently altered, the result is automatically re-calculated. This is how GasCalc works. After data are input, custom functions are automatically generated in the output columns, and results are calculated based upon the input data which is contained within cells that are used as variables within the functions. Hence, if a wrong datum is entered, the relevant cell can be edited when the data entry routine is complete and results will be re-calculated accordingly.

Custom functions may be inserted anywhere within the sheet for one-off calculations. The easiest way to use the custom functions in this way is to choose "function" from the insert menu, and to select a 'user-defined' function from the displayed list (the custom functions have fairly obvious names, like VEBTPS which returns VE at BTPS). A window will be displayed asking for entry of named variables (which may be entered as either as a number or as a cell address containing the variable in question). Custom functions are written in the Visual Basic language, and it is an easy matter to write your own functions. The code used for the custom functions in GasCalc may be seen by unhiding (select format from the file menu, then sheet, then unhide) the sheet labeled "functions".

Final Points

  • The substrate utilization formulae used within GasCalc assume that protein metabolism does not contribute to energy metabolism. This assumption may not be justified, and is definitely not valid under certain circumstances. Compare the formulae used within GasCalc with those of Consolazio et al (1963), which incorporate urinary nitrogen as a measure of protein metabolism.
  • GasCalc is a bit large. Streamline it when you have entered data and Saved As a new name by unhiding and deleting the sheet: "subroutines". If you want to keep the charting function, keep the sheets "ccode" and "dialog1"; delete them if you don't. If you've got acres of disc space, don't bother deleting anything.
  • Feel free to contact me with suggestions, ideas and problems. One of the reasons for submitting GasCalc to Sportscience was to make contact with others in the field.
  • GasCalc is bound to have a few bugs--but remember, it's free, so don't get too upset. Good Luck!


Calculation of VE
at ambient temperature, pressure and saturated with water vapor (ATPS)

where V = volume (L) and t = duration of sample (s).

Calculation of VE
at body temperature, pressure and saturated with water vapor (BTPS)

where PB = barometric pressure (mmHg), T = temperature of sample gas in volume measuring device (° C), and PH2O = partial pressure of water at T (mmHg).

Body temperature is assumed to be 37°C, and PH2O at body temperature is assumed to be 47.08 mmHg.

Calculation of VE
at standard temperature, pressure and dry (STPD) (Wasserman et al., 1994)

Estimation of VI
(adapted from Wilmore and Costill, 1973)

where FEO2 = fraction of O2 in expired air, FECO2 = fraction of CO2 in expired air, FIO2 = fraction of O2 in inspired air, and FICO2 = fraction of CO2 in inspired air.

Calculation of VO2 and VCO2
(Consolazio et al., 1963)

Rates of substrate utilization and energy expenditure
(adapted from Consolazio et al., 1963)

Rate of fat metabolism (g/min) = (1.689 x VO2) - (1.689 x VCO2 )

Rate of carbohydrate metabolism (g/min) = (4.12 x VCO2 ) - (2.91 x VO2 )

Rate of energy expenditure (kJ/min) = (15.88 x VO2 ) - (4.87 x VCO2 )


Consolazio C, Johnson R, Pecora L. (1963). Physiological Measurements of Metabolic Functions in Man. New York NY: McGraw Hill

Mc Ardle WD, Katch FI Katch VL (1994). Essentials of Exercise Physiology. Malvern PA: Lea & Febiger

Wasserman K, Hansen JE, Sue DY, Whipp BJ, Casaburi R, (1994). Principles of Exercise Testing and Interpretation. 2nd Ed. Malvern PA: Lea & Febiger

Wilmore JH, Costill DL (1973). Adequacy of the Haldane transformation in the computation of exercise VO2 in man. Journal of Applied Physiology, 35, 85-89

Edited by Stephen Seiler and Will Hopkins..
Webmastered by Matt Kerner.
Published December 1999.