Skip to main content
Mail a PDF copy of this page to:
(Your email address will not be added to a mailing list)
Show menu Show menu

The MedCalc spreadsheet

In MedCalc, data are entered in a spreadsheet (see How to enter data. You can open the spreadsheet by clicking Spreadsheet on the View menu, or clicking Data in the Contents window.

As in other spreadsheets, a cell can contain a text or string entry, or it can contain a number or a formula.

An example or a string entry is a name such as e.g. Howard. When used in a formula, strings must be placed between quotation marks, e.g. "Howard".

A cell can also contain a number, negative or positive, and with or without a decimal fraction. E.g. 5, -23.5, 0.03. The number of decimals displayed globally in the spreadsheet is selected in the Format spreadsheet box. However, a different number of decimals may be selected for a particular variable on the Column tab of this dialog box.

A formula may be a simple mathematical formula, such as e.g. SQRT(36). This formula will return the value 6, which is the square root of the number 36. Alternatively, the number 36 may be entered in a different cell of the spreadsheet, and the SQRT function can take the address of this cell as an argument, e.g. SQRT(B5).

Most of the formulas available in MedCalc work similar to their equivalents in other spreadsheet programs. When a formula begins with a cell address, this must be preceded by a = or + sign, but MedCalc formulas must not be preceded by the = character.

In the examples in this manual, a dot is used as decimal symbol (e.g., 75.5). In most European countries, a comma is used as decimal symbol, and the number 75.5 is displayed as 75,5.

Also, the functions in this manual use a comma to separate different arguments, e.g. power(5,2). In most European countries you must use a semicolon to separate arguments, e.g. power(5;2).

The decimal symbol and list separator are options in the Windows control panel and MedCalc uses these settings by default. This behavior is the same as in for example Excel.

You can however change the decimal symbol and list separator used by MedCalc in the Options panel (Regional settings tab).

Relative and absolute cell addresses

If you want to use the value of a cell in a formula in another cell of the spreadsheet, then you refer to this cell by means of its cell address. This cell address consists of a column indicator and a row number, e.g. cell D14 is the cell in column D, row 14.

When a cell address includes a $ character before the column or row number, the address is considered as an absolute address. When cell C10 contains for instance the formula $B$5+$B$6, the actual addresses of these two cells are stored in memory. When the formula is copied or moved to cell C11, e.g. by inserting a row at row 9 by pressing function key F3, the formula will still be $B$5+$B$6.

When a cell address does not include any $ characters, the address is considered to be a relative address. This means that the program does not store the actual address, but instead the program stores the number of columns and rows calculated relative to the cell containing the formula. When cell B5 contains the cell address B4, then the program does not store the address B4 as such, but it stores how to get from cell B5 to cell B4, in this case one row up. When the formula 2*B4 is copied from cell B5 to cell D10, the formula will be changed to 2*D9.

When cell B1 contains the formula SUM(B2:B10), and this formula is copied to cell C1, the formula will be converted to SUM(C2:C10). When it is copied to cell D51, the formula will be converted to SUM(D52:D60).

A cell address or formula may contain a combination of absolute and relative cell addressing, e.g.: A$2, $B10, $C5, SUM(B3:B20)*$A$1.

Ranges of cells are identified by two cell addresses separated by a colon. These cell addresses may also include relative and absolute cell addresses, e.g. B2:E8, $A$10:$A$25, D10:D10, etc.

When you design a spreadsheet, you should pay attention to when to use relative and when to use absolute cell addressing.

See also