Formula System¶
OpenSheets includes a full-featured formula engine. All formulas begin with = followed by a function name or expression.
Entering Formulas¶
Type = in any cell to start a formula. As you type, OpenSheets provides auto-complete suggestions for function names, helping you discover and insert functions quickly.
Cell References¶
| Reference Type | Example | Description |
|---|---|---|
| Single cell | A1 |
References one cell |
| Range | A1:B10 |
References a rectangular block of cells |
| Absolute | $A$1 |
Stays fixed when copied |
| Mixed | $A1, A$1 |
Partially fixed reference |
| Cross-sheet | Sheet2!A1 |
References a cell on another sheet |
| Cross-sheet range | Sheet2!A1:B10 |
References a range on another sheet |
Operators¶
| Operator | Example | Description |
|---|---|---|
+ - * / |
=A1+B1 |
Arithmetic |
^ |
=2^3 |
Exponentiation |
& |
="Hello "&"World" |
Text concatenation |
= <> > < >= <= |
=A1>10 |
Comparison |
Function Categories¶
- Math Functions -- SUM, AVERAGE, ROUND, and more
- Text Functions -- CONCATENATE, LEFT, TRIM, and more
- Logical Functions -- IF, AND, OR, IFERROR
- Lookup Functions -- VLOOKUP, INDEX, MATCH, XLOOKUP
- Date & Time Functions -- NOW, TODAY, DATEDIF
- Statistical Functions -- COUNTIF, SUMIF, MEDIAN
- Information Functions -- ISBLANK, ISERROR, TYPE
- Financial Functions -- PMT, NPV, IRR
- Array Functions -- UNIQUE, SORT, FILTER
- Web Functions -- HYPERLINK, IMAGE
- Chart Functions -- CHART, SPARKLINE
- Engineering Functions -- BIN2DEC, CONVERT
- Database Functions -- DSUM, DAVERAGE, DCOUNT