| xlsgen > overview > Formulas |
Formulas and conditional formattings bring dynamic capabilities to Excel documents. There are a number of aspects in Excel formulas :
workbook.FormulaLanguage property. Default is : English.
Formula method from the worksheet object tells you what it is, using the current formula language (English by default).
DeleteFormulas(). Note that removing the formulas preserves the values in cells. This technique of deleting formulas is handy if you want to hide the business logic of your spreadsheet, for instance if the spreadsheet is meant to be distributed to a broad audience.

A formula is attached to a cell at the worksheet level (see IXlsWorksheet for more information), or in the case of merged cells, at the merged cells object level (see IXlsMergedCells for more information).
Formulas are passed just like in Excel. Examples of formulas are as follows :
=SUM(C3:C5) // calculates the sum of the specified cell range =IF(R1C4 > 5; "yes"; "no") // calculates a content depending on a given condition
Supported functions are listed in the following table.
In addition to supporting functions, xlsgen supports the following operators : +, -, *, /, =, <, <=, >, >=, <>, ^ (power), % (percent), & (concatenation).
If the wrong number of parameters is being passed, then an error is raised. A subset of functions support an arbitrary number of parameters, for instance the SUM function supports any number of parameters. Use the semi-colon character to separate parameters.
Formulas and parenthesis can be combined at an arbitrary level. Sometimes, you may have to explicitely add parenthesis for the parser to resolve ambiguities. For instance, A3-A4=0 can be better understood if you pass (A3-A4)=0 instead.
Operands supported include :
R1C1 notation, or Ax notation
R1C1:R1C1 notation, or Ax:Ax notation
The R1C1 notation is one of the two notations supported by Excel, where in RxxxCyyy xxx is a row and yyy is a column, both starting at 1. The Ax notation is the natural Excel notation where A denotes a column (there is more than one letter after the 26th column), and x denotes a row number starting at 1.
Functions are parsed and read using the current formula language. The default formula language is English, but xlsgen supports 5 other languages. See the code below for an example of how to use localized function names.
In order to produce the example in the screen above, the following code is required :
| VB code |
wksht.Number(3, 3) = 5 wksht.Number(4, 3) = 10 wksht.Number(5, 3) = 15 Dim style As IXlsStyle Set style = wksht.NewStyle style.Font.Bold = True style.Apply wksht.Formula(6, 3) = "=SUM(C3:C5)" |
| C# code |
wksht.set_Number(3,3, 5); wksht.set_Number(4,3, 10); wksht.set_Number(5,3, 15); IXlsStyle style = wksht.NewStyle(); style.Font.Bold = 1; style.Apply(); wksht.set_Formula(6,3, "=SUM(C3:C5)"); |
| C/C++ code |
wksht->Number[3][3] = 5; wksht->Number[4][3] = 10; wksht->Number[5][3] = 15; xlsgen::IXlsStylePtr style = wksht->NewStyle(); style->Font->Bold = TRUE; style->Apply(); wksht->Formula[6][3] = "=SUM(C3:C5)"; |
If you'd like to write this function using French function names, you can do the following :
| VB code |
' Tell xlsgen to recognize French function names wbk.FormulaLanguage = xlsgen.formulalanguage_fr wksht.Number(3, 3) = 5 wksht.Number(4, 3) = 10 wksht.Number(5, 3) = 15 Dim style As IXlsStyle Set style = wksht.NewStyle style.Font.Bold = True style.Apply ' French function name, without uppercase wksht.Formula(6, 3) = "=somme(C3:C5)" |
| C# code |
// Tell xlsgen to recognize French function names wbk.FormulaLanguage = (int) xlsgen.enumFormulaLanguage.formulalanguage_fr; wksht.set_Number(3,3, 5); wksht.set_Number(4,3, 10); wksht.set_Number(5,3, 15); IXlsStyle style = wksht.NewStyle(); style.Font.Bold = 1; style.Apply(); // French function name, without uppercase wksht.set_Formula(6,3, "=somme(C3:C5)"); |
| C/C++ code |
// Tell xlsgen to recognize French function names wbk->FormulaLanguage = xlsgen::formulalanguage_fr; wksht->Number[3][3] = 5; wksht->Number[4][3] = 10; wksht->Number[5][3] = 15; xlsgen::IXlsStylePtr style = wksht->NewStyle(); style->Font->Bold = TRUE; style->Apply(); // French function name, without uppercase wksht->Formula[6][3] = "=somme(C3:C5)"; |
If you call user-defined functions (otherwise known as add-ins), you must declare them first using the DeclareUserDefinedFunction method. An example is :
' declare an external user defined function
' (Book1.xls exposes a VBA procedure called Discount1)
wksht.DeclareUserDefinedFunction("Book1.xls!Discount1")
' create a formula with it
wksht.Formula(15,3) = "=Book1.xls!Discount1(970;3)"
| English | French | German | Spanish | Italian | Portuguese | Supported by the built-in calculation engine | Description |
| ABS | ABS | ABS | ABS | ASS | ABS | Returns the absolute value of a number | |
| ACOS | ACOS | ARCCOS | ACOS | ARCCOS | ACOS | Returns the arccosine of a number | |
| ACOSH | ACOSH | ARCCOSHYP | ACOSH | ARCCOSH | ACOSH | Returns the inverse hyperbolic cosine of a number | |
| ADDRESS | ADRESSE | ADRESSE | DIRECCION | INDIRIZZO | ENDEREÇO | Returns a reference as text to a single cell in a worksheet | |
| AND | ET | UND | Y | E | E | Returns TRUE if all of its arguments are TRUE | |
| AREAS | ZONES | BEREICHE | AREAS | AREE | AREAS | Returns the number of areas in a reference | |
| ASIN | ASIN | ARCSIN | ASENO | ARCSEN | ASEN | Returns the arcsine of a number | |
| ASINH | ASINH | ARCSINHYP | ASENOH | ARCSENH | ASENH | Returns the inverse hyperbolic sine of a number | |
| ATAN | ATAN | ARCTAN | ATAN | ARCTAN | ATAN | Returns the arctangent of a number | |
| ATAN2 | ATAN2 | ARCTAN2 | ATAN2 | ARCTAN.2 | ATAN2 | Returns the arctangent from x- and y-coordinates | |
| ATANH | ATANH | ARCTANHYP | ATANH | ARCTANH | ATANH | Returns the inverse hyperbolic tangent of a number | |
| AVEDEV | ECART.MOYEN | MITTELABW | DESVPROM | MEDIA.DEV | DESV.MÉDIO | Returns the average of the absolute deviations of data points from their mean | |
| AVERAGE | MOYENNE | MITTELWERT | PROMEDIO | MEDIA | MÉDIA | Returns the average of its arguments | |
| AVERAGEA | AVERAGEA | MITTELWERTA | PROMEDIOA | MEDIAA | MÉDIAA | Returns the average of its arguments, including numbers, text, and logical values | |
| BETADIST | LOI.BETA | BETAVERT | DISTR.BETA | DISTRIB.BETA | DISTBETA | Returns the beta cumulative distribution function | |
| BETAINV | BETA.INVERSE | BETAINV | DISTR.BETA.INV | INV.BETA | BETA.ACUM.INV | Returns the inverse of the cumulative distribution function for a specified beta distribution | |
| BINOMDIST | LOI.BINOMIALE | BINOMVERT | DISTR.BINOM | DISTRIB.BINOM | DISTRBINOM | Returns the individual term binomial distribution probability | |
| CEILING | PLAFOND | OBERGRENZE | MULTIPLO.SUPERIOR | ARROTONDA.ECCESSO | TETO | Rounds a number to the nearest integer or to the nearest multiple of significance | |
| CELL | CELLULE | ZELLE | CELDA | CELLA | CÉ | Returns information about the formatting, location, or contents of a cell | |
| CHAR | CAR | ZEICHEN | CARACTER | CODICE.CARATT | CARACT | Returns the character specified by the code number | |
| CHIDIST | LOI.KHIDEUX | CHIVERT | DISTR.CHI | DISTRIB.CHI | DIST.QUI | Returns the one-tailed probability of the chi-squared distribution | |
| CHIINV | KHIDEUX.INVERSE | CHIINV | PRUEBA.CHI.INV | INV.CHI | INV.QUI | Returns the inverse of the one-tailed probability of the chi-squared distribution | |
| CHITEST | TEST.KHIDEUX | CHITEST | PRUEBA.CHI | TEST.CHI | TESTE.QUI | Returns the test for independence | |
| CHOOSE | CHOISIR | WAHL | ELEGIR | SCEGLI | ESCOLHER | Chooses a value from a list of values | |
| CLEAN | EPURAGE | SÄUBERN | LIMPIAR | LIBERA | TIRAR | Removes all nonprintable characters from text | |
| CODE | CODE | CODE | CODIGO | CODICE | CÓDIGO | Returns a numeric code for the first character in a text string | |
| COLUMN | COLONNE | SPALTE | COLUMNA | RIF.COLONNA | CO | Returns the column number of a reference | |
| COLUMNS | COLONNES | SPALTEN | COLUMNAS | COLONNE | COLS | Returns the number of columns in a reference | |
| COMBIN | COMBIN | KOMBINATIONEN | COMBINAT | COMBINAZIONE | COMBIN | Returns the number of combinations for a given number of objects | |
| CONCATENATE | CONCATENER | VERKETTEN | CONCATENAR | CONCATENA | CONCATENAR | Joins several text items into one text item | |
| CONFIDENCE | INTERVALLE.CONFIANCE | KONFIDENZ | INTERVALO.CONFIANZA | CONFIDENZA | INT.CONFIANÇA | Returns the confidence interval for a population mean | |
| CORREL | COEFFICIENT.CORRELATION | KORREL | COEF.DE.CORREL | CORRELAZIONE | CORRE | Returns the correlation coefficient between two data sets | |
| COS | COS | COS | COS | COS | COS | Returns the cosine of a number | |
| COSH | COSH | COSHYP | COSH | COSH | COSH | Returns the hyperbolic cosine of a number | |
| COUNT | NB | ANZAHL | CONTAR | CONTA.NUMERI | CONT.NÚM | Counts how many numbers are in the list of arguments | |
| COUNTA | NBVAL | ANZAHL2 | CONTARA | CONTA.VALORI | CONT.VALORES | Counts how many values are in the list of arguments | |
| COUNTBLANK | NB.VIDE | ANZAHLLEEREZELLEN | CONTAR.BLANCO | CONTA.VUOTE | CONTAR.VAZIO | Counts blanks | |
| COUNTIF | NB.SI | ZÄHLENWENN | CONTAR.SI | CONTA.SE | CONT.SE | Counts values matching an expression | |
| COVAR | COVARIANCE | KOVAR | COVAR | COVARIANZA | COVAR | Returns covariance, the average of the products of paired deviations | |
| CRITBINOM | CRITERE.LOI.BINOMIALE | KRITBINOM | BINOM.CRIT | CRIT.BINOM | CRIT.BINOM | Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value | |
| DATE | DATE | DATUM | FECHA | DATA | DATA | Returns the serial number of a particular date | |
| DATEVALUE | DATEVAL | DATWERT | FECHANUMERO | DATA.VALORE | DATA.VALOR | Converts a date in the form of text to a serial number | |
| DAVERAGE | BDMOYENNE | DBMITTELWERT | BDPROMEDIO | DB.MEDIA | BDMÉDIA | Returns the average of selected database entries | |
| DAY | JOUR | TAG | DIA | GIORNO | DIA | Converts a serial number to a day of the month | |
| DAYS360 | JOURS360 | TAGE360 | DIAS360 | GIORNO360 | DIAS360 | Calculates the number of days between two dates based on a 360-day year | |
| DB | DB | GDA2 | DB | AMMORT.FISSO | BD | Returns the depreciation of an asset for a specified period by using the fixed-declining balance method | |
| DCOUNT | BDNB | DBANZAHL | BDCONTAR | DB.CONTA.NUMERI | BDCONTAR | Counts the cells that contain numbers in a database | |
| DCOUNTA | BDNBVAL | DBANZAHL2 | BDCONTARA | DB.CONTA.VALORI | BDCONTARA | Counts nonblank cells in a database | |
| DDB | DDB | GDA | DDB | AMMORT | BDD | Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify | |
| DEGREES | DEGRES | GRAD | GRADOS | GRADI | GRAUS | Converts radians to degrees | |
| DEVSQ | SOMME.CARRE.ECARTS | SUMQUADABW | DESVIA2 | DEV.Q | DESVQ | Returns the sum of squares of deviations | |
| DGET | BDLIRE | DBAUSZUG | BDEXTRAER | DB.VALORI | BDEXTRAIR | Extracts from a database a single record that matches the specified criteria | |
| DMAX | BDMAX | DBMAX | BDMAX | DB.MAX | BDMÁX | Returns the maximum value from selected database entries | |
| DMIN | BDMIN | DBMIN | BDMIN | DB.MIN | BDMÍN | Returns the minimum value from selected database entries | |
| DOLLAR | FRANC | DM | MONEDA | VALUTA | MOEDA | Converts a number to text, using the $ (dollar) currency format | |
| DPRODUCT | BDPRODUIT | DBPRODUKT | BDPRODUCTO | DB.PRODOTTO | BDMULTIP | Multiplies the values in a particular field of records that match the criteria in a database | |
| DSTDEV | BDECARTTYPE | DBSTDABW | BDDESVEST | DB.DEV.ST | BDEST | Estimates the standard deviation based on a sample of selected database entries | |
| DSTDEVP | BDECARTTYPEP | DBSTDABWN | BDDESVESTP | DB.DEV.ST.POP | BDDESVPA | Calculates the standard deviation based on the entire population of selected database entries | |
| DSUM | BDSOMME | DBSUMME | BDSUMA | DB.SOMMA | BDSOMA | Adds the numbers in the field column of records in the database that match the criteria | |
| DVAR | BDVAR | DBVARIANZ | BDVAR | DB.VAR | BDVAREST | Estimates variance based on a sample from selected database entries | |
| DVARP | BDVARP | DBVARIANZEN | BDVARP | DB.VAR.POP | BDVARP | Calculates variance based on the entire population of selected database entries | |
| ERROR.TYPE | TYPE.ERREUR | FEHLER.TYP | TIPO.DE.ERROR | ERRORE.TIPO | TIPO.ERRO | Returns a number corresponding to an error type | |
| EVEN | PAIR | GERADE | REDONDEA.PAR | PARI | PAR | Rounds a number up to the nearest even integer | |
| EXACT | EXACT | IDENTISCH | IGUAL | IDENTICO | EXATO | Checks to see if two text values are identical | |
| EXP | EXP | EXP | EXP | EXP | EXP | Returns e raised to the power of a given number | |
| EXPONDIST | LOI.EXPONENTIELLE | EXPONVERT | DISTR.EXP | DISTRIB.EXP | DISTEXPON | Returns the exponential distribution | |
| FACT | FACT | FAKULTÄT | FACT | FATTORIALE | FATORIA | Returns the factorial of a number | |
| FALSE | FAUX | FALSCH | FALSO | FALSO | FALSO | Returns the logical value of FALSE | |
| FDIST | LOI.F | FVERT | DISTR.F | DISTRIB.F | DISTF | Returns the F probability distribution | |
| FIND | TROUVE | FINDEN | ENCONTRAR | TROVA | PROCURAR | Finds one text value within another (case-sensitive) | |
| FINV | INSERVE.LOI.F | FINV | DISTR.F.INV | INV.F | INVF | Returns the inverse of the F probability distribution | |
| FISHER | FISHER | FISHER | FISHER | FISHER | FISHER | Returns the Fisher transformation | |
| FISHERINV | FISHER.INVERSE | FISHERINV | PRUEBA.FISHER.INV | INV.FISHER | FISHERINV | Returns the inverse of the Fisher transformation | |
| FIXED | CTXT | FEST | DECIMAL | FISSO | DEF.NÚM.DEC | Formats a number as text with a fixed number of decimals | |
| FLOOR | PLANCHER | UNTERGRENZE | MULTIPLO.INFERIOR | ARROTONDA.DIFETTO | ARREDMULTB | Rounds a number down, toward zero | |
| FORECAST | PREVISION | SCHÄTZER | PRONOSTICO | PREVISIONE | PREVISÃO | Returns a value along a linear trend | |
| FREQUENCY | FREQUENCE | HÄUFIGKEIT | FRECUENCIA | FREQUENZA | FREQÜÊNCIA | Returns a frequency distribution as a vertical array | |
| FTEST | TEST.F | FTEST | PRUEBA.F | TEST.F | TESTEF | Returns the result of an F-test | |
| FV | VC | ZW | VF | VAL.FUT | VF | Returns the future value of an investment | |
| GAMMADIST | LOI.GAMMA | GAMMAVERT | DISTR.GAMMA | DISTRIB.GAMMA | DISTGAMA | Returns the gamma distribution | |
| GAMMAINV | LOI.GAMMA.INVERSE | GAMMAINV | DISTR.GAMMA.INV | INV.GAMMA | INVGAMA | Returns the inverse of the gamma cumulative distribution | |
| GAMMALN | LNGAMMA | GAMMALN | GAMMA.LN | LN.GAMMA | LNGAMA | Returns the natural logarithm of the gamma function, G(x) | |
| GEOMEAN | MOYENNE.GEOMETRIQUE | GEOMITTEL | MEDIA.GEOM | MEDIA.GEOMETRICA | MÉDIA.GEOMÉTRICA | Returns the geometric mean | |
| GETPIVOTDATA | LIREDONNEESTABCROISDYNAMIQUE | GETPIVOTDATA | GETPIVOTDATA | GETPIVOTDATA | GETPIVOTDATA | ||
| GROWTH | CROISSANCE | VARIATION | CRECIMIENTO | CRESCITA | CRESCIMENTO | Returns values along an exponential trend | |
| HARMEAN | MOYENNE.HARMONIQUE | HARMITTEL | MEDIA.ARMO | MEDIA.ARMONICA | MÉDIA.HARMÔNICA | Returns the harmonic mean | |
| HLOOKUP | RECHERCHEH | WVERWEIS | BUSCARH | CERCA.ORIZZ | PROCH | Looks in the top row of an array and returns the value of the indicated cell | |
| HOUR | HEURE | STUNDE | HORA | ORA | HORA | Converts a serial number to an hour | |
| HYPERLINK | LIEN_HYPERTEXTE | HYPERLINK | HYPERLINK | HYPERLINK | HYPERLINK | Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet | |
| HYPGEOMDIST | LOI.HYPERGEOMETRIQUE | HYPGEOMVERT | DISTR.HIPERGEOM | DISTRIB.IPERGEOM | DIST.HIPERGEOM | Returns the hypergeometric distribution | |
| IF | SI | WENN | SI | SE | SE | Specifies a logical test to perform | |
| INDEX | INDEX | INDEX | INDICE | INDICE | ÍNDICE | Uses an index to choose a value from a reference or array | |
| INDIRECT | INDIRECT | INDIREKT | INDIRECTO | INDIRETTO | INDIRETO | Returns a reference indicated by a text value | |
| INFO | INFO | INFO | INFO | AMBIENTE.INFO | INFORMAÇÃO | Returns information about the current operating environment | |
| INT | ENT | GANZZAHL | ENTERO | INT | INT | Rounds a number down to the nearest integer | |
| INTERCEPT | ORDONNEE.ORIGINE | ACHSENABSCHNITT | INTERSECCION | INTERCETTA | INTERCEPÇÃO | Returns the intercept of the linear regression line | |
| INTERSECT | INTERSECTION | INTERSECT | INTERSECT | INTERSECT | INTERSECT | Returns the logical intersection of an arbitrary number of ranges | |
| IPMT | INTPER | ZINSZ | PAGOINT | INTERESSI | IPGTO | Returns the interest payment for an investment for a given period | |
| IRR | TRI | IKV | TIR | TIR.COST | TIR | Returns the internal rate of return for a series of cash flows | |
| ISBLANK | ESTVIDE | ISTLEER | ESBLANCO | VAL.VUOTO | ÉCÉL.VAZIA | Returns TRUE if the value is blank | |
| ISERR | ESTERR | ISTFEHL | ESERR | VAL.ERR | ÉERRO | Returns TRUE if the value is any error value except #N/A | |
| ISERROR | ESTERREUR | ISTFEHLER | ESERROR | VAL.ERRORE | ÉERROS | Returns TRUE if the value is any error value | |
| ISLOGICAL | ESTLOGIQUE | ISTLOG | ESLOGICO | VAL.LOGICO | ÉLÓGICO | Returns TRUE if the value is a logical value | |
| ISNA | ESTNA | ISTNV | ESNOD | VAL.NON.DISP | É.NÃO.DISP | Returns TRUE if the value is the #N/A error value | |
| ISNONTEXT | ESTNONTEXTE | ISTKTEXT | ESNOTEXTO | VAL.NON.TESTO | É.NÃO.TEXTO | Returns TRUE if the value is not text | |
| ISNUMBER | ESTNUM | ISTZAHL | ESNUMERO | VAL.NUMERO | ÉNÚM | Returns TRUE if the value is a number | |
| ISPMT | ISPMT | ISPMT | INT.PAGO.DIR | ISPMT | ÉPGTO | Calculates the interest paid during a specific period of an investment | |
| ISREF | ESTREF | ISTBEZUG | ESREF | VAL.RIF | ÉREF | Returns TRUE if the value is a reference | |
| ISTEXT | ESTTEXTE | ISTTEXT | ESTEXTO | VAL.TESTO | ÉTEXTO | Returns TRUE if the value is text | |
| KURT | KURTOSIS | KURT | CURTOSIS | CURTOSI | CURT | Returns the kurtosis of a data set | |
| LARGE | GRANDE.VALEUR | KGRÖSSTE | K.ESIMO.MAYOR | GRANDE | MAIOR | Returns the k-th largest value in a data set | |
| LEFT | GAUCHE | LINKS | IZQUIERDA | SINISTRA | ESQUERDA | Returns the leftmost characters from a text value | |
| LEN | NBCAR | LÄNGE | LARGO | LUNGHEZZA | NÚM.CARACT | Returns the number of characters in a text string | |
| LINEST | DROITEREG | RGP | ESTIMACION.LINEAL | REGR.LIN | PROJ.LIN | Returns the parameters of a linear trend | |
| LN | LN | LN | LN | LN | LN | Returns the natural logarithm of a number | |
| LOG | LOG | LOG | LOG | LOG | LOG | Returns the logarithm of a number to a specified base | |
| LOG10 | LOG10 | LOG10 | LOG10 | LOG10 | LOG10 | Returns the base-10 logarithm of a number | |
| LOGEST | LOGREG | RKP | ESTIMACION.LOGARITMICA | REGR.LOG | PROJ.LOG | Returns the parameters of an exponential trend | |
| LOGINV | LOI.LOGNORMALE.INVERSE | LOGINV | DISTR.LOG.INV | INV.LOGNORM | INVLOG | Returns the inverse of the lognormal distribution | |
| LOGNORMDIST | LOI.LOGNORMALE | LOGNORMVERT | DISTR.LOG.NORM | DISTRIB.LOGNORM | DIST.LOGNORMA | Returns the cumulative lognormal distribution | |
| LOOKUP | RECHERCHE | VERWEIS | BUSCAR | CERCA | PROC | Looks up values in a vector or array | |
| LOWER | MINUSCULE | KLEIN | MINUSC | MINUSC | MINÚSCULA | Converts text to lowercase | |
| MATCH | EQUIV | VERGLEICH | COINCIDIR | CONFRONTA | CORRESP | Looks up values in a reference or array | |
| MAX | MAX | MAX | MAX | MAX | MÁXIMO | Returns the maximum value in a list of arguments | |
| MAXA | MAXA | MAXA | MAXA | MAXA | MÁXIMOA | Returns the maximum value in a list of arguments, including numbers, text, and logical values | |
| MDETERM | DETERMAT | MDET | MDETERM | MATR.DETERM | MATRIZ.DETERM | Returns the matrix determinant of an array | |
| MEDIAN | MEDIANE | MEDIAN | MEDIANA | MEDIANA | MED | Returns the median of the given numbers | |
| MID | STXT | TEIL | EXTRAE | STRINGA.ESTRAI | EXT.TEXTO | Returns a specific number of characters from a text string starting at the position you specify | |
| MIN | MIN | MIN | MIN | MIN | MÍNIMO | Returns the minimum value in a list of arguments | |
| MINA | MINA | MINA | MINA | MINA | MÍNIMOA | Returns the smallest value in a list of arguments, including numbers, text, and logical values | |
| MINUTE | MINUTE | MINUTE | MINUTO | MINUTO | MINUTO | Converts a serial number to a minute | |
| MINVERSE | INVERSEMAT | MINV | MINVERSA | MATR.INVERSA | MATRIZ.INVERSO | Returns the matrix inverse of an array | |
| MIRR | TRIM | QIKV | TIRM | TIR.VAR | MTIR | Returns the internal rate of return where positive and negative cash flows are financed at different rates | |
| MMULT | PRODUITMAT | MMULT | MMULT | MATR.PRODOTTO | MATRIZ.MULT | Returns the matrix product of two arrays | |
| MOD | MOD | REST | RESIDUO | RESTO | MOD | Returns the remainder from division | |
| MODE | MODE | MODALWERT | MODA | MODA | MODO | Returns the most common value in a data set | |
| MONTH | MOIS | MONAT | MES | MESE | MÊS | Converts a serial number to a month | |
| N | N | N | N | NUM | N | Returns a value converted to a number | |
| NA | NA | NV | NOD | NON.DISP | NÃO.DISP | Returns the error value #N/A | |
| NEGBINOMDIST | LOI.BINOMIALE.NEG | NEGBINOMVERT | NEGBINOMDIST | DISTRIB.BINOM.NEG | DIST.BIN.NEG | Returns the negative binomial distribution | |
| NORMDIST | LOI.NORMALE | NORMVERT | DISTR.NORM | DISTRIB.NORM | DIST.NORM | Returns the normal cumulative distribution | |
| NORMINV | LOI.NORMALE.INVERSE | NORMINV | DISTR.NORM.INV | INV.NORM | INV.NORM | Returns the inverse of the normal cumulative distribution | |
| NORMSDIST | LOI.NORMALE.STANDARD | STANDNORMVERT | DISTR.NORM.ESTAND | DISTRIB.NORM.ST | DIST.NORMP | Returns the standard normal cumulative distribution | |
| NORMSINV | LOI.NORMALE.STANDARD.INVERSE | STANDNORMINV | DISTR.NORM.ESTAND.INV | INV.NORM.ST | INV.NORMP | Returns the inverse of the standard normal cumulative distribution | |
| NOT | NON | NICHT | NO | NON | NÃO | Reverses the logic of its argument | |
| NOW | MAINTENANT | JETZT | AHORA | ADESSO | AGORA | Returns the serial number of the current date and time | |
| NPER | NPM | ZZR | NPER | NUM.RATE | NPER | Returns the number of periods for an investment | |
| NPV | VAN | NBW | VNA | VAN | VP | Returns the net present value of an investment based on a series of periodic cash flows and a discount rate | |
| ODD | IMPAIR | UNGERADE | REDONDEA.IMPAR | DISPARI | ÍMPAR | Rounds a number up to the nearest odd integer | |
| OFFSET | DECALER | BEREICH.VERSCHIEBEN | DESREF | SCARTO | DESLOC | Returns a reference offset from a given reference | |
| OR | OU | ODER | O | O | OU | Returns TRUE if any argument is TRUE | |
| PEARSON | PEARSON | PEARSON | PEARSON | PEARSON | PEARSON | Returns the Pearson product moment correlation coefficient | |
| PERCENTILE | CENTILE | QUANTIL | PERCENTIL | PERCENTILE | PERCENTI | Returns the k-th percentile of values in a range | |
| PERCENTRANK | RANG.POURCENTAGE | QUANTILSRANG | RANGO.PERCENTIL | PERCENT.RANGO | ORDEM.PORCENTUA | Returns the percentage rank of a value in a data set | |
| PERMUT | PERMUTATION | VARIATIONEN | PERMUTACIONES | PERMUTAZIONE | PERMUT | Returns the number of permutations for a given number of objects | |
| PI | PI | PI | PI | PI.GRECO | PI | Returns the value of pi | |
| PMT | VPM | RMZ | PAGO | RATA | PGTO | Returns the periodic payment for an annuity | |
| POISSON | LOI.POISSON | POISSON | POISSON | POISSON | POISSON | Returns the Poisson distribution | |
| POWER | PUISSANCE | POTENZ | POTENCIA | POTENZA | POTÊNCIA | Returns the result of a number raised to a power | |
| PPMT | PRINCPER | KAPZ | PAGOPRIN | P.RATA | PPGTO | Returns the payment on the principal for an investment for a given period | |
| PROB | PROBABILITE | WAHRSCHBEREICH | PROBABILIDAD | PROBABILITÀ | PROB | Returns the probability that values in a range are between two limits | |
| PRODUCT | PRODUIT | PRODUKT | PRODUCTO | PRODOTTO | MULT | Multiplies its arguments | |
| PROPER | NOMPROPRE | GROSS2 | NOMPROPIO | MAIUSC.INIZ | PRI.MAIÚSCULA | Capitalizes the first letter in each word of a text value | |
| PV | VA | BW | VA | VA | VP | Returns the present value of an investment | |
| QUARTILE | QUARTILE | QUARTILE | CUARTIL | QUARTILE | QUARTI | Returns the quartile of a data set | |
| RADIANS | RADIANS | RADIANT | RADIANES | RADIANTI | RADIANOS | Converts degrees to radians | |
| RAND | ALEA | ZUFALLSZAHL | ALEATORIO | CASUALE | ALEATÓRIO | Returns a random number between 0 and 1 | |
| RANK | RANG | RANG | JERARQUIA | RANGO | ORDEM | Returns the rank of a number in a list of numbers | |
| RATE | TAUX | ZINS | TASA | TASSO | TAXA | Returns the interest rate per period of an annuity | |
| REPLACE | REMPLACER | ERSETZEN | REEMPLAZAR | RIMPIAZZA | MUDAR | Replaces characters within text | |
| REPT | REPT | WIEDERHOLEN | REPETIR | RIPETI | REPETIR | Repeats text a given number of times | |
| RIGHT | DROITE | RECHTS | DERECHA | DESTRA | DIREITA | Returns the rightmost characters from a text value | |
| ROMAN | ROMAIN | RÖMISCH | NUMERO.ROMANO | ROMANO | ROMANO | Converts an arabic numeral to roman, as text | |
| ROUND | ARRONDI | RUNDEN | REDONDEAR | ARROTONDA | ARRED | Rounds a number to a specified number of digits | |
| ROUNDDOWN | ARRONDI.INF | ABRUNDEN | REDONDEAR.MENOS | ARROTONDA.PER.DIF | ARREDONDAR.PARA.BAIXO | Rounds a number down, toward zero | |
| ROUNDUP | ARRONDI.SUP | AUFRUNDEN | REDONDEAR.MAS | ARROTONDA.PER.ECC | ARREDONDAR.PARA.CIMA | Rounds a number up, away from zero | |
| ROW | LIGNE | ZEILE | FILA | RIF.RIGA | LIN | Returns the row number of a reference | |
| ROWS | LIGNES | ZEILEN | FILAS | RIGHE | LINS | Returns the number of rows in a reference | |
| RSQ | COEFFICIENT.DETERMINATION | BESTIMMTHEITSMASS | COEFICIENTE.R2 | RQ | RQUAD | Returns the square of the Pearson product moment correlation coefficient | |
| RTD | RTD | RTD | RTD | RTD | RTD | ||
| SEARCH | CHERCHE | SUCHEN | HALLAR | RICERCA | LOCALIZAR | Finds one text value within another (not case-sensitive) | |
| SECOND | SECONDE | SEKUNDE | SEGUNDO | SECONDO | SEGUNDO | Converts a serial number to a second | |
| SIGN | SIGNE | VORZEICHEN | SIGNO | SEGNO | SINA | Returns the sign of a number | |
| SIN | SIN | SIN | SENO | SEN | SEN | Returns the sine of the given angle | |
| SINH | SINH | SINHYP | SENOH | SENH | SENH | Returns the hyperbolic sine of a number | |
| SKEW | COEFFICIENT.ASYMETRIE | SCHIEFE | COEFICIENTE.ASIMETRIA | ASIMMETRIA | DISTORÇÃO | Returns the skewness of a distribution | |
| SLN | AMORLIN | LIA | SLN | AMMORT.COST | DPD | Returns the straight-line depreciation of an asset for one period | |
| SLOPE | PENTE | STEIGUNG | PENDIENTE | PENDENZA | INCLINAÇÃO | Returns the slope of the linear regression line | |
| SMALL | PETITE.VALEUR | KKLEINSTE | K.ESIMO.MENOR | PICCOLO | MENOR | Returns the k-th smallest value in a data set | |
| SQRT | RACINE | WURZEL | RAIZ | RADQ | RAIZ | Returns a positive square root | |
| STANDARDIZE | CENTREE.REDUITE | STANDARDISIERUNG | NORMALIZACION | NORMALIZZA | PADRONIZAR | Returns a normalized value | |
| STDEV | ECARTYPE | STABW | DESVEST | DEV.ST | DESVPAD | Estimates standard deviation based on a sample | |
| STDEVA | STDEVA | STABWA | DESVESTA | DEV.STA | DESVPADA | Estimates standard deviation based on a sample, including numbers, text, and logical values | |
| STDEVP | ECARTYPEP | STABWN | DESVESTP | DEV.ST.POP | DESVPADP | Calculates standard deviation based on the entire population | |
| STDEVPA | STDEVPA | STABWNA | DESVESTPA | DEV.ST.POPA | DESVPADPA | Calculates standard deviation based on the entire population, including numbers, text, and logical values | |
| STEYX | ERREUR.TYPE.XY | STFEHLERYX | ERROR.TIPICO.XY | ERR.STD.YX | EPADYX | Returns the standard error of the predicted y-value for each x in the regression | |
| SUBSTITUTE | SUBSTITUE | WECHSELN | SUSTITUIR | SOSTITUISCI | SUBSTITUIR | Substitutes new text for old text in a text string | |
| SUBTOTAL | SOUS.TOTAL | TEILERGEBNIS | SUBTOTALES | SUBTOTALE | SUBTOTA | Returns a subtotal in a list or database | |
| SUM | SOMME | SUMME | SUMA | SOMMA | SOMA | Adds its arguments | |
| SUMIF | SOMME.SI | SUMMEWENN | SUMAR.SI | SOMMA.SE | SOMASE | Adds the cells specified by a given criteria | |
| SUMPRODUCT | SOMMEPROD | SUMMENPRODUKT | SUMAPRODUCTO | MATR.SOMMA.PRODOTTO | SOMARPRODUTO | Returns the sum of the products of corresponding array components | |
| SUMSQ | SOMME.CARRES | QUADRATESUMME | SUMA.CUADRADOS | SOMMA.Q | SOMAQUAD | Returns the sum of the squares of the arguments | |
| SUMX2MY2 | SOMME.X2MY2 | SUMMEX2MY2 | SUMAX2MENOSY2 | SOMMA.DIFF.Q | SOMAX2DY2 | Returns the sum of the difference of squares of corresponding values in two arrays | |
| SUMX2PY2 | SOMME.X2PY2 | SUMMEX2PY2 | SUMAX2MASY2 | SOMMA.SOMMA.Q | SOMAX2SY2 | Returns the sum of the sum of squares of corresponding values in two arrays | |
| SUMXMY2 | SOMME.XMY2 | SUMMEXMY2 | SUMAXMENOSY2 | SOMMA.Q.DIFF | SOMAXMY2 | Returns the sum of squares of differences of corresponding values in two arrays | |
| SYD | SYD | DIA | SYD | AMMORT.ANNUO | SDA | Returns the sum-of-years' digits depreciation of an asset for a specified period | |
| T | T | T | T | T | T | Converts its arguments to text | |
| TAN | TAN | TAN | TAN | TAN | TAN | Returns the tangent of a number | |
| TANH | TANH | TANHYP | TANH | TANH | TANH | Returns the hyperbolic tangent of a number | |
| TDIST | LOI.STUDENT | TVERT | DISTR.T | DISTRIB.T | DISTT | Returns the Student's t-distribution | |
| TEXT | TEXTE | TEXT | TEXTO | TESTO | TEXTO | Formats a number and converts it to text | |
| TIME | TEMPS | ZEIT | NSHORA | ORARIO | TEMPO | Returns the serial number of a particular time | |
| TIMEVALUE | TEMPSVAL | ZEITWERT | HORANUMERO | ORARIO.VALORE | VALOR.TEMPO | Converts a time in the form of text to a serial number | |
| TINV | LOI.STUDENT.INVERSE | TINV | DISTR.T.INV | INV.T | INVT | Returns the inverse of the Student's t-distribution | |
| TODAY | AUJOURDHUI | HEUTE | HOY | OGGI | HOJE | Returns the serial number of today's date | |
| TRANSPOSE | TRANSPOSE | MTRANS | TRANSPONER | MATR.TRASPOSTA | TRANSPOR | Returns the transpose of an array | |
| TREND | TENDANCE | TREND | TENDENCIA | TENDENZA | TENDÊNCIA | Returns values along a linear trend | |
| TRIM | SUPPRESPACE | GLÄTTEN | ESPACIOS | ANNULLA.SPAZI | ARRUMAR | Removes spaces from text | |
| TRIMMEAN | MOYENNE.REDUITE | GESTUTZTMITTEL | MEDIA.ACOTADA | MEDIA.TRONCATA | MÉDIA.INTERNA | Returns the mean of the interior of a data set | |
| TRUE | VRAI | WAHR | VERDADERO | VERO | VERDADEIRO | Returns the logical value of TRUE | |
| TRUNC | TRONQUE | KÜRZEN | TRUNCAR | TRONCA | TRUNCAR | Truncates a number to an integer | |
| TTEST | TEST.STUDENT | TTEST | PRUEBA.T | TEST.T | TESTET | Returns the probability associated with a Student's t-test | |
| TYPE | TYPE | TYP | TIPO | TIPO | TIPO | Returns a number indicating the data type of a value | |
| UNION | UNION | UNION | UNION | UNION | UNION | Returns the logical union of an arbitrary number of ranges | |
| UPPER | MAJUSCULE | GROSS | MAYUSC | MAIUSC | MAIÚSCULA | Converts text to uppercase | |
| VALUE | CNUM | WERT | VALOR | VALORE | VALOR | Converts a text argument to a number | |
| VAR | VAR | VARIANZ | VAR | VAR | VAR | Estimates variance based on a sample | |
| VARA | VARA | VARIANZA | VARA | VARA | VARA | Estimates variance based on a sample, including numbers, text, and logical values | |
| VARP | VAR.P | VARIANZEN | VARP | VAR.POP | VARP | Calculates variance based on the entire population | |
| VARPA | VARPA | VARIANZENA | VARPA | VAR.POPA | VARPA | Calculates variance based on the entire population, including numbers, text, and logical values | |
| VDB | VDB | VDB | DVS | AMMORT.VAR | BDV | Returns the depreciation of an asset for a specified or partial period by using a declining balance method | |
| VLOOKUP | RECHERCHEV | SVERWEIS | BUSCARV | CERCA.VERT | PROCV | Looks in the first column of an array and moves across the row to return the value of a cell | |
| WEEKDAY | JOURSEM | WOCHENTAG | DIASEM | GIORNO.SETTIMANA | DIA.DA.SEMANA | Converts a serial number to a day of the week | |
| WEIBULL | LOI.WEIBULL | WEIBULL | DIST.WEIBULL | WEIBULL | WEIBUL | Returns the Weibull distribution | |
| YEAR | ANNEE | JAHR | AÑO | ANNO | ANO | Converts a serial number to a year | |
| ZTEST | TEST.Z | GTEST | PRUEBA.Z | TEST.Z | TESTEZ | Returns the one-tailed probability-value of a z-test |
All of functions above are supported by xlsgen for reading and writing.
Functions supported by the calculation engine (see the third column of the above table) : a subset of the over 200 built-in Excel functions are currently supported, and support for other functions is being added on a case by case basis. In addition, xlsgen does not currently provide calculation support for the following :
If, upon closing a workbook in Excel that was generated by xlsgen, Excel brings a prompt saying "Microsoft Excel recalculates formulas when opening files last saved by an earlier version of Excel (than the one currently running)", then you may avoid this behavior by setting the appropriate Excel target version. See here.
Detailed here.
xlsgen documentation. © ARsT Design all rights reserved.