Formulas are cells that begin with an =. You can write simple math expressions or call functions to compute a value, that will then be the resulting displayed value.
Just write a number yo.
=3
If you want the cell to just have a number, you don't have to make it a formula.
Enclose it in either single or double quotes.
='hello'
="goodbye"
If you want the cell to just have a string, you don't have to make it a formula and use quotes. But this is how you would pass one to a function call.
=cat('hello ', a1)
You can refer to cells excel-style:
=a1
The $ means "this row".
=a$
Spaces are allowed.
=a column
Cell references are case-insensitive.
=A ColUmN
This will probably change. It is kind of pointless for in-sheet references.
=[a, 1]
The row is optional
=[a]
You can refer to cells in other sheets. Use the longhand, but with an extra first value.
This is the only use of the "longhand" notation and we're looking for a replacement.
=[OtherSheet, a, 1]
The row is optional.
=[OtherSheet, a]
The bare name of a column is treated as a reference to that column, with a length determined by the last non-empty row in the sheet.
=a
You can also specify the start or end of the column range.
=a1:a4
You don't have to repeat the column name.
=a1:4
If you leave out the start, it's assumed to be row 1.
=:a4
If you leave off the end, it's assumed to be the last non-empty row in the sheet.
=a4:
If the reference starts in row 1, you can leave that off.
=a:4
The $ means "this row".
=a$:a4
To specify a row reference, specify two different columns.
=a1:c1
You don't have to repeat the row number.
=a1:c
You can leave out the row number to refer to the first row.
=a:c
Two-dimensional range references are not supported.
=a1 - b2 =a1 + b2 =a1 * b2 =a1 / b2 =a1 = b2 =a1 != b2 =a1 < b2 =a1 > b2 =a1 <= b2 =a1 >= b2
=-a1 =+a1 =!a1
See Functions for a list of the functions. You can also define your own. See User-Defined Functions.
To call a function, just write it's name and put the arguments to the function in parens.
=abs(a2)
Arguments can be numbers, strings, cells, rows, columns. See the function's description for what it expects.
Function calls are case-insensitive.
=ABS(a2)
=abs(number)
Returns the absolute value of the input.
This can also be applied to a range.
A number argument.
=abs(-2)
The above yields 2.
=avg(range)
Returns the average value of the input range, ignoring non-number inputs.
Returns 0 for an empty range.
A column range.
=array(args) =array(args, args) =array(args, args, args) =...
Returns a computed array composed of the given args, that can be passed to functions expecting a range like sum.
Any number of arguments that will be in the array.
They must evaluate to strings or numbers.
=call(funcname) =call(funcname, args) =call(funcname, args, args) =call(funcname, args, args, args) =...
Calls the function identified by funcname.
A string identifying the function to call.
Any number of arguments. They are forwarded to the identified function as arguments.
=call('pow', 2, 3)
The above will yield 8.
=cat(arg, arg) =cat(arg, arg, arg) =cat(arg, arg, arg, arg) =...
Concatenates the strings together. If at least one argument is an array, the output is an array. Otherwise it outputs a string.
A string or an array of strings.
If there is more than one array and they are different lengths, the shorter arrays will be treated as if the missing elements were blanks.
=cat('The', ' ', 'Cat')
The above will yield 'The Cat'.
=ceil(number)
Returns the smallest integer greater than or equal to the argument.
This can also be applied to a range.
A number argument.
=cell(sheetname, col, row) =cell(col, row)
Returns the value at the identified cell.
This is the dynamic equivelant to a cell literal.
(Optional) A string identifying the sheet the cell is in. If not given, the current sheet is assumed.
A string identifying the column the cell is in.
A number identifying the row the cell is in.
=col(sheetname, colname, rowstart, rowend) =col(colname, rowstart, rowend) =col(colname, rowstart) =col(colname) =col(sheetname, colname, rowstart) =col(sheetname, colname)
Returns the range identified by the parameters.
This is the dynamic equivelant to a range literal.
(Optional) A string identifying the sheet the range is in. If not given, the current sheet is assumed.
A string identifying the column the range is in.
(Optional) A number identifying the start of the range. If not given, the beginning of the column is assumed.
(Optional) A number identifying the end of the range (inclusive). If not given, the end of the column is assumed.
=count(range)
Counts the number of non-empty cells in the range.
Note that sum(range)/count(range) does not give the same result as avg(range) as count includes strings.
A column range.
=eval(code)
Evaluates the argument as it if were a formula in a cell.
A string that is the code to evaluate.
=eval('round(12.1)')
The above evaluates to 12.
=find(needle, haystack) =find(needle, haystack, default)
Returns the 1-based index of the location of needle in haystack.
If needle is not found, default is returned, or an error is returned if default is not given.
The value to lookup
A range in which to search for needle.
The value to return if needle is not found in haystack.
=floor(number)
Returns the largest integer value less than or equal to the argument.
This can also be applied to a range.
A number argument.
=if(condition, if_true, if_false)
Evaluates condition. If that evaluates to a truthy value (non-zero-length string or non-zero number), if_true is evaluated and returned. Otherwise, if_false is evaluated and returned.
condition can be a range or array, in which each value in the range is evaluated and if_true or if_false placed in the resulting array at the corresponding position. In this form, if_true and if_false can be single values or ranges/arrays. If it is an array, the value from the corresponding position is placed in the output array.
The condition to evaluate. Either a single value or a range/array.
The value to use if condition is truthy.
The value to use if condition is falsey.
=if(1, 2, 3)
The above yields 2.
| A | B | C | |
|---|---|---|---|
| 1 | 0 | 3 | 31 |
| 2 | 1 | 4 | 41 |
| 3 | 0 | 5 | 51 |
For the following, assume the above table.
=sum(if(a, b, c))
Yields 86 (31+4+51).
=sum(if(a, 1, c))
Yields 83 (31+1+51)
=sum(if(a, b, 1))
Yields 6 (1+4+1).
=sum(if(a, 2, 3))
Yields 8 (3+2+3).
=max(range) =max(num, num) =max(num, num, num) =max(num, num, num, num)
Returns the largest number from range.
Returns 0 for empty ranges (or ranges with no numbers).
Alternatively, returns the largest from among nums.
A column range.
Any number of numbers.
=min(range) =max(num, num) =max(num, num, num) =max(num, num, num, num)
Returns the smallest number from range.
Returns 0 for empty ranges (or ranges with no numbers).
Alternatively, returns the smallest from among nums.
A column range.
Any number of numbers.
=mod(score)
Returns the ability score modifier corresponding to the ability score number.
This can also be applied to a range.
An ability score number.
=num(maybe_number) =num(maybe_number, default)
If maybe_number is a number, then maybe_number is returned. If it is a string that starts with a number (like "2 people"), then the numeric portion of the string is converted to a number. Otherwise, if default is given, default is returned. Finally, 0 is returned if none of the above conditions are met.
The first argument can also be a range/array.
An expression that might be a number.
An expression to be used if maybe_number is not a number.
=pow(base, exponent)
Raises the base to the power of exponent.
This can also be applied to a range.
The number to be exponentiated.
The power to raise base to.
=prod(range)
Returns the product of the numeric values in range, ignoring strings and blanks.
Returns 1 for an empty range.
A column range.
=round(number)
Rounds number to the nearest integral value, rounding .5 away from zero.
This can also be applied to a range.
The number to round
=sqrt(number)
Returns the non-negative square root.
This can also be applied to a range.
The number to sqrt.
=sum(range)
Returns the sum of the numeric values in range, ignoring strings and blanks.
Returns 0 for an empty range.
A column range.
=tlu(needle, haystack, values) =tlu(needle, haystack, values, default)
Looks up needle in haystack, return the corresponding value from values.
If default is given, that value is returned if needle is not found in haystack. Otherwise, an error is returned.
Needle can also be a range. In which case, a computed range is returned instead of a single value
The value to lookup
A range in which to search for needle.
A range in which to yield the corresponding value from.
If needle is not found, this value is returned.
=tlu('Plate', Items, Weights)
=trunc(number)
Removes the part of the number after the decimal point.
The number.
=try(fallible, default)
If the first argument does not fail, returns the first argument.
Otherwise, returns the second argument.
An expression that could result in an error.
The expression to use if fallible fails.
=try(call('doesnotexist', 1), 3)
The above yields 3.
You can define your own functions.
You do this by creating a special sheet that is marked as a function. You also give the function a name. Note that you can't use the name of a builtin function for one of your functions.
Some of the cells of that sheet are the inputs to the function and one of the cells is the output.
You can then call it like one of the builtin functions.
| A | B | |
|---|---|---|
| 1 | ||
| 2 | =a1*2 | =b1+2 |
| 3 | =a2/b2 |
In this example, the input cells are green and the output cell is blue.
Given the above is named MyFunc, you can then call it like so:
=MyFunc(1, 2)
which would yield 0.5.
Start at row 2 so you can put this formula in row 1. Or you can leave the 2 off to sum the whole column.
=sum(a2:)
The num(b, 1) will either return the value in b or 1, so blanks will be counted as 1.
=sum(a * num(b, 1))
=sum(tlu(a, [othersheet, a], [othersheet, b]))