Products

JSS Formula Premium v2 New Version

JSS Formula Premium is a JavaScript application for parsing and executing spreadsheet-like formulas. It handles ranges, variables, JS precision, worksheets and a great number of formulas available in other spreadsheet software such as Excel or Google Sheets.

	Formula Basic	Formula Premium
License	MIT	Requires a license
Scope	window	restricted scope
Parser	new Function	Custom parser
JavaScript precision issues	No	Yes
Date operations	No	Yes
Cross worksheet/spreadsheet calculations	No	Yes *
Defined names	No	Yes **
External variables	No	Yes **
Matrix calculations	No	Yes **
Internationalization	No	Yes
Number of implemented formulas	403	482

Available formulas

You can find a list of the available formulas here.

Compatibility

The plugin can be used stand-alone or integrated with Jspreadsheet CE, BASE, and PRO.

* Only available on stand-alone and with the PRO distributions.
** Only available on stand-alone and with the PRO v8 distributions.

License

This plugin requires a license that should be associated with one specified domain. If you need a license for redistribution or SaaS, please contact contact@jspreadsheet.com

Precision

JSS Formula Premium has an adjustPrecision flag to help with JS rounding problems. The solution is based on an adaptative toFixed depending on the number of decimals.

// Activate the precision adjust
formula.adjustPrecision = true;

formula('37.02 + 2.56');
// Without adjustPrecision: 39.580000000000005
// With adjustPrecision: 39.58

formula('185.32 - 84.78');
// Without adjustPrecision: 100.53999999999999
// With adjustPrecision: 100.54

formula('25.92 * 3.33');
// Without adjustPrecision: 86.31360000000001
// With adjustPrecision: 86.3136

formula('9.15 / 6');
// Without adjustPrecision: 1.5250000000000001
// With adjustPrecision: 1.525

NOTE: When this option is enabled, the results will be rounded to a maximum of ten decimal places.

Usage

The formula method receives the expression and the variables that will support the calculations.

@param {string} expression - the formula to be calculated
@param {object} variables - the variables and values necessary to parse the expression
@param {number=} x - a optional coordinate reference
@param {number=} y - a optional coordinate reference

formula(expression: String, variables: Object, [x: Number], [y: Number]) : string|array

Internationalization

Translate the formulas name to portuguese, for example:

<html>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script>
// Activate the precision adjust
formula.adjustPrecision = true;

// Formula parser
formula.onbeforeformula = function(expression) {
    return expression.replace(/\./g, '').replace(/\,/g, '.')
}

