+---+---+---+---+---+ | t | a | l | l | y | +---+---+---+---+---+
Expressions give tally a bit more power than just entering data into cells. Expressions let you create relationships between data and calculate changes/results.
Expressions in tally do not follow the Excel style expression methodology. Instead, expressions use a stack for execution and use postfix notation. This is similar to nimf, the concatenative language written by the author of tally; it is also similar to forth and many other concatenative programming languages.
tally uses postfix notation, sometimes called reverse polish notation. In this form of notation the operator comes after the operand(s). Most people are used to doing maths utilizing pemdas (parenthesis, exponents, multiplication, division, addition, subtraction). This type of notation requires reading ahead and backtracking to make sure everything is done in the correct order based on the precedence set forth by pemdas. In postfix notation you simply read from left to right and perform the actions.
Here is a simple math problem in the notation most folks are familiar with:
2 + 5 * 3
To write the same problem in postfix notation you would write:
5 3 * 2 +
tally has a number of operators/functions available to ease calculations, including all of the basic math operators you are likely used to.
Cell references can appear in expressions as a way to get the value of another cell and include it in the current expression. Cell references takt he form [COLUMN][ROW], for example B6. These references are dynamic. If the value in the referenced cell changes, the expression recalculates to take the changes into account.
When an expression gets yanked (copied) from one cell to another and pasted with the P key all references will be updated dynamically. So, if an expression referenced A1 and that expression was pasted into the cell directly to the right of its current location the A1 reference would now reference B1. This lets you write one expression and apply it lots of times to different values in the sheet. However, you may find that you want this dynamic pasting to occure, but only on one axis (ie. only on the row or only on the column). This can be achieved by using a $ character before the value you want to lock. So in the previous example if A1 had actually been $A1, then the result after the paste would also be $A1. To look a row you put the $ before the number value like so: A$1. It is possible to use both: $A$1. This lets you lock this value entirely while allowing the rest of the expression to potentially update dynamically.
tally can accept ranges of cells for operation. However, tally currently only supports summing the numerical values of those cells. The syntax is A1:B3+. The + at the end designates summing the range. Eventually there may be other range operations available. Locking rows and columns can be used in ranges. In the example, A1:B3+, the values of A1, A2, A3, B1, B2, and B3 will all get added together. Using a range for summation is much easier to read and reason about than A1 A2 A3 B1 B2 B3 + + + + +.
For purposes of range summation any cell within the range that contains text will be treated as 0, the same goes for any empty cell. This allows you to cleanly use ranges anywhere without worrying about mixing text and numerical values.
The range operation treats the result like any other number: it adds it to the top of the cells number stack, wher eit can be operated on by other values in the expression. So, something like the following is perfectly fine:
"$" A2:B5+ / 100 2 ROUND . SPC "- TOTAL"
The above would result in something along the lines of: $52.11 - TOTAL. You'll see why in the upcoming sections. The main takeaway here is that ranges can be a part of your expression and not just a standalone feature.
Expressions can combine text, numbers, and reference values into a form of code that produces a result, often a dynamic one that can respond to changes elsewhere on your sheet.
A1 A2 +
In the above example the values of A1 and A2 get added together and the result becomes the value of the cell. Note that if either A1 or A2 is a text value, rather than a number, this will result in #Err: Stack underflow. We will cover mixing text and numbers further into this guide.
Basic math operators exist for +, -, *, and /. Additional operators that work with numbers are: ROUND, MAX, MIN, FLOOR, CEIL, and POW.
There are also functions to do a few basic things:
A1 2 ROUND
The above would round the numerical value at A1 to two decimal places.
A1 A2 MAX
The above would find the greater of the two values that come before MAX. MIN is also available.
You can combine cell references and hard coded numbers as well:
A1 2 * 10.5 MIN 1 ROUND
In the above, lets assume A1 is currently 3.125. If so, then A1 gets multiplied by 2, resulting in 6.25. Then the minimum value between 6.25 and 10.5 is found, which is 6.25. This then gets rounded to one decimal place for the final value of 6.3.
Text is pretty easy to work with. No operators are required in most cases. Since tally splits expressions on whitespace, consecutive whitespace in strings should be entered as \_. So, a cell with the value "Hello" that is intended to be combined with another cell and result in a space in between may need to be written as "Hello\_".
To then combine them, you could use the following expression (assuming A2 to be something like "world"):
A1 A2
Or, since text can be entered into expression manually, the following would also work:
A1 "world"
That is pretty good, but that space character (\_) still feels awkward. To make things easier there is a function that will add a space between two text values:
A1 SPC A2
Assuming A1 to be "Hello" and A2 to be "world", this would create the cell value Hello world. Without SPC the words would be shoved together and read as Helloworld.
To concatenate a numerical and text value, you can enter the values as if they were both strings, but it may not work out like you would think:
21.75 "Hello"
The above results in Hello21.75. If you want the 21.75 to appear before "Hello", you need to use a special function: .. This lets tally know that you would like the number on the top of the stack to be appended to the current string value and dropped from the stack. So the above could be rewritten as:
21.72 . SPC "Hello"
With the result being 21.72 Hello. Of course, either the number value or the text value could have been a cell reference, such as B25 of the like.
You may be asking yourself, why do we need .? The answer is that it allows you to have numbers before and after text and choose when to put numbers into the text value. This gives a fine grained control of both the number stack as well as the string buffer.
For a more complicated example, we could do something like the following:
23 5 + . "hello" 3 1 -
This produces the result: 28hello2. If you remove the . that comes after the plus, you would get the output: hello228. That may seem strange. Why is that happening? It is happening for the same reason you saw a 2 at the end in the first result: at the end of the expression, the stack is flushed and added to the string from top of stack down. So since 2 was the last number on the stack it is the first off. Then 28 gets added to the end.
There are a few words that affect the stack, but do not necessarily modify the output in a transformative way like + or MIN do, for example. The next section will cover these. The important thing to take away from the above paragraph is that . can help you control where numbers go, and any numbers left on the stack at the end of an expression that involves text will be flushed to the text buffer (this is not true of purely numerical expressions, where the cell value is always just represented by the top of the stack).
The number stack can be manipulated in a number of ways. You have seen mathematical operators and a few functions (MIN, ROUND, MAX, .). There are a few more options for dealing with numbers on the stack:
DUP will copy the number on the top of the stack and place the copy on top. So if the stack starts empty and you enter 5 DUP, the stack will contain 5 and then another 5, for a total of 2 values on the stack.
DROP will remove the top value on the stack and throw it away.
CLEAR will wipe out all values from the stack. This, in theory, could be useful to prevent a stack flush at the end of a field containing numbers and text. However, be wary of doing so as it likely indicates that some refactoring could prevent the need to use it.
SWAP will swap the position of the top two values on the stack.
OVER will copy the number underneath the top value on the stack and place the copy on the top of the stack without removing any numbers from the stack..
If there is a problem in your expression, the cell will show #Err, likely followed by a message about the nature of the problem. If you try to reference a stack value that doesn't exist, for example... or a cell that does not exist on the sheet.
Some functions require certain things to be in place on the stack. All of the math operators, for example, require at least two numbers to be on the stack (the two will be replaced by the result of the operation). ., DROP, and DUP only require one item on the number stack. CLEAR and SPC do not require any numbers to be on the stack. If the minimum number of items on the stack is not met, an error will be the result.
For a full listing of the operators/functions available in tally read on to the next section. Otherwise, this should be enough to get you on the road to making expressive spreadsheets in your terminal with concatenative expressions.