Scripts - Spreadsheet data and variables

Data from the spreadsheet can be used in the script by using cell addresses, or column designators.

Spreadsheet cells

For example to assign a numeric value to cell A1:

A1=5;

To assign a formula to a cell, place the formula in quotation marks, e.g.

a2="sin(A1)";

You can also assign a value to a spreadsheet cell using the CELL() function as follows

CELL(1,A)=5;

Spreadsheet columns

Columns in the spreadsheet can be accessed as indexed arrays (vectors) using the column header. For example, if you have a column with header AGE in the spreadsheet, then AGE[2] references the value on the second data row in the column AGE.

When FOO is the name of a variable (column) in the spreadsheet, then

FOO[r]=x;

will assign the value x to the cell in the column with header FOO on row r

Spreadsheet cells and matrices

A MedCalc script can make use of matrices stored in a spreadsheet. If cell A1 contains the matrix {2,3,5|5,6,7|1,9,2} then the following statement will copy that matrix to the workspace variable mtrx:

mtrx=A1;

Individual elements of that matrix can be referenced by the INDEX function: as follows:

e=INDEX(A1,2,3); 

places the element on row 2, column 3 of the matrix stored in cell A1 into the worksheet variable e.

Although a script can place small matrices into a spreadsheet cell, the script will usually copy the formula into the cell.

A1={2,3,5|5,6,7|1,9,2};	// places the matrix {2,3,5|5,6,7|1,9,2} in cell A1
A1=magic(20); 			// places the matrix formula "magic(20)" in cell A1.
A2=2*A1; 				// places the formula "2*A1" in cell A2 of the spreadsheet.

See also

• Workspace variables
• System variables
• Spreadsheet commands
• Scripts