// Translate the formula names
var translate = {
    NOW: "AGORA",
    RAND: "ALEATÓRIO",
    RANDBETWEEN: "ALEATÓRIOENTRE",
    YEAR: "ANO",
    AREAS: "ÁREAS",
    ROUND: "ARRED",
    FLOOR: "ARREDMULTB",
    ROUNDDOWN: "ARREDONDAR.PARA.BAIXO",
    ROUNDUP: "ARREDONDAR.PARA.CIMA",
    TRIM: "ARRUMAR",
    ASINH: "ASENH",
    ASIN: "ASEN",
    AVERAGEIF: "MÉDIASE",
    DB: "BD",
    DCOUNT: "BDCONTAR",
    DCOUNTA: "BDCONTARA",
    DDB: "BDD",
    DSTDEVP: "BDDESVPA",
    DSTDEV: "BDEST",
    DGET: "BDEXTRAIR",
    DMAX: "BDMÁX",
    DAVERAGE: "BDMÉDIA",
    DMIN: "BDMÍN",
    DPRODUCT: "BDMULTIPL",
    DSUM: "BDSOMA",
    VDB: "BDV",
    DVAR: "BDVAREST",
    DVARP: "BDVARP",
    BETAINV: "BETA.ACUM.INV",
    BIN2DEC: "BINADEC",
    BIN2HEX: "BINAHEX",
    BIN2OCT: "BINAOCT",
    CHAR: "CARACT",
    CELL: "CÉL",
    CODE: "CÓDIGO",
    COLUMN: "COL",
    COLUMNS: "COLS",
    COMPLEX: "COMPLEXO",
    CONCATENATE: "CONCATENAR",
    COUNTIF: "CONT.SE",
    COUNTIFS: "CONT.SES",
    COUNTA: "CONT.VALORES",
    COUNT: "CONTAR",
    COUNTBLANK: "CONTAR.VAZIO",
    CONVERT: "CONVERTER",
    MATCH: "CORRESP",
    GROWTH: "CRESCIMENTO",
    CRITBINOM: "CRIT.BINOM",
    COUPPCD: "CUPDATAANT",
    COUPNCD: "CUPDATAPRÓX",
    COUPDAYS: "CUPDIAS",
    COUPDAYSBS: "CUPDIASINLIQ",
    COUPDAYSNC: "CUPDIASPRÓX",
    COUPNUM: "CUPNÚM",
    KURT: "CURT",
    DATE: "DATA",
    DATEVALUE: "DATA.VALOR",
    DATEVALUE: "DATA.VALOR",
    EDATE: "DATAM",
    DEC2BIN: "DECABIN",
    DEC2HEX: "DECAHEX",
    DEC2OCT: "DECAOCT",
    FIXED: "DEF.NÚM.DEC",
    GESTEP: "DEGRAU",
    DISC: "DESC",
    OFFSET: "DESLOC",
    AVEDEV: "DESV.MÉDIO",
    STDEV: "DESVPAD",
    STDEVA: "DESVPADA",
    STDEVP: "DESVPADP",
    STDEVPA: "DESVPADPA",
    DEVSQ: "DESVQ",
    DAY: "DIA",
    WEEKDAY: "DIA.DA.SEMANA",
    DAYS360: "DIAS360",
    WORKDAY: "DIATRABALHO",
    NETWORKDAYS: "DIATRABALHOTOTAL",
    RIGHT: "DIREITA",
    NEGBINOMDIST: "DIST.BIN.NEG",
    HYPGEOMDIST: "DIST.HIPERGEOM",
    LOGNORMDIST: "DIST.LOGNORMAL",
    NORMDIST: "DIST.NORM",
    NORMSDIST: "DIST.NORMP",
    CHIDIST: "DIST.QUI",
    BETADIST: "DISTBETA",
    EXPONDIST: "DISTEXPON",
    FDIST: "DISTF",
    GAMMADIST: "DISTGAMA",
    ABSSKEW: "DISTORÇÃO",
    BINOMDIST: "DISTRBINOM",
    TDIST: "DISTT",
    SLN: "DPD",
    DURATION: "DURAÇÃO",
    AND: "E",
    AND: "E",
    ISNA: "É.NÃO.DISP",
    ISNONTEXT: "É.NÃO.TEXTO",
    ISBLANK: "ÉCÉL.VAZIA",
    ISERR: "ÉERRO",
    ISERROR: "ÉERROS",
    EFFECT: "EFETIVA",
    ISODD: "ÉIMPAR",
    ISLOGICAL: "ÉLÓGICO",
    ADDRESS: "ENDEREÇO",
    ISNUMBER: "ÉNÚM",
    STEYX: "EPADYX",
    ISEVEN: "ÉPAR",
    ISPMT: "ÉPGTO",
    ISREF: "ÉREF",
    CHOOSE: "ESCOLHER",
    LEFT: "ESQUERDA",
    ISTEXT: "ÉTEXTO",
    EXACT: "EXATO",
    MID: "EXT.TEXTO",
    FALSO: "FALSO",
    FACTDOUBLE: "FATDUPLO",
    FACT: "FATORIAL",
    EOMONTH: "FIMMÊS",
    PHONETIC: "FONÉTICA",
    YEARFRAC: "FRAÇÃOANO",
    FREQUENCY: "FREQUÊNCIA",
    ERF: "FUNERRO",
    ERFC: "FUNERROCOMPL",
    DEGREES: "GRAUS",
    HEX2BIN: "HEXABIN",
    HEX2DEC: "HEXADEC",
    HEX2OCT: "HEXAOCT",
    HYPERLINK: "HIPERLINK",
    TODAY: "HOJE",
    HOUR: "HORA",
    IMAGINARY: "IMAGINÁRIO",
    IMARGUMENT: "IMARG",
    IMCONJUGATE: "IMCONJ",
    ODD: "ÍMPAR",
    IMPOWER: "IMPOT",
    IMPRODUCT: "IMPROD",
    IMSQRT: "IMRAIZ",
    IMSIN: "IMSENO",
    IMSUM: "IMSOMA",
    IMSUB: "IMSUBTR",
    SLOPE: "INCLINAÇÃO",
    INDEX: "ÍNDICE",
    INDIRECT: "INDIRETO",
    GETPIVOTDATA: "INFODADOSTABELADINÂMICA",
    INFO: "INFORMAÇÃO",
    CONFIDENCE: "INT.CONFIANÇA",
    INTERCEPT: "INTERCEPÇÃO",
    NORMINV: "INV.NORM",
    NORMSINV: "INV.NORMP",
    CHIINV: "INV.QUI",
    FINV: "INVF",
    GAMMAINV: "INVGAMA",
    LOGINV: "INVLOG",
    TINV: "INVT",
    IPMT: "IPGTO",
    ACCRINT: "JUROSACUM",
    ACCRINTM: "JUROSACUMV",
    ROW: "LIN",
    ROWS: "LINS",
    GAMMALN: "LNGAMA",
    FIND: "LOCALIZAR",
    YIELD: "LUCRO",
    YIELDDISC: "LUCRODESC",
    ODDFYIELD: "LUCROPRIMINC",
    ODDLYIELD: "LUCROÚLTINC",
    YIELDMAT: "LUCROVENC",
    LARGE: "MAIOR",
    UPPER: "MAIÚSCULA",
    MROUND: "MARRED",
    MDETERM: "MATRIZ.DETERM",
    MINVERSE: "MATRIZ.INVERSO",
    MMULT: "MATRIZ.MULT",
    MAX: "MÁXIMO",
    MAXA: "MÁXIMOA",
    GCD: "MDC",
    MDURATION: "MDURAÇÃO",
    MEDIAN: "MED",
    AVERAGE: "MÉDIA",
    GEOMEAN: "MÉDIA.GEOMÉTRICA",
    HARMEAN: "MÉDIA.HARMÔNICA",
    TRIMMEAN: "MÉDIA.INTERNA",
    AVERAGEA: "MÉDIAA",
    AVERAGEIF: "MÉDIASE",
    AVERAGEIFS: "MÉDIASES",
    SMALL: "MENOR",
    MONTH: "MÊS",
    MIN: "MÍNIMO",
    MINA: "MÍNIMOA",
    LOWER: "MINÚSCULA",
    MINUTE: "MINUTO",
    LCM: "MMC",
    MODE: "MODO",
    DOLLAR: "MOEDA",
    DOLLARDE: "MOEDADEC",
    DOLLARFR: "MOEDAFRA",
    MIRR: "MTIR",
    REPLACE: "MUDAR",
    PRODUCT: "MULT",
    NOT: "NÃO",
    NA: "NÃO.DISP",
    LEN: "NÚM.CARACT",
    WEEKNUM: "NÚMSEMANA",
    OCT2BIN: "OCTABIN",
    OCT2DEC: "OCTADEC",
    OCT2HEX: "OCTAHEX",
    RANK: "ORDEM",
    PERCENTRANK: "ORDEM.PORCENTUAL",
    TBILLEQ: "OTN",
    TBILLYIELD: "OTNLUCRO",
    TBILLPRICE: "OTNVALOR",
    OR: "OU",
    STANDARDIZE: "PADRONIZAR",
    EVEN: "PAR",
    PERCENTILE: "PERCENTIL",
    PMT: "PGTO",
    CUMPRINC: "PGTOCAPACUM",
    CUMIPMT: "PGTOJURACUM",
    POWER: "POTÊNCIA",
    PPMT: "PPGTO",
    PRICE: "PREÇO",
    PRICEDISC: "PREÇODESC",
    ODDFPRICE: "PREÇOPRIMINC",
    ODDLPRICE: "PREÇOÚLTINC",
    PRICEMAT: "PREÇOVENC",
    FORECAST: "PREVISÃO",
    PROPER: "PRI.MAIÚSCULA",
    LOOKUP: "PROC",
    HLOOKUP: "PROCH",
    SEARCH: "PROCURAR",
    VLOOKUP: "PROCV",
    LINEST: "PROJ.LIN",
    LOGEST: "PROJ.LOG",
    QUARTILE: "QUARTIL",
    QUOTIENT: "QUOCIENTE",
    RADIANS: "RADIANOS",
    SQRT: "RAIZ",
    SQRTPI: "RAIZPI",
    RECEIVED: "RECEBER",
    ROMAN: "ROMANO",
    RSQ: "RQUAD",
    SYD: "SDA",
    IF: "SE",
    IFERROR: "SEERRO",
    SECOND: "SEGUNDO",
    SIN: "SEN",
    SINH: "SENH",
    SIGN: "SINAL",
    SUM: "SOMA",
    SUMSQ: "SOMAQUAD",
    SUMPRODUCT: "SOMARPRODUTO",
    SUMIF: "SOMASE",
    SERIESSUM: "SOMASEQÜÊNCIA",
    SUMIFS: "SOMASES",
    SUMX2MY2: "SOMAX2DY2",
    SUMX2PY2: "SOMAX2SY2",
    SUMXMY2: "SOMAXMY2",
    REPLACE: "SUBSTITUIR",
    RATE: "TAXA",
    INTRATE: "TAXAJUROS",
    TIME: "TEMPO",
    TREND: "TENDÊNCIA",
    CHITEST: "TESTE.QUI",
    FTEST: "TESTEF",
    TTEST: "TESTET",
    ZTEST: "TESTEZ",
    CEILING: "TETO",
    TEXT: "TEXTO",
    TYPE: "TIPO",
    IRR: "TIR",
    CLEAN: "TIRAR",
    TRANSPOSE: "TRANSPOR",
    TRUNC: "TRUNCAR",
    VALUE: "VALOR",
    TIMEVALUE: "VALOR.TEMPO",
    VERDADEIRO: "VERDADEIRO",
    FV: "VF",
    FVSCHEDULE: "VFPLANO",
    PV: "VP",
    NPV: "VPL",
    XIRR: "XTIR",
    XNPV: "XVPL",
}

