Formula Premium
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.comPrecision
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
External Expressions
You can define variables that can be used in your calculations as below.
// Define a number
formula.define({ number: 1000 });
// You can also define a number with single quotes
formula.define({ number: '2000' });
// A string you must define with including double quotes as below
formula.define({ hello: '"Dealing with strings"' });
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('ZmM3YzAzMmU1ZmI0OTdlZGYxM2U2ZjljYjZlZTg2MTc3MmZiZDE5M2I5MWVmZDRmY2FhMGM2MTQwZmFkOTk5ZDdjOTBhNTk0OGFlNzlmMzZlOWU4NDQ4NjhlMTc0NDNjMzZjZmRiNGJkNTZlMTM0YTQ4ZmJhM2NlMzJiNDM5ZTUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNOVGs1T1RrMU55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');
// 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('ZmM3YzAzMmU1ZmI0OTdlZGYxM2U2ZjljYjZlZTg2MTc3MmZiZDE5M2I5MWVmZDRmY2FhMGM2MTQwZmFkOTk5ZDdjOTBhNTk0OGFlNzlmMzZlOWU4NDQ4NjhlMTc0NDNjMzZjZmRiNGJkNTZlMTM0YTQ4ZmJhM2NlMzJiNDM5ZTUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNOVGs1T1RrMU55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');
// 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('ZmM3YzAzMmU1ZmI0OTdlZGYxM2U2ZjljYjZlZTg2MTc3MmZiZDE5M2I5MWVmZDRmY2FhMGM2MTQwZmFkOTk5ZDdjOTBhNTk0OGFlNzlmMzZlOWU4NDQ4NjhlMTc0NDNjMzZjZmRiNGJkNTZlMTM0YTQ4ZmJhM2NlMzJiNDM5ZTUsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTNOVGs1T1RrMU55d2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpJaXdpYzJOdmNHVWlPbHNpZGpjaUxDSjJPQ0lzSW5ZNUlpd2lZMmhoY25Seklpd2labTl5YlhNaUxDSm1iM0p0ZFd4aElpd2ljR0Z5YzJWeUlpd2ljbVZ1WkdWeUlpd2lZMjl0YldWdWRITWlMQ0pwYlhCdmNuUWlMQ0ppWVhJaUxDSjJZV3hwWkdGMGFXOXVjeUlzSW5ObFlYSmphQ0pkTENKa1pXMXZJanAwY25WbGZRPT0=');
// 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>