Formula: Referencing same-field cells

alexhoward · September 29, 2011 4:16PM

Within a formula, having a way to reference cells in the same field in a continuous fashion. In other words, a formula where I can, say, add row 1 to the formula in row 2. Then add row 2 to the formula in row 3, etc., etc. You can do this in Excel just by referencing the cell you want and dragging the formula down the column. I’m attaching an Excel example to help clarify what I’m talking about.

Also, thank you for the SCRIPT idea. Could you please post an example formula with a script that could help get me started writing one?

Thanks

steve · September 30, 2011 6:48AM

Here's an example. Note that SCRIPT is only available in full installations of Omniscope 2.6.

SCRIPT(`

  var x = 0;
  var y = 1;

  for (var i=1; i<count; i++) {
    var n = x+y;
    x = y;
    y = n;
  }

  // Result in this value:
  x;

`, "count", CURRENTROW)

I've defined one input variable "count" which is set to the current row number. I then iterate through all preceding rows to evaluate that row's value. I can't access previous row values directly, so I have to do it this way.

See attached file.

steve · September 30, 2011 7:08AM

Note also the use of the back-quote (2.6+). This is a general point unrelated to your original post.

In formulas you can use double quotes ("), single quotes (') and back-quotes (`) when specifying literal text. The difference being as follows.

In all 3 examples we are specifying the resulting literal text value:

Some text with backslashes \ and "other" 'quote' `characters` inside

If you single-quote some text, you must precede any single-quotes inside your text with a backslash (\). This is called "escaping". And if you want a single backslash, you have to double it up. You can use double-quotes inside single-quotes freely.
For example:

'Some text with backslashes \\ and "other" \'quote\' `characters` inside'
The equivalent applies for double-quotes.
For example:

"Some text with backslashes \\ and \"other\" 'quote' `characters` inside"
Back-quotes were introduced from 2.6 for use with scripts and regular expressions, where the above characters appear frequently (e.g. regular expressions with doubled-up backslashes are completely unreadable), but can be used anywhere to enclose text literals.
These have different rules. You can use any character inside back-quotes without any special behaviour, except for a back-quote itself, which must be doubled-up.
For example:

`Some text with backslashes \ and "other" 'quote' ``characters`` inside`

steve · November 3, 2011 12:15PM

Note: in 2.7, scripting support has been enhanced. Alpha partners, see here:
http://forums.visokio.com/discussion/1073/scripting-in-formulae-2.7-enhancements

steve · July 16, 2012 12:17PM

Please note that scripting functionality is now documented here:
http://www.visokio.com/kb/scripting

Welcome!

Categories

Ideas Parade

Tagged