var keys = Object.keys(translate)
keys.forEach(function(v){
    formula[translate[v]] = formula[v];
});

// By default method arguments are separated by comma. But you can change that using:
formula.divisor = ';';

// Run the formula
formula("SOMA(1;2;3)"); // result = 6
</script>
</html>

Stand-alone Initialization

The basic standalone usage.

<html>
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>
<script>
// Activate the precision adjust
formula.adjustPrecision = true;
// Define the license just once
formula.license('YjQzMzdlOTRiOGY3ZTQ0ZDQ4ZTI1YWU3MDFjMDI0ZWJmOTNjODA');
</script>
</html>

Examples

You can run the following tests in the browser's console when initialized as above.

Range operations

formula('A1:B1*2', { A1: 2, B1: 4 });
// Returns Array[ 4, 8 ]

formula('SUM(A1:B1*2)', { A1: 1, B1: 'A2+B2', A2: 3, B2: 4 })
// Returns 16

formula('SUM(A1:A6)', { A1: 2, A2: 4, A3: 5, A4: 1, A5: 5, A6: 1 });
// Returns 18

formula('AVERAGE(CALCULATION*10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns 20

formula('SUM(B:B)', { B1: 1, B2: 1, B3: 14 });
// Returns 16

Conditional calculations

formula('IF(B1=0,0,B9/B1)', { B1:0, B9: 3 });
// Returns zero when B1 is zero

formula('IF(true, CALCULATION, 10)', { CALCULATION: 'A1:A3', A1: 1, A2: 2, A3: 3 })
// Returns [[1], [2], [3]]

formula('IF(C16+C15!=0,C13+C14,false)', { C16: 0, C15: 0, C14: 3, C13: 12 })
// Returns false

Comparisons

formula('1*2<1^4');
// Returns false

formula('(1==1)<>(2>2)')
// Returns true

Date calculations

formula('NOW()+1');
// Today + one day

formula('DATE(2021,1,1) > DATE(2021,2,1)');
// false

Cross worksheets

formula('SHEET1!A1*A1', { 'SHEET1!A1': 2, 'A1': 3 });
// Returns 6

formula('SUM(SHEET3!B1:B3)', { 'SHEET3!B1': 3, 'SHEET3!B2': 3, 'SHEET3!B3': 4 });
// Returns 10

formula('SUM(B:B)', { 'B1': 1, 'B2': 2, 'B3': 4 });
// Returns 7

Worksheet operations

formula('SHEET1!A1*10', { SHEET1: [[1,2,3],[4,5,6]] });
// Returns 1 * 10

formula('SHEET1!B1*SHEET2!B1', { SHEET1: [[1,2,3],[4,5,6]], SHEET2: [[10,20,30]] });
// Returns 2 * 20

Live calculator

For testing, you can use the following form, or use the browser’s console.

Type here your formula

Variables should be define as an object

Results

Integrating with JSS spreadsheet

How to integrate JSS spreadsheet and the formula-pro plugin.

Using CDN

Using NPM

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id='spreadsheet'></div>

<script>
// License for Formula Plugin
jspreadsheet.setLicense('MmEwMjcyNWRkMmU2N2I2NTkwNzg3ZDcyNmIyODBiYzk0ZGY1OGQ3N2MzOTRkYzc2Yzg5NWJlNzc2YWQ1MDc1MmVkMjZhMzYyNzM4ZWExNjU4OTRmMjhmMDAwYTY0MmNiMWVmYTQzZGYwNzI5NzMwNDgyMDQ0ZDVlYzM1ZjMwZjIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJORFF5TURRd015d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// Add-on for Jspreasheet
jspreadsheet.setExtensions({ formula });

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { minDimensions: [10, 10] },
        { minDimensions: [10, 10] },
    ],
});
</script>
</html>

