Rukbat uses Furud to parse and evaluate
formulas. Enter a leading = to store a formula. The grid shows its result,
and the formula bar shows the original expression.
Cells and ranges
Rows and columns are one-based: A1 is row 1, column 1. A colon specifies
a rectangular range, such as A1:B3.
| Expression | Meaning |
|---|---|
=A1*3 |
Multiply the value in A1 by three. |
=SUM(A1:A2) |
Sum the values in A1 and A2. |
=AVERAGE(B2:B4) |
Average the numeric values in B2 through B4. |
=COUNT(B2:B4) |
Count numeric values in that range. |
=$A$1*B2 |
Keep A1 fixed when filling the formula into other cells. |
For example, enter 12 in A1, 30 in A2, and =SUM(A1:A2) in B1. B1 displays
42; editing A1 recalculates B1. References are tracked so dependent values
update when their inputs change.
Relative references move during fill operations. $A$1 fixes both the row and
column; $A1 fixes the column, and A$1 fixes the row. A fill that moves a
reference outside the grid produces #REF!.
Function completion
Type a partial function name, such as =SU, into the formula bar. When a
candidate appears, choose Use completion to insert the function name.
Add the parentheses and arguments yourself, then choose Apply:
=SUM(A1:A2)
Completion uses Furud’s standard function registry. To list the available functions from Ruby:
require "rukbat"
puts Furud::Functions.standard.names.sort
Consult Furud’s documentation for the full function language. Rukbat does not promise compatibility with every Excel or LibreOffice function.
Other sheets
Use ! between the sheet name and the cell reference. Quote names that contain
spaces with single quotes:
='Annual Plan'!A1+2
If Annual Plan!A1 contains 25, the result is 27. Use the sheet’s actual
name; the editor creates new sheets with names such as Sheet2.
The Ruby API can create a specifically named sheet:
book = Rukbat::Workbook.new
book.add_sheet("Annual Plan")
book.set(1, 1, 25, sheet: "Annual Plan")
book.set(1, 1, "='Annual Plan'!A1+2", sheet: "Sheet1")
book[1, 1, sheet: "Sheet1"] # => 27
Named ranges
Select a cell or range, enter a name such as TaxRate or Sales, and choose
Name range. Use that name in a formula:
=TaxRate*2
=SUM(Sales)
Names are case-insensitive. A name must parse as a name rather than a cell address or another expression. Redefining a name updates formulas that use it. Definitions follow supported row and column edits and undo/redo.
Invalid formulas and saving
A syntax error, such as =SUM(, rejects the edit and reports an error in the
status bar. An expression that parses but cannot calculate can display a
Furud error value, such as #REF!. Correct the referenced inputs or expression.
CSV/TSV saves calculated results by default, including error values as text.
To export formula expressions from Ruby, use values: :input:
Rukbat::CSVFile.write(book, "formulas.csv", values: :input)
This still exports only one sheet. Other sheets and named-range definitions are not serialized, so a formula that depends on them cannot be restored from that file alone. See files and exports.