Basic Functions

SpreadJS includes a comprehensive Calculation Engine that supports 450 built-in functions, custom functions, array functions, dynamic arrays, the formula textbox, localized language function names, most Excel functions, and more.

Double-click the Avg Score cells or bottom score values to view the formulas being used. You can also add any formula by entering the function name and the cell range, or by selecting the range using your mouse like Excel.

SpreadJS provides a large variety of functions, from basic functions that are commonly used — like SUM — to advanced functions like MAX: You can input the SUM function from the above code directly into a cell, or you can use the setFormula method to apply the formula. Note: SpreadJS provides support for the following Excel basic functions: ABS ACOS ASIN ATAN ATAN2 COS CEILING ODD EVEN FLOOR LN SQRT SIN TAN SIGN GCD LCM PRODUCT POWER MOD QUOTIENT SUBTOTAL INT MROUND ROUND ROUNDDOWN ROUNDUP TRUNC EXP LOG LOG10 SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 SERIESSUM PI SQRTPI DEGREES RADIANS COSH ACOSH SINH ASINH TANH ATANH MDETERM MINVERSE MMULT FACT FACTDOUBLE MULTINOMIAL RAND RANDBETWEEN COMBIN ROMAN CEILING.PRECISE ISO.CEILING FLOOR.PRECISE MUNIT AND OR NOT IF IFERROR TRUE FALSE DATE TIME DATEVALUE TIMEVALUE NOW TODAY HOUR MINUTE SECOND DAY MONTH YEAR WEEKNUM WEEKDAY EDATE EOMONTH WORKDAY WORKDAY.INTL DAYS360 NETWORKDAYS NETWORKDAYS.INTL YEARFRAC DATEDIF CLEAN TRIM DOLLAR FIXED TEXT VALUE LOWER UPPER PROPER CHAR CODE REPLACE SUBSTITUTE CONCATENATE LEFT MID RIGHT REPT LEN FIND SEARCH EXACT T ISERROR ISERR ISNA ERROR.TYPE ISNUMBER ISEVEN ISODD N ISBLANK ISLOGICAL ISTEXT ISNONTEXT ISREF TYPE NA REFRESH DAVERAGE DCOUNT DCOUNTA DGET DMAX DMIN DPRODUCT DSTDEV DSTDEVP DSUM DVAR DVARP BESSELI BESSELJ BESSELK BESSELY BIN2DEC BIN2HEX BIN2OCT DEC2BIN DEC2HEX DEC2OCT HEX2BIN HEX2DEC HEX2OCT OCT2BIN OCT2DEC OCT2HEX ERF ERF.PRECISE ERFC ERFC.PRECISE DELTA GESTEP COMPLEX IMABS IMAGINARY IMARGUMENT IMCONJUGATE IMCOS IMDIV IMEXP IMLN IMLOG10 IMLOG2 IMREAL IMSIN IMSQRT IMSUB IMPOWER IMPRODUCT IMSUM RANK.AVG FV FVSCHEDULE NPV PV RECEIVED XNPV CUMIPMT CUMPRINC IPMT ISPMT PMT PPMT COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD DURATION MDURATION NPER YIELD YIELDDISC YIELDMAT AMORDEGRC AMORLINC ODDFYIELD ODDLYIELD ODDLPRICE TBILLEQ TBILLYIELD IRR XIRR RATE VDB ACCRINT ACCRINTM DISC EFFECT INTRATE NOMINAL DB DDB SLN SYD DOLLARDE DOLLARFR PRICE PRICEDISC PRICEMAT ODDFPRICE TBILLPRICE EURO EUROCONVERT RRI ADDRESS INDEX OFFSET ROW COLUMN ROWS COLUMNS TRANSPOSE LOOKUP HLOOKUP VLOOKUP CHOOSE MATCH INDIRECT TREND GROWTH FORECAST AVERAGE STDEV STDEV.S PERCENTILE PERCENTILE.INC MAX MAXA MIN MINA LARGE SMALL AVERAGEA AVERAGEIF AVERAGEIFS MEDIAN MODE MODE.SNGL GEOMEAN HARMEAN TRIMMEAN FREQUENCY RANK RANK.EQ KURT PERCENTRANK PERCENTRANK.INC PERCENTRANK.EXC QUARTILE QUARTILE.INC COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS AVEDEV STDEVA STDEVP STDEV.P STDEVPA VAR VAR.S VARA VARP VAR.P VARPA COVAR COVARIANCE.P DEVSQ CONFIDENCE CONFIDENCE.NORM CONFIDENCE.T INTERCEPT LINEST SLOPE LOGEST STEYX BETADIST BETA.DIST BETAINV BETA.INV BINOMDIST BINOM.DIST NEGBINOMDIST NEGBINOM.DIST CRITBINOM BINOM.INV CHIDIST CHISQ.DIST.RT CHISQ.DIST CHIINV CHISQ.INV.RT CHISQ.INV CHITEST CHISQ.TEST CORREL EXPONDIST EXPON.DIST FDIST F.DIST F.DIST.RT FINV F.INV.RT F.INV FISHER FISHERINV FTEST F.TEST GAMMADIST GAMMA.DIST GAMMAINV GAMMA.INV GAMMALN GAMMALN.PRECISE HYPGEOMDIST HYPGEOM.DIST LOGNORMDIST LOGNORM.DIST LOGINV LOGNORM.INV NORMDIST NORM.DIST NORMINV NORM.INV NORMSDIST NORMSINV NORM.S.INV NORM.S.DIST PEARSON RSQ POISSON POISSON.DIST PROB SKEW STANDARDIZE TDIST T.DIST T.DIST.RT T.DIST.2T TINV T.INV.2T T.INV TTEST T.TEST WEIBULL WEIBULL.DIST ZTEST Z.TEST PERMUT ACOT ACOTH ARABIC BASE COMBINA COT COTH CSC CSCH DECIMAL FLOOR.MATH SEC SECH BINOM.DIST.RANGE GAMMA MAXIFS GAUSS MINIFS PERMUTATIONA PHI SKEW.P BAHTTEXT CONCAT FINDB LEFTB LENB MIDB REPLACEB RIGHTB SEARCHB TEXTJOIN UNICHAR UNICODE BITAND BITLSHIFT BITOR BITRSHIFT BITXOR IMCOSH IMCOT IMCSC IMCSCH IMSEC IMSECH IMSINH IMTAN DAYS ISOWEEKNUM IFNA IFS SWITCH XOR PDURATION RRI ISFORMULA AREAS FORMULATEXT HYPERLINK ENCODEURL CEILING.MATH CONVERT XMATCH XLOOKUP LET OBJECT PROPERTY
import * as React from 'react'; import * as ReactDOM from 'react-dom'; import GC from '@grapecity/spread-sheets'; import { SpreadSheets, Worksheet } from '@grapecity/spread-sheets-react'; import './styles.css'; const Component = React.Component; function _getElementById(id) { return document.getElementById(id); } class App extends Component { constructor(props) { super(props); this.spread = null; } render() { return <div class="sample-tutorial"> <div class="sample-spreadsheets"> <SpreadSheets workbookInitialized={spread=>this.initSpread(spread)}> <Worksheet> </Worksheet> <Worksheet> </Worksheet> </SpreadSheets> </div> </div>; } initSpread(spread) { this.spread = spread; var spreadNS = GC.Spread.Sheets; var sheet = spread.getActiveSheet(); spread.suspendPaint(); var data = [ ['Math - Grade 5'], ['Assignments'], ['Student', 1, 2, 3, 4, 5, , 'Avg. Score'], ['Anna Mull', 76, 52, 91, 87, 98], ['Anna Sthesia', 95, 95, 94, 98, 95], ['Barb Ackue', 86, 83, 84, 89, 90], ['Barb Dwyer', 59, 40, 60, 20, 66], ['Barry Wine', 75, 55, 64, 76, 89], ['Bob Frapples', 91, 80, 72, 98, 95], ['Brock Lee', 86, 77, 89, 76, 70], ['Buck Kinnear', 100, 95, 94, 92, 91], ['Cliff Hanger', 97, 98, 99, 81, 89], ['Cory Ander', 53, 69, 93, 60, 95], [''], ['Average Score:'], ['Highest Score:'], ['Lowest Score:'], ['Median Score:'], ]; var formulas_r = [ ['=AVERAGE(C4:G4)'], ['=AVERAGE(C5:G5)'], ['=AVERAGE(C6:G6)'], ['=AVERAGE(C7:G7)'], ['=AVERAGE(C8:G8)'], ['=AVERAGE(C9:G9)'], ['=AVERAGE(C10:G10)'], ['=AVERAGE(C11:G11)'], ['=AVERAGE(C12:G12)'], ['=AVERAGE(C13:G13)'] ]; var formulas_b = [ ['=AVERAGE(C4:C13)', '=AVERAGE(D4:D13)', '=AVERAGE(E4:E13)', '=AVERAGE(F4:F13)', '=AVERAGE(G4:G13)'], ['=MAX(C4:C13)', '=MAX(D4:D13)', '=MAX(E4:E13)', '=MAX(F4:F13)', '=MAX(G4:G13)'], ['=MIN(C4:C13)', '=MIN(D4:D13)', '=MIN(E4:E13)', '=MIN(F4:F13)', '=MIN(G4:G13)'], ['=MEDIAN(C4:C13)', '=MEDIAN(D4:D13)', '=MEDIAN(E4:E13)', '=MEDIAN(F4:F13)', '=MEDIAN(G4:G13)'], ]; sheet.setArray(0, 1, data); sheet.setArray(3, 8, formulas_r, true); sheet.setArray(14, 2, formulas_b, true); sheet.setRowHeight(0, 40); sheet.getCell(0, 1).font('Bold 19px Arial').vAlign(spreadNS.VerticalAlign.center); sheet.addSpan(1, 1, 1, 8); sheet.getCell(1, 1).font('Bold 13px Arial') .hAlign(spreadNS.HorizontalAlign.center) .backColor('rgb(56,83,145') .foreColor('white') .vAlign(spreadNS.VerticalAlign.center); sheet.getRange(2, 1, 1, 8).font('Bold 13px Arial') .backColor('rgb(219,225,240)') .vAlign(spreadNS.VerticalAlign.center) .borderBottom(new spreadNS.LineBorder('black', spreadNS.LineStyle.thin)); sheet.getCell(2, 8).hAlign(spreadNS.HorizontalAlign.right).backColor('rgb(184,198,228)'); sheet.getRange(3, 1, 10, 8).font('12px Arial'); sheet.getRange(14, 1, 4, 8).backColor('rgb(230,230,230)'); sheet.getRange(14, 1, 4, 1).font('Bold 12px Arial').hAlign(spreadNS.HorizontalAlign.right); [110, 70, 70, 70, 70, 70, 10, 80].forEach(function (val, index) { sheet.setColumnWidth(index + 1, val); }); sheet.conditionalFormats.add3ScaleRule( spreadNS.ConditionalFormatting.ScaleValueType.lowestValue, null, 'rgb(231,114,111)', spreadNS.ConditionalFormatting.ScaleValueType.percentile, 50, 'rgb(252,252,255)', spreadNS.ConditionalFormatting.ScaleValueType.highestValue, null, 'rgb(122,188,129)', [new GC.Spread.Sheets.Range(3, 8, 10, 1)]); spread.resumePaint(); this.initExcel2010(spread); } initExcel2010(spread) { var spreadNS = GC.Spread.Sheets; var sheet = spread.getSheet(1); sheet.name("Excel 2010 Functions"); sheet.setColumnWidth(0, 200); sheet.setColumnWidth(1, 400); sheet.setColumnWidth(2, 80); sheet.getRange(-1, 0, -1, 1).wordWrap(true); sheet.getRange(-1, 1, -1, 1).wordWrap(true); var data = [ ["Formula", "Description", "Result"], ["=BETA.DIST(2,8,10,TRUE,1,3)", "Cumulative beta probability density function"], ["=BETA.DIST(2,8,10,FALSE,1,3)", "Beta probability density function"], ["=CEILING.PRECISE(4.3)", "Rounds 4.3 up to the nearest multiple of 1."], ["=CEILING.PRECISE(-4.3)", "Rounds -4.3 up to the nearest multiple of 1. Rounds toward 0 because the number is negative."], ["=CEILING.PRECISE(4.3, 2)", "Rounds 4.3 up to the nearest multiple of 2."], ["=CHISQ.DIST(0.5,1,TRUE)", "The chi-squared distribution for 0.5, returned as the cumulative distribution function, using 1 degree of freedom."], ["=CHISQ.DIST(2,3,FALSE)", "The chi-squared distribution for 2, returned as the probability density function, using 3 degrees of freedom."], ["=CHISQ.INV(0.93,1)", "Inverse of the left-tailed probability of the chi-squared distribution for 0.93, using 1 degree of freedom."], ["=CHISQ.INV(0.6,2)", "Inverse of the left-tailed probability of the chi-squared distribution for 0.6, using 2 degrees of freedom."], ["=CONFIDENCE.T(0.05,1,50)", "Confidence interval for the mean of a population based on a sample size of 50, with a 5% significance level and a standard deviation of 1. This is based on a Student's t-distribution."], ["=COVARIANCE.S({2,4,8},{5,8,11})", "Sample covariance for the data points entered as an array in the function."], ["=ERF.PRECISE(0.74500)", "Error function integrated between 0 and 0.74500 (0.707929)"], ["=ERFC.PRECISE(0.74500)", "Complementary ERF function of 0.74500."], ["=F.DIST(15.2069,6,4,TRUE)", "F probability using the cumulative distribution function (TRUE cumulative argument)."], ["=F.DIST(15.2069,6,4,FALSE)", "F probability using the probability density function (FALSE cumulative argument)."], ["=F.INV(0.01,6,4)", "Inverse of the F probability distribution."], ["=FLOOR.PRECISE(-3.2)", "Rounds -3.2 down to the nearest multiple of -1"], ["=FLOOR.PRECISE(3.2)", "Rounds 3.2 down to the nearest multiple of 1"], ["=FLOOR.PRECISE(3.2, 2)", "Rounds 3.2 down to the nearest multiple of 2"], ["=GAMMALN.PRECISE(4)", "Natural logarithm of the gamma function at 4"], ["=HYPGEOM.DIST(1,4,8,20,TRUE)", "Cumulative hypergeometric distribution function."], ["=HYPGEOM.DIST(1,4,8,20,FALSE)", "Probability hypergeometric distribution function."], ["=ISO.CEILING(4.3)", "Rounds 4.3 up to nearest multiple of 1"], ["=ISO.CEILING(-4.3)", "Rounds -4.3 up to nearest multiple of 1"], ["=ISO.CEILING(4.3, 2)", "Rounds 4.3 up to the nearest multiple of 2"], ["=LOGNORM.DIST(4,3.5,1.2,TRUE)", "Cumulative lognormal distribution at 4."], ["=LOGNORM.DIST(4,3.5,1.2,FALSE)", "Probability lognormal distribution at 4."], ["=NEGBINOM.DIST(10,5,0.25,TRUE)", "Cumulative negative binomial distribution."], ["=NEGBINOM.DIST(10,5,0.25,FALSE)", "Probability negative binomial distribution."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,1,31))", "Results in 22 future workdays. Subtracts 9 nonworking weekend days (5 Saturdays and 4 Sundays) from the 31 total days between the two dates. By default, Saturday and Sunday are considered non-working days."], ["=NETWORKDAYS.INTL(DATE(2006,2,28),DATE(2006,1,31))", "Results in -21, which is 21 workdays in the past."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),7,{\"2006/1/2\",\"2006/1/16\"})", "Results in 22 future workdays by sutracting 10 nonworking days (4 Fridays, 4 Saturdays, 2 Holidays) from the 32 days between Jan 1 2006 and Feb 1 2006. Uses the 7 argument for weekend, which is Friday and Saturday. There are also two holidays in this time period."], ["=NETWORKDAYS.INTL(DATE(2006,1,1),DATE(2006,2,1),\"0010001\",{\"2006/1/2\",\"2006/1/16\"})", "Results in 20 future workdays. Same time period as above, but with Sunday and Wednesday as weekend days."], ["=NORM.S.DIST(1.333333,TRUE)", "Normal cumulative distribution function at 1.333333."], ["=NORM.S.DIST(1.333333,FALSE)", "Normal probability distribution function at 1.333333."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 7)", "Returns the rank of the value 7 from the array."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 5.43)", "Returns the rank of the value 5.43 in the array."], ["=PERCENTRANK.EXC({1,2,3,4,6,6,7,8,9}, 5.43, 1)", "Returns the rank of the value 5.43 in the array, displaying only 1 significant digit in the result (the default is 3)."], ["=PERCENTILE.EXC({1,2,3,4,5,6}, 0.25)", "Interpolates when the value for the specified percentile lies between two values in the array."], ["=QUARTILE.EXC({1,2,3,4,5,6,7,8,9,10,11},1)", "Locates the position of the first quartile (3)."], ["=QUARTILE.EXC({1,2,3,4,5,6,7,8,9,10,11},3)", "Locates the position of the third quartile (9)."], ["=RANK.AVG(95, {89, 88, 92, 101, 94, 97, 95})", "Finds the rank (the position) of the value 95 in the array (descending order). In this case, 95 was the 3rd one in descending order."], ["=RANK.AVG(95, {89, 88, 92, 101, 94, 97, 95}, 1)", "Finds the rank (the position) of the value 95 in the array (ascending order). In this case, 95 was the 5th one in ascending order."], ["=T.DIST(60,1,TRUE)", "Student's left-tailed t-distribution for 60, returned as the cumulative distribution function, using 1 degree of freedom."], ["=T.DIST(8,3,FALSE)", "Student's left-tailed t-distribution for 8, returned as the probability density function, using 3 degrees of freedom."], ["=T.INV(0.05464,60)", "The t-value of the Student's t-distribution based on specified arguments."], ["=WORKDAY.INTL(DATE(2012,1,1),30,0)", "Using a 0 for the Weekend argument results in a #NUM! error."], ["=WORKDAY.INTL(DATE(2012,1,1),90,11)", "Finds the date 90 workdays from 1/1/2012, counting only Sundays as a weekend day (Weekend argument is 11)."], ["=TEXT(WORKDAY.INTL(DATE(2012,1,1),30,17), \"m/dd/yyyy\")", "Uses the TEXT function to format the resulting serial number (40944) in a \"m/dd/yyyy\" format. Finds the date 30 workdays from 1/1/2012, counting only Saturdays as a weekend day (Weekend argument is 17)."] ]; sheet.setArray(0, 0, data, false); var r, len, i; for (r = 1, len = data.length; r < len; r++) { sheet.setFormula(r, 2, data[r][0]); } var arrayFormulaData = [ ["=MODE.MULT({1,2,3,4,3,2,1,2,1,3})", "The formula must be entered as an array formula. It returns 1, 2, and 3 as the modes because they each appear 3 times. If the formula is not entered as an array formula, the single result is 1.", 3] ]; for (i = 0, len = arrayFormulaData.length; i < len; i++) { var cur = arrayFormulaData[i], rows = cur[2], cols = cur[3] || 1; sheet.addSpan(r, 0, rows, 1); sheet.setValue(r, 0, cur[0]); sheet.addSpan(r, 1, rows, 1); sheet.setValue(r, 1, cur[1]); sheet.setArrayFormula(r, 2, rows, cols, cur[0]); r += rows; } for (i = 1; i < r; i++) { sheet.autoFitRow(i); } sheet.getRange(-1, 2, -1, 1).formatter(".######"); sheet.setFormatter(48, 2, "M/d/yyyy"); var table = sheet.tables.add("FunctionTable", 0, 0, 50, 3, spreadNS.Tables.TableThemes.medium9); table.rowFilter().filterButtonVisible(false); // Array formula with merge cells, set style like above table rows var rowStyle = sheet.getActualStyle(48, 0); sheet.getRange(50, 0, 3, 3).backColor(rowStyle.backColor); sheet.getRange(49, 0, 4, 3).setBorder(rowStyle.borderBottom, { all: true }); } } ReactDOM.render(<App />, _getElementById('app'));
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/react/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- SystemJS --> <script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script> <script src="systemjs.config.js"></script> <script> System.import('$DEMOROOT$/en/lib/react/license.js').then(function () { System.import('./src/app'); }); </script> </head> <body> <div id="app"></div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } #switchAutoMergeMode { margin: 10px 0px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } #app { height: 100%; }
(function (global) { System.config({ transpiler: 'plugin-babel', babelOptions: { es2015: true, react: true }, meta: { '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { '@grapecity/spread-sheets': 'npm:@grapecity/spread-sheets/index.js', '@grapecity/spread-sheets-react': 'npm:@grapecity/spread-sheets-react/index.js', '@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js', 'react': 'npm:react/umd/react.production.min.js', 'react-dom': 'npm:react-dom/umd/react-dom.production.min.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js' }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'jsx' }, "node_modules": { defaultExtension: 'js' }, } }); })(this);