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;
- 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;
- 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