// Jspreadsheet Pro
import jspreadsheet from 'jspreadsheet';

// Formula Premium Plugin
import formula from '@jspreadsheet/formula-pro';

// License for Formula Plugin
jspreadsheet.setLicense('MmEwMjcyNWRkMmU2N2I2NTkwNzg3ZDcyNmIyODBiYzk0ZGY1OGQ3N2MzOTRkYzc2Yzg5NWJlNzc2YWQ1MDc1MmVkMjZhMzYyNzM4ZWExNjU4OTRmMjhmMDAwYTY0MmNiMWVmYTQzZGYwNzI5NzMwNDgyMDQ0ZDVlYzM1ZjMwZjIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJORFF5TURRd015d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// Add-on for Jspreasheet
jspreadsheet.setExtensions({ formula });

// Create a spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { minDimensions: [10, 10] },
        { minDimensions: [10, 10] },
    ]
});

Using external variable in the calculations

<html>
<script src="https://jspreadsheet.com/v9/jspreadsheet.js"></script>
<script src="https://jsuites.net/v4/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v9/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v4/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />

<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id='spreadsheet'></div>

<script>
// License for Formula Plugin
jspreadsheet.setLicense('MmEwMjcyNWRkMmU2N2I2NTkwNzg3ZDcyNmIyODBiYzk0ZGY1OGQ3N2MzOTRkYzc2Yzg5NWJlNzc2YWQ1MDc1MmVkMjZhMzYyNzM4ZWExNjU4OTRmMjhmMDAwYTY0MmNiMWVmYTQzZGYwNzI5NzMwNDgyMDQ0ZDVlYzM1ZjMwZjIsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTJORFF5TURRd015d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');

// Declare external values to be used in the calculations
formula.define({ test: 10 });

// Add-on for Jspreasheet
jspreadsheet.setExtensions({ formula });

// Create the spreadsheets
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [
        { data: [['=test*10']] },
    ],
});
</script>
</html>