Supported Formula Functions
Here we list all supported and built-in functions in Keikai OSE and EE. All functions listed here are available in Keikai EE. But only some of them are available in Keikai OSE.
Check Microsoft Excel function list for details.
Date & Time (25)
| Function | OSE | EE |
|---|---|---|
| DATE | Y | Y |
| DATEDIF5.8 | Y | |
| DATEVALUE | Y | |
| DAY | Y | Y |
| DAYS6.0 | Y | |
| DAYS360 | Y | Y |
| EDATE6.0 | Y | |
| EOMONTH | Y | |
| HOUR | Y | Y |
| ISOWEEKNUM6.3 | Y | |
| MINUTE | Y | Y |
| MONTH | Y | Y |
| NETWORKDAYS | Y | Y |
| NETWORKDAYS.INTL6.3 | Y | |
| NOW | Y | Y |
| SECOND | Y | Y |
| TIME | Y | Y |
| TIMEVALUE6.0 | Y | |
| TODAY | Y | Y |
| WEEKDAY | Y | Y |
| WEEKNUM6.0 | Y | |
| WORKDAY | Y | Y |
| WORKDAY.INTL6.0 | Y | |
| YEAR | Y | Y |
| YEARFRAC | Y | Y |
Engineering (54)
| Function | OSE | EE |
|---|---|---|
| BESSELI | Y | |
| BESSELJ | Y | |
| BESSELK | Y | |
| BESSELY | Y | |
| BITAND6.3 | Y | |
| BITLSHIFT6.3 | Y | |
| BITOR6.3 | Y | |
| BITRSHIFT6.3 | Y | |
| BITXOR6.3 | Y | |
| BIN2DEC | Y | |
| BIN2HEX | Y | |
| BIN2OCT | Y | |
| COMPLEX | Y | |
| CONVERT6.3 | Y | |
| DEC2BIN | Y | |
| DEC2HEX | Y | |
| DEC2OCT | Y | |
| DELTA | Y | |
| ERF | Y | |
| ERF.PRECISE6.3 | Y | |
| ERFC | Y | |
| ERFC.PRECISE6.3 | Y | |
| GESTEP | Y | |
| HEX2BIN | Y | |
| HEX2DEC | Y | |
| HEX2OCT | Y | |
| IMABS | Y | |
| IMAGINARY | Y | |
| IMARGUMENT | Y | |
| IMCONJUGATE | Y | |
| IMCOS | Y | |
| IMCOSH6.3 | Y | |
| IMCOT6.3 | Y | |
| IMCSC6.3 | Y | |
| IMCSCH6.3 | Y | |
| IMDIV | Y | |
| IMEXP | Y | |
| IMLN | Y | |
| IMLOG10 | Y | |
| IMLOG2 | Y | |
| IMPOWER | Y | |
| IMPRODUCT | Y | |
| IMREAL | Y | |
| IMSEC6.3 | Y | |
| IMSECH6.3 | Y | |
| IMSIN | Y | |
| IMSINH6.3 | Y | |
| IMSQRT | Y | |
| IMSUB | Y | |
| IMTAN6.3 | Y | |
| IMSUM | Y | |
| OCT2BIN | Y | |
| OCT2DEC | Y | |
| OCT2HEX | Y |
Financial (55)
| Function | OSE | EE |
|---|---|---|
| ACCRINT | Y | |
| ACCRINTM | Y | |
| AMORDEGRC | Y | |
| AMORLINC | Y | |
| COUPDAYBS | Y | |
| COUPDAYS | Y | |
| COUPDAYSNC | Y | |
| COUPNCD | Y | |
| COUPNUM | Y | |
| COUPPCD | Y | |
| CUMIPMT | Y | |
| CUMPRINC | Y | |
| DB | Y | |
| DDB | Y | |
| DISC | Y | |
| DOLLARDE | Y | |
| DOLLARFR | Y | |
| DURATION | Y | |
| EFFECT | Y | |
| FV | Y | Y |
| FVSCHEDULE | Y | |
| INTRATE | Y | |
| ISPMT6.3 | Y | |
| IPMT | Y | |
| IRR | Y | Y |
| MDURATION6.3 | Y | |
| MIRR6.0 | Y | |
| NOMINAL | Y | |
| ODDFPRICE6.3 | Y | |
| ODDFYIELD6.3 | Y | |
| ODDLPRICE6.3 | Y | |
| ODDLYIELD6.3 | Y | |
| NPER | Y | Y |
| NPV | Y | Y |
| PDURATION6.3 | Y | |
| PMT | Y | Y |
| PPMT | Y | |
| PRICE | Y | |
| PRICEDISC | Y | |
| PRICEMAT | Y | |
| PV | Y | Y |
| RATE | Y | Y |
| RECEIVED | Y | |
| RRI6.3 | Y | |
| SLN | Y | |
| SYD | Y | |
| TBILLEQ | Y | |
| TBILLPRICE | Y | |
| TBILLYIELD | Y | |
| VDB | Y | |
| XIRR6.3 | Y | |
| XNPV | Y | |
| YIELD | Y | |
| YIELDDISC | Y | |
| YIELDMAT | Y |
Information (21)
| Function | OSE | EE |
|---|---|---|
| CELL6.0 | Y | |
| ERROR.TYPE | Y | Y |
| FORMULATEXT | Y | |
| INFO7.0 | Y | |
| ISBLANK | Y | Y |
| ISERR | Y | |
| ISERROR | Y | Y |
| ISEVEN | Y | Y |
| ISFORMULA | Y | |
| ISLOGICAL | Y | Y |
| ISNA | Y | Y |
| ISNONTEXT | Y | Y |
| ISNUMBER | Y | Y |
| ISODD | Y | Y |
| ISREF | Y | Y |
| ISTEXT | Y | Y |
| SHEET | Y | |
| SHEETS6.3 | Y | |
| N | Y | |
| NA | Y | Y |
| TYPE | Y |
Logical (9)
| Function | OSE | EE |
|---|---|---|
| AND | Y | Y |
| FALSE | Y | Y |
| IF | Y | Y |
| IFERROR | Y | |
| LET6.3 | Y | |
| NOT | Y | Y |
| OR | Y | Y |
| TRUE | Y | Y |
| XOR6.3 | Y |
Lookup & Reference (26)
| Function | OSE | EE |
|---|---|---|
| AREAS6.0 | Y | |
| ADDRESS | Y | Y |
| CHOOSE | Y | Y |
| COLUMN | Y | Y |
| COLUMNS | Y | Y |
| GROUPBY7.0 | Y | |
| HLOOKUP | Y | Y |
| HYPERLINK | Y | Y |
| IFNA6.0 | Y | |
| IFS6.0 | Y | |
| INDEX | Y | Y |
| INDIRECT | Y | Y |
| LOOKUP | Y | Y |
| MATCH | Y | Y |
| OFFSET | Y | Y |
| PIVOTBY7.0 | Y | |
| ROW | Y | Y |
| ROWS | Y | Y |
| SINGLE | Y | |
| SWITCH6.0 | Y | |
| TRANSPOSE | Y | |
| TRIMRANGE7.0 | Y | |
| UNIQUE7.0 | Y | |
| VLOOKUP | Y | Y |
| XLOOKUP6.0 | Y | |
| XMATCH6.0 | Y |
Mathematical (81)
| Function | OSE | EE |
|---|---|---|
| ABS | Y | Y |
| ACOS | Y | Y |
| ACOSH | Y | Y |
| ASIN | Y | Y |
| ASINH | Y | Y |
| ATAN | Y | Y |
| ATAN2 | Y | Y |
| ATANH | Y | Y |
| ACOT6.3 | Y | |
| ACOTH6.3 | Y | |
| AGGREGATE6.3 | Y | |
| ARABIC6.3 | Y | |
| BASE6.3 | Y | |
| CEILING | Y | Y |
| CEILING.MATH6.0 | Y | |
| CEILING.PRECISE6.0 | Y | |
| COMBIN | Y | Y |
| COMBINA6.3 | Y | |
| COS | Y | Y |
| COSH | Y | Y |
| COT6.3 | Y | |
| COTH6.3 | Y | |
| CSC6.3 | Y | |
| CSCH6.3 | Y | |
| DEGREES | Y | Y |
| DECIMAL6.3 | Y | |
| EVEN | Y | Y |
| EXP | Y | Y |
| FACT | Y | Y |
| FACTDOUBLE | Y | |
| FLOOR | Y | Y |
| FLOOR.MATH6.0 | Y | |
| FLOOR.PRECISE6.0 | Y | |
| GCD | Y | |
| INT | Y | Y |
| ISO.CEILING6.3 | Y | |
| LCM | Y | |
| LN | Y | Y |
| LOG | Y | Y |
| LOG10 | Y | Y |
| MDETERM | Y | |
| MINVERSE | Y | |
| MMULT | Y | |
| MOD | Y | Y |
| MROUND | Y | |
| MULTINOMIAL | Y | |
| MUNIT7.0 | Y | |
| NUMBERVALUE6.0 | Y | |
| ODD | Y | Y |
| PERCENTOF7.0 | Y | |
| PI | Y | Y |
| POWER | Y | Y |
| PRODUCT | Y | Y |
| QUOTIENT | Y | |
| RADIANS | Y | Y |
| RAND | Y | Y |
| RANDBETWEEN | Y | Y |
| ROMAN | Y | |
| ROUND | Y | Y |
| ROUNDDOWN | Y | Y |
| ROUNDUP | Y | Y |
| SEC6.3 | Y | |
| SECH6.3 | Y | |
| SERIESSUM6.3 | Y | |
| SIGN | Y | Y |
| SIN | Y | Y |
| SINH | Y | Y |
| SQRT | Y | Y |
| SQRTPI | Y | |
| SUBTOTAL | Y | Y |
| SUM | Y | Y |
| SUMIF | Y | Y |
| SUMIFS | Y | Y |
| SUMPRODUCT | Y | Y |
| SUMSQ | Y | Y |
| SUMX2MY2 | Y | Y |
| SUMX2PY2 | Y | Y |
| SUMXMY2 | Y | Y |
| TAN | Y | Y |
| TANH | Y | Y |
| TRUNC | Y | Y |
SUMPRODUCT
You can specify a condition 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)
SUBTOTAL
Keikai follows Microsoft SUBTOTAL function.
Regarding the 1st argument, “1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded”.
- If you enable the auto filter in a sheet, Keikai treats all hidden rows as filtered-out cells.
- If you don’t enable the auto filter, Keikai treats all hidden rows as manually-hidden rows.
Statistical (150)
| Function | New Name since Excel 2010 | OSE | EE |
|---|---|---|---|
| AVEDEV | Y | Y | |
| AVERAGE | Y | Y | |
| AVERAGEA | Y | ||
| AVERAGEIF | Y | ||
| AVERAGEIFS | Y | ||
| BETADIST | BETA.DIST | Y | |
| BETAINV | BETA.INV | Y | |
| BINOMDIST | BINOM.DIST | Y | |
| BINOM.DIST.RANGE6.3 | Y | ||
| CORREL | Y | ||
| CONFIDENCE | CONFIDENCE.NORM6.3 | Y | |
| CONFIDENCE.T6.3 | Y | ||
| CRITBINOM | BINOM.INV | Y | |
| CHIDIST | CHISQ.DIST.RT | Y | |
| CHIINV | CHISQ.INV.RT | Y | |
| CHISQ.DIST | Y | ||
| CHISQ.INV | Y | ||
| CHITEST | CHISQ.TEST6.3 | Y | |
| COUNT | Y | Y | |
| COUNTA | Y | Y | |
| COUNTBLANK | Y | Y | |
| COUNTIF | Y | Y | |
| COUNTIFS5.12 | Y | ||
| COVAR6.0 | Y | ||
| COVARIANCE.P6.3 | Y | ||
| COVARIANCE.S6.3 | Y | ||
| DEVSQ | Y | Y | |
| EXPONDIST | EXPON.DIST | Y | |
| F.DIST6.3 | Y | ||
| F.INV6.3 | Y | ||
| FTEST | F.TEST6.3 | Y | |
| FISHER6.3 | Y | ||
| FISHERINV6.3 | Y | ||
| FDIST | F.DIST.RT | Y | |
| FINV | F.INV.RT | Y | |
| FORECAST6.0 | Y | ||
| FORECAST.ETS7.0 | Y | ||
| FORECAST.ETS.CONFINT7.0 | Y | ||
| FORECAST.ETS.SEASONALITY7.0 | Y | ||
| FORECAST.ETS.STAT7.0 | Y | ||
| FORECAST.LINEAR6.3 | Y | ||
| FREQUENCY6.0 | Y | ||
| GAMMADIST | GAMMA.DIST | Y | |
| GAMMAINV | GAMMA.INV | Y | |
| GAMMA6.3 | Y | ||
| GAMMALN | Y | ||
| GAMMALN.PRECISE6.3 | Y | ||
| GAUSS6.3 | Y | ||
| GEOMEAN | Y | ||
| GROWTH7.0 | Y | ||
| HARMEAN | Y | ||
| HYPGEOMDIST | HYPGEOM.DIST | Y | |
| INTERCEPT6.0 | Y | ||
| KURT | Y | ||
| LARGE | Y | Y | |
| MAX | Y | Y | |
| MAXA | Y | Y | |
| MEDIAN | Y | Y | |
| MIN | Y | Y | |
| MINA | Y | Y | |
| MODE | MODE.SNGL | Y | Y |
| MODE.MULT7.0 | Y | ||
| NEGBINOMDIST | NEGBINOM.DIST | Y | |
| NORMDIST | NORM.DIST | Y | |
| NORMINV | NORM.INV | Y | |
| NORMSDIST | NORM.S.DIST | Y | |
| NORMSINV | NORM.S.INV | Y | |
| LINEST6.0 | Y | ||
| LOGEST | Y | ||
| LOGNORMDIST | LOGNORM.DIST | Y | |
| LOGINV | LOGNORM.INV | Y | Y |
| MAXIFS6.0 | Y | ||
| MINIFS6.0 | Y | ||
| PEARSON6.0 | Y | ||
| PERMUT6.3 | Y | ||
| PERMUTATIONA6.3 | Y | ||
| PHI6.3 | Y | ||
| PROB6.3 | Y | ||
| PERCENTILE6.0 | Y | ||
| PERCENTRANK6.0 | Y | ||
| PERCENTRANK.EXC6.0 | Y | ||
| PERCENTRANK.INC6.0 | Y | ||
| POISSON | POISSON.DIST | Y | Y |
| POISSON.INV | Y | ||
| PERCENTILE.EXC | Y | ||
| PERCENTILE.INC | Y | ||
| QUARTILE.EXC6.3 | Y | ||
| QUARTILE | QUARTILE.INC6.3 | Y | |
| RANK | RANK.EQ | Y | Y |
| RANK.AVG6.3 | Y | ||
| RSQ6.3 | Y | ||
| SKEW | Y | ||
| SKEW.P6.3 | Y | ||
| SLOPE | Y | ||
| SMALL | Y | Y | |
| STANDARDIZE6.0 | Y | ||
| STDEV | STDEV.S6.0 | Y | Y |
| STDEVA6.0 | Y | ||
| STDEVP | STDEV.P6.0 | Y | |
| STDEVPA6.0 | Y | ||
| STEYX6.3 | Y | ||
| T.DIST6.0 | Y | ||
| T.DIST.2T | Y | ||
| T.INV6.3 | Y | ||
| TDIST | T.DIST.RT | Y | |
| TINV | T.INV.2T | Y | |
| TTEST | T.TEST6.3 | Y | |
| TREND6.0 | Y | ||
| TRIMMEAN6.3 | Y | ||
| VAR | VAR.S | Y | Y |
| VARA6.0 | Y | ||
| VARP | VAR.P | Y | Y |
| VARPA6.0 | Y | ||
| WEIBULL | WEIBULL.DIST | Y | |
| ZTEST | Z.TEST6.3 | Y |
- Note: Keikai supports both function names listed here.
- Note: The count (150) reflects the total number of unique function names, including both the old and new (since Excel 2010) synonyms listed in the first two columns.
Text (39)
| Function | OSE | EE |
|---|---|---|
| ARRAYTOTEXT6.3 | Y | |
| ASC | Y | |
| BAHTTEXT | Y | |
| CHAR | Y | Y |
| CLEAN | Y | Y |
| CODE | Y | |
| CONCAT6.0 | Y | |
| CONCATENATE | Y | Y |
| DBCS | Y | |
| DOLLAR | Y | Y |
| ENCODEURL6.3 | Y | |
| EXACT | Y | Y |
| FIND | Y | Y |
| FIXED | Y | |
| JIS7.0 | Y | |
| LEFT | Y | Y |
| LEN | Y | Y |
| LOWER | Y | Y |
| MID | Y | Y |
| PROPER | Y | |
| REPLACE | Y | Y |
| REPT | Y | |
| REGEXEXTRACT6.3 | Y | |
| REGEXREPLACE6.3 | Y | |
| REGEXTEST6.3 | Y | |
| RIGHT | Y | Y |
| SEARCH | Y | Y |
| SUBSTITUTE | Y | Y |
| T | Y | Y |
| TEXT | Y | Y |
| TEXTAFTER6.3 | Y | |
| TEXTBEFORE6.3 | Y | |
| TEXTJOIN6.0 | Y | |
| TRIM | Y | Y |
| UPPER | Y | Y |
| UNICHAR6.3 | Y | |
| UNICODE6.3 | Y | |
| VALUE | Y | Y |
| VALUETOTEXT6.3 | Y |
Database (12)
since 6.0.0
| Function |
|---|
| DAVERAGE |
| DCOUNT |
| DCOUNTA |
| DGET |
| DMAX |
| DMIN |
| DPRODUCT |
| DSTDEV |
| DSTDEVP |
| DSUM |
| DVAR |
| DVARP |
Dynamic Array & Lambda (25)
since 7.0.0
Since Keikai 7.0, formulas whose result is a range spill into the neighbouring cells (Excel-compatible dynamic arrays), and the modern function family that produces or consumes arrays is supported. These functions are available in Keikai EE.
| Function | OSE | EE |
|---|---|---|
| BYCOL | Y | |
| BYROW | Y | |
| CHOOSECOLS | Y | |
| CHOOSEROWS | Y | |
| DROP | Y | |
| EXPAND | Y | |
| FILTER | Y | |
| HSTACK | Y | |
| ISOMITTED | Y | |
| LAMBDA | Y | |
| MAKEARRAY | Y | |
| MAP | Y | |
| RANDARRAY | Y | |
| REDUCE | Y | |
| SCAN | Y | |
| SEQUENCE | Y | |
| SORT | Y | |
| SORTBY | Y | |
| TAKE | Y | |
| TEXTSPLIT | Y | |
| TOCOL | Y | |
| TOROW | Y | |
| VSTACK | Y | |
| WRAPCOLS | Y | |
| WRAPROWS | Y |
(UNIQUE is another dynamic-array function; it is listed under
Lookup & Reference.)
Spilled-range reference (#)
A cell holding a dynamic-array formula spills its result into the
neighbouring cells. To reference the whole spilled range from another
formula, append # to the anchor cell - e.g. =SUM(A1#) sums every
cell that the formula in A1 spilled into. Internally this operator is
named ANCHORARRAY.
Cube Functions
Keikai recognises the seven Cube functions - CUBEMEMBER, CUBEVALUE,
CUBESET, CUBESETCOUNT, CUBERANKEDMEMBER, CUBEKPIMEMBER, and
CUBEMEMBERPROPERTY - so that workbooks using them import and round-trip.
Keikai has no OLAP / data-model connection, so they evaluate to #N/A, the
same result Excel returns without a cube data source.
Deliberately Excluded Functions
These functions are recognised by the calculation engine (so workbooks using them still import and round-trip) but are not offered in the Insert-Function dialog:
- Byte-length text variants -
LEFTB,RIGHTB,MIDB,LENB,FINDB,SEARCHB,REPLACEB. Microsoft deprecated these onceLEFT/RIGHT/MID/LEN/FIND/SEARCH/REPLACEbecame Unicode-aware (surrogate pairs). EUROCONVERT
Unsupported Functions
Keikai does not support the Web functions WEBSERVICE and FILTERXML, which
require live external HTTP requests.
Locale Support
- Keikai supports 2 separator: comma
,and semi-color;depeneding on the locale. - You can only use a function in English.
Relative Position Evaluation
When evaluating a row area (e.g. A1:B1) or column area (e.g. A1:A2), keikai will select a single cell from an area depending on the coordinates of the calling cell. Here is an example demonstrating both selection from a single row area and a single column area in the same formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 15 | 20 | 25 | |
| 2 | 200 | |||
| 3 | 300 | |||
| 4 | 400 |
If the formula =1000+A1:B1+D2:D3 is put into the 9 cells from A2 to C4, the spreadsheet
will look like this:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 15 | 20 | 25 | |
| 2 | 1215 | 1220 | #VALUE! | 200 |
| 3 | 1315 | 1320 | #VALUE! | 300 |
| 4 | #VALUE! | #VALUE! | #VALUE! | 400 |
Note that the row area (A1:B1) does not include column C and the column area (D2:D3) does not include row 4, so the values in C1(=25) and D4(=400) are not accessible to the formula as written, but in the 4 cells A2:B3, the row and column selection works ok.
The same concept is extended to references across sheets, such that even multi-row, multi-column areas can be useful.