Visokio website     Downloads     Video tutorials     KnowledgeBase  
Formula: Referencing same-field cells - Visokio Forums
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
    Attachments
    Fibonacci.xlsx 9K
  • 4 Comments
  •     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.
    Attachments
    Fibonacci.iok 6K
  •     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!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership