0.0.2 • Published 9 years ago

formula-information v0.0.2

Weekly downloads
-
License
MIT
Repository
-
Last release
9 years ago

#+TITLE: Information

  • CELL

    Returns information about a cell.

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet();

    assert( wb.run(ws, 'CELL("row", A1) = 1') ); assert( wb.run(ws, 'CELL("col", A1) = 1') );

    assert( wb.run(ws, 'CELL("row", A2) = 2') ); assert( wb.run(ws, 'CELL("col", B1) = 2') );

    #+END_SRC

  • DEPENDENTS

    Returns the list of dependents for a given cell.

    #+BEGIN_SRC js var wb = new workbook(); var sheet = wb.sheet();

    wb.set(sheet, { A1: "=SUM(A2:A3)", A2: 50, A3: 50, A4: "=A2 + A3" });

    var dependents = wb.run(sheet, "DEPENDENTS(A2)"); assert( dependents.length == 2, "Should have 1 dependent");

    dependents = wb.run(sheet, "DEPENDENTS(A3)"); assert( dependents.length == 2, "Should have 1 dependent");

    assert( "Sheet1!A1" === dependents0, "Should be equal"); assert( "Sheet1!A4" === dependents1, "Should be equal"); #+END_SRC

  • TODO INFO

    Returns information about the operating environment running your workbook.

    |-----------+-----------------------------------------------------+-----------| | Text Type | Returns | Supported | |-----------+-----------------------------------------------------+-----------| | directory | Location where spreadsheet is stored | | | numfile | Count of active worksheets in all open workbooks | | | origin | #NA! | | | osversion | Returns browser or container version | | | recalc | Current recalculation mode; "Automatic" or "Manual" | | | release | Version number; as text | | | system | Return browser or container name | | |-----------+-----------------------------------------------------+-----------|

    #+BEGIN_SRC js // TBD: Make this work! var wb = new workbook(); var ws = wb.sheet(); ws.run('INFO("directory")'); ws.run('INFO("numfile")'); ws.run('INFO("origin")'); ws.run('INFO("")'); ws.run('INFO("directory")'); ws.run('INFO("directory")'); #+END_SRC

  • ISARRAY

    Tests if the value is an array.

    #+BEGIN_EXAMPLE ISARRAY({1,2,3}) = TRUE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISBLANK("FOO") = FALSE #+END_EXAMPLE

  • ISBLANK

    Tests if the value is blank (empty).

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet();

    wb.set(0, "A1", null); wb.set(0, "A2", undefined); wb.set(0, "A3", ""); wb.set(0, "A4", "Hello");

    assert(wb.run(0, 'ISBLANK(A1) = TRUE'), "A1 should be blank"); assert(wb.run(0, 'ISBLANK(A2) = TRUE'), "A2 should be blank"); assert(wb.run(0, 'ISBLANK(A3) = FALSE'), "A3 should not be blank"); // empty string is not blank assert(wb.run(0, 'ISBLANK(A4) = FALSE'), "A4 should not be blank"); #+END_SRC

  • TODO ISEMAIL

  • ISERR

    Test for any error but #N/A.

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet(); wb.set(ws, "A1", workbook.errors.na); wb.set(ws, "A2", workbook.errors.div0); wb.set(ws, "A3", Number.POSITIVE_INFINITY);

    assert( wb.run(ws, "ISERR(A1) = FALSE"), "A1 should not be error" );

    assert( wb.run(ws, "ISERR(A2) = TRUE"), "A2 should be error" );

    assert( wb.run(ws, "ISERR(A3) = TRUE"), "A3 should be error" );

    #+END_SRC

  • ISERROR

    Test for error.

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet(); wb.set(ws, "A1", workbook.errors.na); wb.set(ws, "A2", workbook.errors.div0); wb.set(ws, "A3", Number.POSITIVE_INFINITY);

    assert( wb.run(ws, "ISERROR(A1) = TRUE"), "A1 should be error" );

    assert( wb.run(ws, "ISERROR(A2) = TRUE"), "A2 should be error" );

    assert( wb.run(ws, "ISERROR(A3) = TRUE"), "A3 should be error" );

    #+END_SRC

  • ISEVEN

    Test for even number.

    #+BEGIN_SRC js assert( workbook.run('ISEVEN(1) = FALSE'), "should be true"); assert( workbook.run('ISEVEN(2) = TRUE'), "should be true"); #+END_SRC

    Test values in a worksheet.

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet(); wb.set(ws, "A1", 1) wb.set(ws, "A2", 2) assert( wb.run(ws, "ISEVEN(A1) = FALSE"), "should be true" ); assert( wb.run(0, "ISEVEN(A2) = TRUE"), "should be true" ); #+END_SRC

  • ISFORMULA Test if cell has formula. #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet(); wb.set(ws, "A1", 2); wb.set(ws, "A2", "=A1^8"); assert( wb.run(0, "ISFORMULA(A1) = FALSE") ); assert( wb.run(0, "ISFORMULA(A2) = TRUE") ); #+END_SRC
  • ISLOGICAL

    Test for TRUE or FALSE

    #+BEGIN_EXAMPLE ISLOGICAL(1) = FALSE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISLOGICAL("HELLO") = FALSE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISLOGICAL(TRUE) = TRUE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISLOGICAL(FALSE) = TRUE #+END_EXAMPLE

  • ISNA

    Test for NA error.

    #+BEGIN_EXAMPLE ISNA("foo") = FALSE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISNA(NA()) = TRUE #+END_EXAMPLE

  • ISNONTEXT

    Test for non text

    #+BEGIN_EXAMPLE ISNONTEXT("foo") = FALSE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISNONTEXT(NA()) = TRUE #+END_EXAMPLE

  • ISNUMBER

    Returns TRUE if the value_to_check is a number.

    #+BEGIN_EXAMPLE ISNUMBER("FOO") = FALSE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISNUMBER(1) #+END_EXAMPLE

  • ISODD

    Returns true if the value is odd.

    #+BEGIN_EXAMPLE ISODD(1) = TRUE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISODD(2) = FALSE #+END_EXAMPLE

  • ISTEXT

    Returns TRUE if the value is text.

    #+BEGIN_EXAMPLE ISTEXT("foo") = TRUE #+END_EXAMPLE

    #+BEGIN_EXAMPLE ISTEXT(2) = FALSE #+END_EXAMPLE

  • ISRANGE

    Return TRUE when the value is a range or a cell reference.

    #+BEGIN_SRC js var wb = new workbook(); var ws = wb.sheet();

    assert( wb.run(ws, "ISRANGE(A1:A3)"), "A1:A3 is not a range" ); assert( wb.run(ws, 'ISRANGE(OFFSET(A1,0,0,2,2))'), "OFFSET function not returning range" );

    #+END_SRC

  • ISREF

    Return TRUE when the value is a range or a cell reference.

    #+BEGIN_SRC js wb = new workbook(); ws = wb.sheet();

    assert( ws.run('ISREF(A1) = TRUE') );
    assert( ws.run('ISREF("FOO") = FALSE') );
       

    #+END_SRC

  • TODO ISURL

  • N Returns a value converted to a number. #+BEGIN_EXAMPLE N("5.2") = 0 #+END_EXAMPLE #+BEGIN_EXAMPLE N(NA()) = NA() #+END_EXAMPLE #+BEGIN_EXAMPLE N(TRUE) = 1 #+END_EXAMPLE
  • NA Returns the error "#NA!" #+BEGIN_SRC js assert( workbook.run( "NA()" ) === workbook.errors.na, "should be true" ); #+END_SRC
  • PRECEDENTS

    Return the list of precedents for a given cell.

    #+BEGIN_SRC js var wb = new workbook(); var sheet = wb.sheet();

    sheet.set({ A1: "=SUM(A2:A3)", A2: 50, A3: 50, A4: "=A2+Sheet1!A3" });

    var precedents = sheet.run("PRECEDENTS(A1)"); assert( precedents.length == 1, "Should have 1 precedents");

    assert( precedents0.type === "range", "Type should be range"); assert( workbook.fn.ISRANGE(precedents0.range), "Should be range object");

    precedents = sheet.run("PRECEDENTS(A4)"); assert( precedents.length == 2, "Should have 2 precedents");

    assert( precedents0.type === "cell", "Type should be cell"); assert( precedents0.subtype === "local", "Subtype should be local"); assert( precedents0.addr === "A2", "Addr should be A2");

    assert( precedents1.type === "cell", "Type should be cell"); assert( precedents1.subtype === "remote", "Subtype should be local"); assert( precedents1.sheetName === "Sheet1", "Sheet name should be Sheet1"); assert( precedents1.addr === "A3", "Addr should be A3");

    #+END_SRC

  • SHEET

    Returns a named worksheet.

    #+BEGIN_SRC js var ws = workbook.Current.sheet({ name: "TipCalculator" }); assert( ws.run('=SHEET("TipCalculator")') ); #+END_SRC

  • SHEETS Returns the number of sheets in the workbook. #+BEGIN_SRC js var ws = workbook.Current.sheet({ name: "SheetCount" }); assert( ws.run('SHEETS()') === Object.keys(workbook.Current.worksheets).length, "SHEETS returns unexpected value" ); #+END_SRC
  • TYPE Returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell. #+BEGIN_SRC js assert( workbook.run('=TYPE(1) = 1'), "number should be 1" ); assert( workbook.run('=TYPE("Foo") = 2'), "string should be 2" ); assert( workbook.run('=TYPE(TRUE) = 4'), "boolean should be 4" ); assert( workbook.run('=TYPE(NA()) = 16'), "error should be 16" ); #+END_SRC