
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>