Use Sheet values in a Calculator

Keep the inputs of a calculation in a Sheet, read one cell with sheet and a block as a Matrix with sheet.mat, and see the Calculator follow a change.

Goal

Keep the inputs of a calculation in a Sheet: a notional, a rate and a 2 by 2 block of numbers. Read them into a Calculator, then change the notional and see the new result.

Before you start

Every Sheet has a workbook name, shown at the left of the address above the grid. The first Sheet on the page is Book1, the next Book2, and so on. Double-click the name to rename the workbook. Workbook names are case-sensitive.

A Calculator on the same page gets values from a workbook with two functions. Both take the workbook name and an address as text in double quotes:

  • sheet(book, ref): one cell as a number, or a range as a Series.
  • sheet.mat(book, ref): a range as a Matrix with the same rows and columns.

An address without a tab name uses the first tab of the workbook, whichever tab is on screen. To read another tab, put its name and ! before the address, such as "Data!B1". Cells, ranges and other sheets gives the full rules for addresses, ranges and named cells.

The guide uses these inputs: a label and a number in rows 1 and 2, and a 2 by 2 block in A4 to B5.

Cell Formula Shows
A1 notional notional
B1 50000 50000
A2 rate rate
B2 0.05 0.05
A4 2 2
B4 1 1
A5 1 1
B5 3 3

Steps

  1. Click Add widget and select Sheet. The workbook name shows Book1.
  2. Type each input from the table in Before you start into its cell. Press Enter after each one.
  3. Click Add widget and select Calculator.
  4. Type sheet("Book1", "B1") in the formula field. The result line shows 50000.
  5. Replace the formula with sheet("Book1", "B1") * sheet("Book1", "B2"). The result line shows 2500, the notional times the rate.
  6. Replace the formula with sheet.mat("Book1", "A4:B5"). The result line shows the block as two rows: 2 1 and 1 3.
  7. Replace the formula with det(sheet.mat("Book1", "A4:B5")). The result line shows 5, the determinant of the block.
  8. Type the interest formula from step 5 again. The result line shows 2500.
  9. In the Sheet, click B1, type 80000 and press Enter. Within one second, the Calculator result line shows 4000.

Result

The Calculator results for each formula:

Formula Result line
sheet("Book1", "B1") 50000
sheet("Book1", "B1") * sheet("Book1", "B2") 2500, then 4000 after step 9
sheet.mat("Book1", "A4:B5") a 2 by 2 Matrix: 2 1 and 1 3
det(sheet.mat("Book1", "A4:B5")) 5

The Calculator takes a copy of the cells each time it runs the formula. It runs the formula 120 ms after any change to the formula field. While the clock is live, it also runs a formula with sheet or sheet.mat once a second, so step 9 shows the new notional with no edit. In step, the formula runs again only when you click +1s or +1m, or edit the formula.

Other ways

Name the tab

sheet("Book1", "Sheet1!B1") returns the same number as sheet("Book1", "B1") here, 80000, because Sheet1 is the first tab. Use the tab name when the input sits on another tab.

Read a range as a Series

With a range, sheet returns a Series with one point per cell, taken row by row. sheet("Book1", "B1:B2") shows 2 samples and a chart. Functions that take a Series accept it: sum(sheet("Book1", "A4:B5")) shows 7. Empty and text cells in the range become gaps.

One cell with sheet.mat

With a single address, sheet.mat returns a number. sheet.mat("Book1", "B1") shows 80000, the same as sheet.

See Sheet and Calculator for the other controls of each widget.

If something goes wrong

  • E_NO_DATA ... is empty: the cell is empty. sheet never treats an empty cell as 0. Type a number in the cell.
  • E_TYPE ... holds text, not a number: the cell contains text, such as the label in A1. Check the address, or type the number without commas or a percent sign.
  • E_REF ... no workbook named: no Sheet on the page has that name. Compare the name in the formula with the name at the left of the Sheet address, including upper and lower case.
  • E_NO_DATA from sheet.mat: every cell of the block must contain a number. sheet.mat("Book1", "A4:C5") stops at the empty C4. Fix the range or fill the cell.
  • The result does not change after a Sheet edit: the Calculator clock shows step, so the formula has not run since the edit. Click +1s, or click the clock button to return to live.