Entering Formulas

ClearFactr has two modes for entering formulas: directly in the cell, and via the Formula Toolbar. The latter is more powerful and works very similarly to that of legacy spreadsheet applications.

The Formula Toolbar

null

The Formula Toolbar allows for “point-and-click” and “type-to-completion” editing, based on the attributes. When you click on a cell in the grid, its description will appear in the left display field of the Toolbar, and its data or formula will appear in the input field on the right. Consider the picture below:

null

You can see how ClearFactr automatically displays a cell’s formula in “Natural Language.” Click on the “Edit” button, or on the formula input field, to edit the contents of the cell. In editing mode, the attributes in the formula will be displayed by their abbreviations, and ClearFactr will even make suggestions when you start typing the name of an attribute or function:

null

When in editing mode, “pointing and clicking” on the grid will add the clicked cells to the formula. By default, cells are added with Relative referencing. Relative referencing will reference cells by their position relative to the current cell. For example, “accumulatedRevenue(-1)” will always reference the Accumulated Revenue cell immediately to the left of the current cell. On the contrary, Absolute referencing will reference cells by their actual position in time, and thus wont change if the current cell changes. For example, “accumulatedRevenue(q1_2015)” will always point the Accumulated Revenue @ Q1 2015 cell, no matter what the current cell is. Use the Cycle button to cycle through the four combinations of row and column referencing for a given cell. You can also use familiar "$" syntax to lock a row reference. Click the “Save” button to save changes to the formula, or click the “Cancel” button to discard the changes.

Direct Cell Editing

You are free to type directly in any cell. If the cell’s content comes from a formula, that formula will be displayed. Click Esc to cancel, or Enter to save the cell.

Deleting Cells

Deleting a cell that isn’t controlling anything is harmless. Deleting a cell that drives other cells will present you with a warning box that shows you what cells are about to be affected. You can disable this warning via your Preferences.

Referring to Cells within Formulas

ClearFactr’s “Self-Describing Spreadsheet” is driven by the row and column labels — the attribute and timepoint names. They become the basis by which you create formulas and build the relationships between the various aspects of your Plan.

With these labels in place, every cell can be thought of as the value of some thing at some point in time.

The naming scheme for cells stems from that observation.

Every cell knows what its timepoint is, so if you just refer to an abbreviation by itself ClearFactr assumes you mean the value of that attribute at the same point in time.

For example, say you have three attributes, each with a short abbreviation:

Attribute NameAbbreviation
Number of SalespeoplenumSP
Average Sales Per PersonavgSales
Total SalestotalSales

In the Total Sales for December 2013 cell, you can have a formula that simply reads:

numSP * avgSales

In the formula bar, if you inspect that cell in Natural Language terms, you’ll see

Number of Salespeople * Average Sales Per Person

For editing clarity and simplicity, formulas are always created in abbreviations mode.

What if I want to refer to some other timepoint?

Of course not every value at a given moment in time is a function of only things from that time. If you want to refer to another time, first decide if the relationship is relative or fixed.

Relative relations take into account the timepoint of the current cell. For example, Accumulated Profit might be represented as the Profit from the current timepoint plus the Accumulated Profit from the prior one.

Fixed (or Absolute) relationships are frozen in time. For example, “change in sales since April 2014”.

Relative termsSpecify the abbreviation name plus a number in parenthesis representing the number of timepoints relative to the current one:
totSales(-1)In a monthly plan, that would be the total sales from the prior month.
totSales(-3)In a monthly plan, that would be the total sales from three months ago.
Be careful when using positive numbers – they imply you’re looking into the future!
Fixed termsSpecify the abbreviation name plus a Timepoint description in parenthesis.
totSales(Apr 2013)Means the Total Sales in April 2013.