Starting from v4.0, the package of dhtmlxSpreadSheet includes a set of predefined formulas that can be used for different types of calculations of strings and numbers. The formulas are compatible with Excel and Google Sheets.

Related sample: Formulas cheat sheet - DHTMLX Spreadsheet

Here's a list of all the available functions with detailed descriptions.

ABS |
Returns the absolute value of a number. The absolute value of a number is always positive. |

AVERAGE |
Calculates the average (arithmetic mean) of a group of numbers. The function ignores logical values, empty cells and cells that contain text. However, cells with the value zero are included. |

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 contain numbers, text, logical values, error values, and empty text (""); cells with zero values are excluded. The function does not count empty cells. |

COUNTBLANK |
Returns the number of empty cells from a specified range. Cells with zero values are not counted. |

EVEN |
Returns a number rounded up to the nearest even integer. |

INT |
Returns a number rounded down to the nearest integer. |

MAX |
Returns the largest value in a set of values. The function ignores empty cells, the logical values TRUE and FALSE, and text values. 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 a 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 given 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 result of integer division without the remainder. Works with real numbers. |

RAND |
Returns a random number that is greater than or equal to 0 and less than 1. Returns a new random number each time your spreadsheet recalculates. |

ROUND |
Returns a number rounded to a specified number of digits. |

ROUNDDOWN |
Returns a number rounded down to a specified number of digits. |

ROUNDUP |
Returns a number rounded up to a specified number of digits. |

SQRT |
Returns a positive square root of a number. |

STDEVP |
Calculates standard deviation based on the entire population of numbers. 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 |
Returns the sum of supplied values. Empty cells, logical values like TRUE, or text are ignored. |

SUMPRODUCT |
Multiplies range of cells or arrays and returns the sum of 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 |
Returns the variance of a population based on an entire population of numbers. Empty cells, logical values, text, or error values in the array or reference are ignored. |

CONCATENATE(A1,"",B2:C3) |
Joins two or more text strings into one string. |

LEFT(text, count) |
Returns the first character or characters in a text string, based on the number of characters you specify. |

LEN(text) |
Returns the length of the specified string. |

LOWER(text) |
Converts all letters in the specified string to lowercase. |

MID(text, start, count) |
Returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify. |

PROPER(text) |
Sets the first character in each word to uppercase and converts all other characters to lowercase. |

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 all spaces from text except for single spaces between words. |

UPPER(text) |
Converts text to uppercase. |

Starting with v4.1, you can get the formula applied to a cell via the getFormula() method. The method takes the id of the cell as a parameter:

var formula = spreadsheet.getFormula("B2");
// -> "ABS(C2)"

Back to top