Supported Formula Functions"
(11 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,302: | Line 1,306: | ||
|} | |} | ||
− | + | == SUMPRODUCT == | |
− | |||
<!-- ZSS-852 --> | <!-- ZSS-852 --> | ||
since 3.7.0 | since 3.7.0 | ||
Line 1,318: | Line 1,321: | ||
|- | |- | ||
! style="width:60%"| '''Function''' | ! style="width:60%"| '''Function''' | ||
+ | |||
+ | ! style="width:60%"| '''New Name since Excel 2010''' | ||
!style="width:20%;"| '''OSE''' | !style="width:20%;"| '''OSE''' | ||
Line 1,325: | Line 1,330: | ||
|- | |- | ||
| AVEDEV | | AVEDEV | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| AVERAGE | | AVERAGE | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| AVERAGEA | | AVERAGEA | ||
+ | | - | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | BETADIST | ||
+ | | BETA.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | BETAINV | ||
+ | | BETA.INV | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| BINOMDIST | | BINOMDIST | ||
+ | | BINOM.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | CORREL | ||
+ | | | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | CRITBINOM | ||
+ | | BINOM.INV | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| CHIDIST | | CHIDIST | ||
+ | | CHISQ.DIST.RT | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| CHIINV | | CHIINV | ||
+ | | CHISQ.INV.RT | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | - | ||
+ | | CHISQ.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | - | ||
+ | | CHISQ.INV | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| COUNT | | COUNT | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| COUNTA | | COUNTA | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| COUNTBLANK | | COUNTBLANK | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| COUNTIF | | COUNTIF | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| DEVSQ | | DEVSQ | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| EXPONDIST | | EXPONDIST | ||
+ | | EXPON.DIST | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| FDIST | | FDIST | ||
+ | | F.DIST.RT | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| FINV | | FINV | ||
+ | | F.INV.RT | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| GAMMADIST | | GAMMADIST | ||
+ | | GAMMA.DIST | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| GAMMAINV | | GAMMAINV | ||
+ | | GAMMA.INV | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| GAMMALN | | GAMMALN | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| GEOMEAN | | GEOMEAN | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| HARMEAN | | HARMEAN | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| HYPGEOMDIST | | HYPGEOMDIST | ||
+ | | HYPGEOM.DIST | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| KURT | | KURT | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| LARGE | | LARGE | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MAX | | MAX | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MAXA | | MAXA | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MEDIAN | | MEDIAN | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MIN | | MIN | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MINA | | MINA | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| MODE | | MODE | ||
+ | | MODE.SNGL | ||
| <center>Y</center> | | <center>Y</center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | NEGBINOMDIST | ||
+ | | NEGBINOM.DIST | ||
+ | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| NORMDIST | | NORMDIST | ||
+ | | NORM.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | NORMINV | ||
+ | | NORM.INV | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | NORMSDIST | ||
+ | | NORM.S.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | NORMSINV | ||
+ | | NORM.S.INV | ||
| <center></center> | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | LOGNORMDIST | ||
+ | | LOGNORM.DIST | ||
+ | | <center></center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | LOGINV | ||
+ | | LOGNORM.INV | ||
+ | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| POISSON | | POISSON | ||
+ | | POISSON.DIST | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| RANK | | RANK | ||
+ | | RANK.EQ | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| SKEW | | SKEW | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| SLOPE | | SLOPE | ||
+ | | - | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| SMALL | | SMALL | ||
+ | | - | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| STDEV | | STDEV | ||
+ | | STDE.V | ||
| <center>Y</center> | | <center>Y</center> | ||
+ | | <center>Y</center> | ||
+ | |- | ||
+ | | - | ||
+ | | T.DIST.2T | ||
+ | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| TDIST | | TDIST | ||
+ | | T.DIST.RT | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| TINV | | TINV | ||
+ | | T.INV.2T | ||
| <center></center> | | <center></center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| VAR | | VAR | ||
+ | | VAR.S | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| VARP | | VARP | ||
+ | | VAR.P | ||
| <center>Y</center> | | <center>Y</center> | ||
| <center>Y</center> | | <center>Y</center> | ||
|- | |- | ||
| WEIBULL | | WEIBULL | ||
+ | | WEIBULL.DIST | ||
| <center></center> | | <center></center> | ||
| <center>Y</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= | = Text= | ||
Line 1,590: | Line 1,702: | ||
− | + | = 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.