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
LicenseMITRequires a license
Scopewindowrestricted scope
Parsernew FunctionCustom parser
JavaScript precision issuesNoYes
Date operationsNoYes
Cross worksheet/spreadsheet calculationsNoYes *
Defined namesNoYes **
External variablesNoYes **
Matrix calculationsNoYes **
InternationalizationNoYes
Number of implemented formulas403482


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.







{{self.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>


More references