Working with Functions

Built-in Functions

SpreadSheet can work with the Excel functions. Below you will find the full list of functions with detailed descriptions.

Math functions

  • ABS - returns the absolute value of a number. The absolute value of a number is the number without its sign.
  • AVERAGE - returns the average (arithmetic mean) of the arguments;
    • if a range or a cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • CONCATENATE - joins values from a range of cells into a string.
  • COUNT - counts the number of cells that contain numbers, and counts numbers within the list of arguments;
    • empty cells, logical values, text, or error values in the array or reference are not counted.
  • COUNTA - counts the number of cells that are not empty in a range, zero is excluded.
  • COUNTBLANK - counts empty cells in a specified range of cells;
    • cells with zero values are not counted.
  • EVEN - returns number rounded up to the nearest even integer.
  • INT - rounds a number down to the nearest integer.
  • MAX - returns the largest value in a set of values;
    • empty cells, logical values, or text in the array or reference are ignored. If the arguments contain no numbers, MAX returns 0 (zero).
  • MIN - returns the smallest number in a set of values.
    • empty cells, logical values, or text in the array or reference are ignored. If the arguments contain no numbers, MIN returns 0 (zero).
  • ODD - returns number rounded up to the nearest odd integer.
  • PI - returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.
  • POWER - returns the result of a number raised to a power;
    • works with real numbers.
  • PRODUCT - multiplies all the numbers given as arguments and returns the product;
    • only numbers in the array or reference are multiplied. Empty cells, logical values, and text in the array or reference are ignored.
  • QUOTIENT -returns the integer portion of a division. Use this function when you want to discard the remainder of a division;
    • works with real numbers.
  • RAND - returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
  • ROUND - rounds a number to a specified number of digits.
  • ROUNDDOWN - rounds a number down to a specified number of digits.
  • ROUNDUP - rounds a number up to a specified number of digits.
  • SQRT - returns a positive square root.
  • STDEVP - calculates standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value (the mean).
    • empty cells, logical values, text, or error values in the array or reference are ignored.
  • SUM - adds values;
    • empty cells, logical values like TRUE, or text are ignored.
  • SUMPRODUCT - multiplies corresponding components in the given arrays, and returns the sum of those products.
    • for valid products only numbers are multiplied. Empty cells, logical values, and text are ignored. Treats array entries that are not numeric as if they were zeros.
  • SUMSQ - returns the sum of the squares of the arguments;
    • empty cells, logical values, text, or error values in the array or reference are ignored.
  • TRUNC - truncates a number to an integer by removing the fractional part of the number.
  • VARP - calculates variance based on the entire population;
    • empty cells, logical values, text, or error values in the array or reference are ignored.

String functions

  • CONCATENATE (A1,B2, C2:D4) - joins several text items into one text item.
  • LEFT (text, count) - returns the first character or characters (leftmost) in a text string, based on the number of characters you specify.
  • LEN (text) - returns the number of characters in a text string.
  • LOWER (text) - converts all uppercase letters in a text string to lowercase.
  • MID (text, start, count) - returns a specific number of characters from a text string starting at the position you specify.
  • PROPER (text) - capitalizes the first letter in each word of a text value.
  • RIGHT (text, count) - returns the last character or characters (rightmost) in a text string, based on the number of characters you specify.
  • TRIM (text) - removes spaces from text.
  • UPPER (text) - converts text to uppercase.

Date functions

  • DATE (year, month, day) - creates a valid date from individual year, month, and day components.
  • TIME (hour, minute, second) - allows to create a time with individual hour, minute, and second components.
  • SECOND (date) - returns a second as a number between 0 to 59 from a given date.
  • MINUTE (date) - returns a minute as a number between 0 to 59 from a given date.
  • HOUR (date) - returns an hour as a number between 0 to 23 from a given date.
  • DAY (date) - returns the day of the month as a number between 1 to 31 from a given date.
  • MONTH (date) - extracts the month from a given date as number between 1 to 12.
  • YEAR (date) - returns the year component of a date as a 4-digit number.
  • NOW () - returns the current date and time, updated continuously when a worksheet is changed or opened
  • DATEDIF (start, end, unit) - returns the difference between two date values in years, months, or days.

Other functions

  • IMAGE (url) - displays an image the URL of which is set as a parameter of the function
  • SPARKLINE (range, type, color1, color2) - draws a sparkline according to the specified parameters
  • HYPERLINK (url, text) - creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
    • link URL can be a text string or a reference to a cell that contains the link
  • IF (smth is true, then do smth, otherwise do smth else) - specifies a logical test to perform
    (e.g. IF(A1 < 10, "Yes" , "No"))
    • the function supports the following operators <, >, <=, >=, <>, =

Related sample:  Math

Creating a Custom Function

If you need a function that isn't listed above, you can create your own function and use it in the spreadsheet.

To add a new function, use the registerMathMethod method. You should pass two parameters to it:

  • the name of the function. It should be defined in uppercase
  • the function handler. It takes all the necessary parameters and contains the function's logic. The handler receives either of the following notions as a parameter:
    • a single value (a reference to a cell)
    • an array of values (a reference to a range of cells)

For example, let's create a function "FIRST" that will return the value of the first cell in a range. So, we should add our function to the list as follows:

$$("ss").registerMathMethod("FIRST", function(range){
    return range[0];
});

And we can use the new method as:

=FIRST(H3:H5);

In the above example we have passed one parameter to the handler, the cell range. The "FIRST" function returns the value of the first cell.

Back to top
If you have not checked yet, be sure to visit site of our main product Webix popular javascript framework and page of javascript spreadsheet library product.