Supported Formula Functions"
(15 intermediate revisions by the same user not shown) | |||
Line 28: | Line 28: | ||
| DAYS360 | | DAYS360 | ||
| <center>Y</center> | | <center>Y</center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | EOMONTH | ||
+ | | <center> </center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
Line 1,062: | Line 1,066: | ||
|- | |- | ||
! style="width:60%"| '''Function''' | ! style="width:60%"| '''Function''' | ||
− | |||
!style="width:20%;"| '''OSE''' | !style="width:20%;"| '''OSE''' | ||
− | |||
!style="width:20%"| '''EE''' | !style="width:20%"| '''EE''' | ||
− | |||
|- | |- | ||
− | | | + | |ABS |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |ACOS |
− | + | |<center>Y</center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |ACOSH | |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |ASIN | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |ASINH | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |ATAN |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |ATAN2 |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |ATANH |
− | + | |<center>Y</center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |CEILING | |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |COMBIN | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |COS | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |COSH |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |DEGREES |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |EVEN |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |EXP |
− | + | |<center>Y</center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |FACT | |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |FACTDOUBLE | ||
+ | |<center></center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |FLOOR | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |GCD |
− | + | |<center></center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |INT |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |LCM |
− | + | |<center></center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |LN |
− | + | |<center>Y</center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |LOG | |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |LOG10 | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |MDETERM | ||
+ | |<center></center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |MINVERSE |
− | + | |<center></center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |MMULT |
− | + | |<center></center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |MOD |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |MROUND |
− | + | |<center></center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |MULTINOMIAL | |
+ | |<center></center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |ODD | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |PI | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |POWER |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |PRODUCT |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |QUOTIENT |
− | + | |<center></center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |RADIANS |
− | + | |<center>Y</center> | |
− | | | + | |<center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | |RAND | |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |RANDBETWEEN | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |ROMAN | ||
+ | |<center></center> | ||
+ | |<center>Y</center> | ||
|- | |- | ||
− | | | + | |ROUND |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |ROUNDDOWN |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | |ROUNDUP |
− | + | |<center>Y</center> | |
− | + | |<center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | |SIGN |
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SIN | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SINH | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SQRT | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SQRTPI | ||
+ | |<center></center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUBTOTAL | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUM | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMIF | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMIFS | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMPRODUCT | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMSQ | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMX2MY2 | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMX2PY2 | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |SUMXMY2 | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |TAN | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |TANH | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |- | ||
+ | |TRUNC | ||
+ | |<center>Y</center> | ||
+ | |<center>Y</center> | ||
+ | |} | ||
− | + | == SUMPRODUCT == | |
− | < | + | <!-- ZSS-852 --> |
+ | since 3.7.0 | ||
+ | You can specify a condition for an array formula to just calculate partial cells in a given range. | ||
+ | For example, | ||
− | + | =SUMPRODUCT(--(A1:A3="John"),(B1:B3),(C1:C3)) | |
− | |||
− | |||
− | + | or simpler | |
− | + | =SUMPRODUCT((F12:F21=1)*(G12:G21="Z")*H12:H21) | |
− | |||
− | |||
− | |||
+ | = Statistical = | ||
+ | {| border="2" style="width:50%;" | ||
|- | |- | ||
− | | | + | ! style="width:60%"| '''Function''' |
− | |||
− | | | + | ! style="width:60%"| '''New Name since Excel 2010''' |
− | |||
− | | | + | !style="width:20%;"| '''OSE''' |
− | |||
− | | | + | !style="width:20%"| '''EE''' |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
− | | | + | | AVEDEV |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | AVERAGE |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | | AVERAGEA | |
− | | | + | | - |
− | <center>Y</center> | + | | <center></center> |
− | + | | <center>Y</center> | |
|- | |- | ||
− | | | + | | BETADIST |
− | + | | BETA.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | BETAINV |
− | + | | BETA.INV | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | BINOMDIST |
− | + | | BINOM.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CORREL |
− | + | | | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CRITBINOM |
− | + | | BINOM.INV | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CHIDIST |
− | + | | CHISQ.DIST.RT | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CHIINV |
− | + | | CHISQ.INV.RT | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | - |
− | + | | CHISQ.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | | - | |
− | | | + | | CHISQ.INV |
− | <center>Y</center> | + | | <center></center> |
− | + | | <center>Y</center> | |
|- | |- | ||
− | | | + | | COUNT |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | COUNTA |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | COUNTBLANK |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | COUNTIF |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | DEVSQ |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | EXPONDIST |
− | + | | EXPON.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | FDIST |
− | + | | F.DIST.RT | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | FINV |
− | + | | F.INV.RT | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | GAMMADIST |
− | + | | GAMMA.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | GAMMAINV |
− | + | | GAMMA.INV | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | GAMMALN |
− | + | | - | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | | GEOMEAN | |
− | | | + | | - |
− | <center>Y</center> | + | | <center></center> |
− | + | | <center>Y</center> | |
|- | |- | ||
− | | | + | | HARMEAN |
− | + | | - | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | HYPGEOMDIST |
− | + | | HYPGEOM.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | KURT |
− | + | | - | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | LARGE |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | MAX |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | MAXA |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | MEDIAN |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | MIN |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | + | |- |
− | + | | MINA | |
− | | | + | | - |
− | <center>Y</center> | + | | <center>Y</center> |
− | + | | <center>Y</center> | |
|- | |- | ||
− | | | + | | MODE |
− | + | | MODE.SNGL | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | NEGBINOMDIST |
− | + | | NEGBINOM.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | NORMDIST |
− | + | | NORM.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | NORMINV |
− | + | | NORM.INV | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | NORMSDIST |
− | + | | NORM.S.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | NORMSINV |
− | + | | NORM.S.INV | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | LOGNORMDIST |
− | + | | LOGNORM.DIST | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | LOGINV |
− | + | | LOGNORM.INV | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | POISSON |
− | + | | POISSON.DIST | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
|- | |- | ||
− | + | | RANK | |
− | + | | RANK.EQ | |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | |||
− | |||
|- | |- | ||
− | | | + | | SKEW |
− | + | | - | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | SLOPE |
− | + | | - | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | SMALL |
− | + | | - | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | STDEV |
− | + | | STDE.V | |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | - |
− | + | | T.DIST.2T | |
− | + | | <center></center> | |
− | | | + | | <center>Y</center> |
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | TDIST |
− | + | | T.DIST.RT | |
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | TINV | ||
+ | | T.INV.2T | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | VAR | ||
+ | | VAR.S | ||
+ | | <center>Y</center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | VARP | ||
+ | | VAR.P | ||
+ | | <center>Y</center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | WEIBULL | ||
+ | | WEIBULL.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |} | ||
− | + | * [https://support.office.com/en-us/article/What-s-New-Changes-made-to-Excel-functions-355d08c8-8358-4ecb-b6eb-e2e443e98aac?ui=en-US&rs=en-US&ad=US&fromAR=1#bm2 Microsoft change some statistical function names since Excel 2010], ZSS supports both function names listed above. | |
− | |||
− | + | = Text= | |
− | |||
+ | {| border="2" style="width:50%;" | ||
|- | |- | ||
− | | | + | ! style="width:60%"| '''Function''' |
− | |||
− | | | + | !style="width:20%;"| '''OSE''' |
− | |||
− | | | + | !style="width:20%"| '''EE''' |
− | |||
|- | |- | ||
− | | | + | | CHAR |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | + | |- |
− | <center>Y</center> | + | | CLEAN |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CODE |
− | + | | <center></center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | CONCATENATE |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | DOLLAR |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | EXACT |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | FIND |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | FIXED |
− | + | | <center></center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | LEFT |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | LEN |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | + | |- |
− | <center></center> | + | | LOWER |
− | + | | <center>Y</center> | |
− | | | + | | <center>Y</center> |
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | MID |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | PROPER |
− | + | | <center></center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | REPLACE |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | REPT |
− | + | | <center></center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | RIGHT |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center></center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | SEARCH |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | SUBSTITUTE |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | T |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | TEXT |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | TRIM |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | UPPER |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | | | ||
− | <center>Y</center> | ||
− | |||
− | | | ||
− | <center>Y</center> | ||
− | |||
|- | |- | ||
− | | | + | | VALUE |
− | + | | <center>Y</center> | |
− | + | | <center>Y</center> | |
− | + | |} | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | | | ||
− | |||
− | |||
− | |||
− | <center>Y</center> | ||
− | |||
− | |||
− | | | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | <center>Y</center> | ||
− | |||
− | |} | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | = Not Supported Functions = | |
− | + | ZSS doesn't support Cube, Database, and Web functions. | |
− | + | For current open issues that supported functions have, please refer to [http://tracker.zkoss.org/secure/IssueNavigator.jspa?mode=hide&requestId=12600 our tracker]. | |
− | + | {{ZKSpreadsheetEssentialsPageFooter}} |
Latest revision as of 01:21, 26 November 2019
Here we list all ZK Spreadsheet supported functions in OSE and EE:
Date & Time
Function | OSE | EE |
---|---|---|
DATE | ||
DATEVALUE | ||
DAY | ||
DAYS360 | ||
EOMONTH | ||
HOUR | ||
MINUTE | ||
MONTH | ||
NETWORKDAYS | ||
NOW | ||
SECOND | ||
TIME | ||
TODAY | ||
WEEKDAY | ||
WORKDAY | ||
YEAR | ||
YEARFRAC |
Engineering
Function | OSE | EE |
---|---|---|
BESSELI |
|
|
BESSELJ |
|
|
BESSELK |
|
|
BESSELY |
|
|
BIN2DEC |
|
|
BIN2HEX |
|
|
BIN2OCT |
|
|
COMPLEX |
|
|
DEC2BIN |
|
|
DEC2HEX |
|
|
DEC2OCT |
|
|
DELTA |
|
|
ERF |
|
|
ERFC |
|
|
GESTEP |
|
|
HEX2BIN |
|
|
HEX2DEC |
|
|
HEX2OCT |
|
|
IMABS |
|
|
IMAGINARY |
|
|
IMARGUMENT |
|
|
IMCONJUGATE |
|
|
IMCOS |
|
|
IMDIV |
|
|
IMEXP |
|
|
IMLN |
|
|
IMLOG10 |
|
|
IMLOG2 |
|
|
IMPOWER |
|
|
IMPRODUCT |
|
|
IMREAL |
|
|
IMSIN |
|
|
IMSQRT |
|
|
IMSUB |
|
|
IMSUM |
|
|
OCT2BIN |
|
|
OCT2DEC |
|
|
OCT2HEX |
|
|
Financial
Function | OSE | EE |
---|---|---|
ACCRINT | ||
ACCRINTM | ||
AMORDEGRC | ||
AMORLINC | ||
COUPDAYBS | ||
COUPDAYS | ||
COUPDAYSNC | ||
COUPNCD | ||
COUPNUM | ||
COUPPCD | ||
CUMIPMT | ||
CUMPRINC | ||
DB | ||
DDB | ||
DISC | ||
DOLLARDE | ||
DOLLARFR | ||
DURATION | ||
EFFECT | ||
FV | ||
FVSCHEDULE | ||
INTRATE | ||
IPMT | ||
IRR | ||
NOMINAL | ||
NPER | ||
NPV | ||
PMT | ||
PPMT | ||
PRICE | ||
PRICEDISC | ||
PRICEMAT | ||
PV | ||
RATE | ||
RECEIVED | ||
SLN | ||
SYD | ||
TBILLEQ | ||
TBILLPRICE | ||
TBILLYIELD | ||
XNPV | ||
YIELD | ||
YIELDDISC | ||
YIELDMAT |
Info
Function | OSE | EE |
---|---|---|
ERROR.TYPE |
|
|
ISBLANK |
|
|
ISERR |
|
|
ISERROR |
|
|
ISEVEN |
|
|
ISLOGICAL |
|
|
ISNA |
|
|
ISNONTEXT |
|
|
ISNUMBER |
|
|
ISODD |
|
|
ISREF |
|
|
ISTEXT |
|
|
N |
|
|
NA |
|
|
TYPE |
|
|
Logical
Function | OSE | EE |
---|---|---|
AND |
|
|
FALSE |
|
|
IF |
|
|
IFERROR |
|
|
NOT |
|
|
OR |
|
|
TRUE |
|
|
Lookup & Reference
Function | OSE | EE |
---|---|---|
ADDRESS |
|
|
CHOOSE |
|
|
COLUMN |
|
|
COLUMNS |
|
|
HLOOKUP |
|
|
HYPERLINK |
|
|
INDEX |
|
|
INDIRECT |
|
|
LOOKUP |
|
|
MATCH |
|
|
OFFSET |
|
|
ROW |
|
|
ROWS |
|
|
VLOOKUP |
|
|
Mathematical
Function | OSE | EE |
---|---|---|
ABS | ||
ACOS | ||
ACOSH | ||
ASIN | ||
ASINH | ||
ATAN | ||
ATAN2 | ||
ATANH | ||
CEILING | ||
COMBIN | ||
COS | ||
COSH | ||
DEGREES | ||
EVEN | ||
EXP | ||
FACT | ||
FACTDOUBLE | ||
FLOOR | ||
GCD | ||
INT | ||
LCM | ||
LN | ||
LOG | ||
LOG10 | ||
MDETERM | ||
MINVERSE | ||
MMULT | ||
MOD | ||
MROUND | ||
MULTINOMIAL | ||
ODD | ||
PI | ||
POWER | ||
PRODUCT | ||
QUOTIENT | ||
RADIANS | ||
RAND | ||
RANDBETWEEN | ||
ROMAN | ||
ROUND | ||
ROUNDDOWN | ||
ROUNDUP | ||
SIGN | ||
SIN | ||
SINH | ||
SQRT | ||
SQRTPI | ||
SUBTOTAL | ||
SUM | ||
SUMIF | ||
SUMIFS | ||
SUMPRODUCT | ||
SUMSQ | ||
SUMX2MY2 | ||
SUMX2PY2 | ||
SUMXMY2 | ||
TAN | ||
TANH | ||
TRUNC |
SUMPRODUCT
since 3.7.0
You can specify a condition for an array formula to just calculate partial cells in a given range. For example,
=SUMPRODUCT(--(A1:A3="John"),(B1:B3),(C1:C3))
or simpler
=SUMPRODUCT((F12:F21=1)*(G12:G21="Z")*H12:H21)
Statistical
Function | New Name since Excel 2010 | OSE | EE |
---|---|---|---|
AVEDEV | - | ||
AVERAGE | - | ||
AVERAGEA | - | ||
BETADIST | BETA.DIST | ||
BETAINV | BETA.INV | ||
BINOMDIST | BINOM.DIST | ||
CORREL | |||
CRITBINOM | BINOM.INV | ||
CHIDIST | CHISQ.DIST.RT | ||
CHIINV | CHISQ.INV.RT | ||
- | CHISQ.DIST | ||
- | CHISQ.INV | ||
COUNT | - | ||
COUNTA | - | ||
COUNTBLANK | - | ||
COUNTIF | - | ||
DEVSQ | - | ||
EXPONDIST | EXPON.DIST | ||
FDIST | F.DIST.RT | ||
FINV | F.INV.RT | ||
GAMMADIST | GAMMA.DIST | ||
GAMMAINV | GAMMA.INV | ||
GAMMALN | - | ||
GEOMEAN | - | ||
HARMEAN | - | ||
HYPGEOMDIST | HYPGEOM.DIST | ||
KURT | - | ||
LARGE | - | ||
MAX | - | ||
MAXA | - | ||
MEDIAN | - | ||
MIN | - | ||
MINA | - | ||
MODE | MODE.SNGL | ||
NEGBINOMDIST | NEGBINOM.DIST | ||
NORMDIST | NORM.DIST | ||
NORMINV | NORM.INV | ||
NORMSDIST | NORM.S.DIST | ||
NORMSINV | NORM.S.INV | ||
LOGNORMDIST | LOGNORM.DIST | ||
LOGINV | LOGNORM.INV | ||
POISSON | POISSON.DIST | ||
RANK | RANK.EQ | ||
SKEW | - | ||
SLOPE | - | ||
SMALL | - | ||
STDEV | STDE.V | ||
- | T.DIST.2T | ||
TDIST | T.DIST.RT | ||
TINV | T.INV.2T | ||
VAR | VAR.S | ||
VARP | VAR.P | ||
WEIBULL | WEIBULL.DIST |
- Microsoft change some statistical function names since Excel 2010, ZSS supports both function names listed above.
Text
Function | OSE | EE |
---|---|---|
CHAR | ||
CLEAN | ||
CODE | ||
CONCATENATE | ||
DOLLAR | ||
EXACT | ||
FIND | ||
FIXED | ||
LEFT | ||
LEN | ||
LOWER | ||
MID | ||
PROPER | ||
REPLACE | ||
REPT | ||
RIGHT | ||
SEARCH | ||
SUBSTITUTE | ||
T | ||
TEXT | ||
TRIM | ||
UPPER | ||
VALUE |
Not Supported Functions
ZSS doesn't support Cube, Database, and Web functions.
For current open issues that supported functions have, please refer to our tracker.
All source code listed in this book is at Github.