Log in | Register



Row and Column level Formulae

Resolver One allows formulae to be applied to entire rows or columns. This is distinct from, and often superior to, applying many copies of a formula to each cell in a row.

Row or Column level formula use the same syntax as cell formulae, with the addition that references to cells in other rows or columns can use '_' as a dummy ordinate, best explained by example, below:

Note that formulae can also be applied to a whole worksheet - see Worksheet Level Formulae in Resolver One Feature Overview. The syntax and UI for sheet level formulae are not similar to those for row and column level formulae. Hopefully these features will be more cohesive in a future release.

Column Level Formula Example

If column A is filled with numbers, then column B could be filled with the square of each adjacent number by selecting column B, and entering the following column-level formula in the formula bar:

= A_^2

Note the reference to column 'A', using '_' as a dummy row ordinate. This means that the value of each cell in column C should be filled by evaluating this formula using the value from the corresponding cell in column A.

An asterisk appears in the column header for column C, to remind you that these values are coming from the column level formula.

Row Level Formula Example

Likewise, row level formulae use '_' as a dummy column ordinate. For example, select all of row 6, and enter the formula in the formula bar:

= _5^3

This would fill row 6 with the values from row 5, raised to the third power.

Using headers

Row and column level formulae become even more powerful when used with Header Rows and Columns. For example, let's say that you have a header row, where you have given column A the header "Price", B "Quantity" and C "Total". You could just give column C the formula:

= A_ * B_

but that could be hard to interpret, especially as the spreadsheet grew larger. Instead, you can do this:

= #Price#_ * #Quantity#_

and get the same effect. This has the added advantage that if you move the price and quantity headers to different columns later, your formulae will not need to be changed.

Advantages

Using row or column level formulae is often superior to filling each cell in a column with a copy of the same formula, as is traditionally achieved using drag filling or copy and paste. These traditional methodologies are prone to error if the range of the input data changes. eg. In the column-level formula example above, populating more cells in column A would require a corresponding change to column B, which is easy to overlook or apply incorrectly with large, complex worksheets, especially ones which pull in variable-sized areas of data from external sources.

Additionally, using drag-fill or cut-and-paste methods are error prone if the formula in one cell should be inadvertently modified. In the general case, it is impossible to verify that this has not happened without clicking on, and examining every cell in the column. With a column or row level formula, there is only one copy of the formula, so you can be certain it is always applied correctly to every single cell in the row or column.

Comments