formula-information v0.0.2
#+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