Formulas > Simple Formulas

Simple formula expressions are designed to work exclusively with tables. Simple formula expressions begin with 1 of 3 bases; they are “left()”, “right()” and “prior()”. The example table below will be used when describing simple expressions. The blue numbers and letters are for cell identification only. WhisperReporter does not automatically reference cells like “A1” or “C3”, since it is not a spreadsheet program.

FormulaTableExample

Single click on a formula expression’s answer to return to the expression window; the expression can then be modified or deleted.

Left(xx)

An expression with the left base adds up cells to the left of the cell containing the formula expression. Let’s say that in cell B7 we put the expression left(1,2,3,4,5), this expression is adding up the first, second, third, fourth and fifth cells to the left of B7. The result is 5+30+50+20+10 = 115. You can leave out a cell by omitting the number in the expression. For example, in B7, left(1,2,3,4) will omit the fifth cell from B7 which is January. The result in B7 would be 105. To subtract, multiply or divide we need to use a left base for each cell. For example, to subtract C5 (70) from C6 (80) we would put this expression in C7: left(1)–left(2), the result being 10. The same expression also works reversed: left(2)–left(1), the result here would be -10.

Right(xx)

An expression with the right base adds up cells to the right of the cell containing the expression. The right base works exactly like the left base, the example expressions in the left(XX) section above will give you an idea of how to use the right base.

Special note about adding and removing columns
When using the left() or right() base, expressions will not automatically update when a column is added or removed. The expression will need to be modified to take these changes into account. For example, let’s say we added a “June” column and there were 10 spoons, 10 forks, and 10 knives sold in June. If we leave the current expression left(1,2,3,4,5) in B7 (technically now B8), we would be adding June, May, April, March and February. January would be left out because it is now six cells to the left of the expression. The expression would need to be modified to left(1,2,3,4,5,6) to include January.

Prior(xx)

A formula expression with the prior base adds up cells above the cell containing the expression. If, in E2, we put the expression prior(1,2,3), this would add the first, second and third cells above E2 (5, 15, 10). E2 would display 30 as the answer. We can omit cells in a column by omitting them on the expression. The expression prior(2,3) in E2 would omit the “Knives Sold” column; the answer in E2 would then be 25.

Special note about adding and removing rows
When using the prior() base, the expressions will not automatically update if rows are added or removed. The expression will need to be modified to take these changes into account. For example, let’s say you added this row to the table under the “Knives Sold” row:

If the original expression, prior(1,2,3), is left unchanged in E2 (technically F2 now) the expression would add cups + knives + forks. Spoons would not be added because they are 4 cells from the expression. The answer would be 80 and not automatically become 90. The expression would need to be changed to prior(1,2,3,4) to include spoons and get the correct answer of 90. Click on the “80” to access the expression window.

If a row is removed, then one row above the end of the expression would be included. If the “Knives Sold” row is removed, than the existing expression in E2 (now D2) prior(1,2,3) would add forks, spoons and “January”. Since there is no number in the cell containing “January”, zero is added. The answer would correctly be 25, but only because text equals zero. If we had used “1” instead of the word “January”, then the answer would have been an incorrect 26.