DHTMLX Spreadsheet. Formulas (functions) cheat sheet example
This demo provides a working reference of formulas and functions supported in DHTMLX Spreadsheet, including math, logical, string, and information functions like SUM, IF, LEFT, and ISBLANK.
Live example
const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
menu: true,
});
spreadsheet.parse(formulas);
spreadsheet.sortCells("Boolean!A1:A200, Date!A1:A200, Financial!A1:A200, Information!A1:A200, Lookup!A1:A200, Math!A1:A200, Regex!A1:A200, String!A1:A200, Other!A1:A200", 1);
<!-- component container -->
<div style="height: 100%; max-width:100%" id="spreadsheet"></div>
<!-- dataset -->
<script>
const formulas = {
sheets: [
{
name: "Boolean",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{
cell: "A2",
css: "header",
format: "common",
value: "Boolean",
},
{
cell: "A3",
css: "highlighting",
format: "common",
value: "Equal to",
},
{ cell: "B3", format: "common", value: "=C3=D3" },
{ cell: "C3", format: "number", value: 5 },
{ cell: "D3", format: "number", value: 5 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "Greater than",
},
{ cell: "B4", format: "common", value: "=C4>D4" },
{ cell: "C4", format: "number", value: 7 },
{ cell: "D4", format: "number", value: 5 },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "Greater than or equal to",
},
{ cell: "B5", format: "common", value: "=C5>=D5" },
{ cell: "C5", format: "number", value: 7 },
{ cell: "D5", format: "number", value: 5 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "Less than",
},
{ cell: "B6", format: "common", value: "=C6<D6" },
{ cell: "C6", format: "number", value: 5 },
{ cell: "D6", format: "number", value: 7 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "Less than or equal to",
},
{ cell: "B7", format: "common", value: "=C7<=D7" },
{ cell: "C7", format: "number", value: 5 },
{ cell: "D7", format: "number", value: 7 },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "Not equal to",
},
{ cell: "B8", format: "common", value: "=C8<>D8" },
{ cell: "C8", format: "number", value: 5 },
{ cell: "D8", format: "number", value: 5 },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Date",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{ cell: "A2", css: "header", format: "common", value: "Date" },
{
cell: "A3",
css: "highlighting",
format: "common",
value: "DATE",
},
{ cell: "B3", format: "date", value: "=DATE(C3;D3;E3)" },
{ cell: "C3", format: "common", value: 2021 },
{ cell: "D3", format: "number", value: 11 },
{ cell: "E3", format: "number", value: 1 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "DATEDIF",
},
{ cell: "B4", format: "common", value: "=DATEDIF(C4;D4;E4)" },
{ cell: "C4", format: "date", value: 44136.125 },
{ cell: "D4", format: "date", value: 44501 },
{ cell: "E4", format: "common", value: "M" },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "DATEVALUE",
},
{ cell: "B5", format: "date", value: "=DATEVALUE(C5)" },
{ cell: "C5", format: "common", value: "3/10/1975" },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "DAY",
},
{ cell: "B6", format: "common", value: "=DAY(C6)" },
{ cell: "C6", format: "date", value: 44501 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "DAYS",
},
{ cell: "B7", format: "common", value: "=DAYS(C7;D7)" },
{ cell: "C7", format: "date", value: 44501 },
{ cell: "D7", format: "date", value: 44136.125 },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "DAYS360",
},
{ cell: "B8", format: "common", value: "=DAYS360(C8;D8)" },
{ cell: "C8", format: "date", value: 44136.125 },
{ cell: "D8", format: "date", value: 44501 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "EDATE",
},
{ cell: "B9", format: "date", value: "=EDATE(C9;D9)" },
{ cell: "C9", format: "date", value: 44136.125 },
{ cell: "D9", format: "number", value: 24 },
{
cell: "A10",
css: "highlighting",
format: "common",
value: "EOMONTH",
},
{ cell: "B10", format: "date", value: "=EOMONTH(C10;D10)" },
{ cell: "C10", format: "date", value: 44136.125 },
{ cell: "D10", format: "number", value: 1 },
{
cell: "A11",
css: "highlighting",
format: "common",
value: "ISOWEEKNUM",
},
{ cell: "B11", format: "common", value: "=ISOWEEKNUM(C11)" },
{ cell: "C11", format: "date", value: 44136.125 },
{
cell: "A12",
css: "highlighting",
format: "common",
value: "MONTH",
},
{ cell: "B12", format: "common", value: "=MONTH(C12)" },
{ cell: "C12", format: "date", value: 44501 },
{
cell: "A13",
css: "highlighting",
format: "common",
value: "NETWORKDAYS",
},
{ cell: "B13", format: "common", value: "=NETWORKDAYS(C13;D13)" },
{ cell: "C13", format: "date", value: 44136.125 },
{ cell: "D13", format: "date", value: 44501 },
{
cell: "A14",
css: "highlighting",
format: "common",
value: "NETWORKDAYS.INTL",
},
{
cell: "B14",
format: "common",
value: "=NETWORKDAYS.INTL(C14;D14;E14)",
},
{ cell: "C14", format: "date", value: 44136.125 },
{ cell: "D14", format: "date", value: 44501 },
{ cell: "E14", format: "number", value: 1 },
{
cell: "A15",
css: "highlighting",
format: "common",
value: "NOW",
},
{ cell: "B15", format: "date", value: "=NOW()" },
{
cell: "A16",
css: "highlighting",
format: "common",
value: "TIMEVALUE",
},
{ cell: "B16", format: "common", value: "=TIMEVALUE(C16)" },
{ cell: "C16", format: "text", value: "14:15:30" },
{
cell: "A17",
css: "highlighting",
format: "common",
value: "WEEKDAY",
},
{ cell: "B17", format: "common", value: "=WEEKDAY(C17)" },
{ cell: "C17", format: "date", value: 44501 },
{
cell: "A18",
css: "highlighting",
format: "common",
value: "WEEKNUM",
},
{ cell: "B18", format: "common", value: "=WEEKNUM(C18)" },
{ cell: "C18", format: "date", value: 44501 },
{
cell: "A19",
css: "highlighting",
format: "common",
value: "WORKDAY",
},
{ cell: "B19", format: "date", value: "=WORKDAY(C19,D19)" },
{ cell: "C19", format: "date", value: 44501 },
{ cell: "D19", format: "number", value: 5 },
{
cell: "A20",
css: "highlighting",
format: "common",
value: "WORKDAY.INTL",
},
{
cell: "B20",
format: "date",
value: "=WORKDAY.INTL(C20;D20;E20)",
},
{ cell: "C20", format: "date", value: 44136.125 },
{ cell: "D20", format: "number", value: 2 },
{ cell: "E20", format: "number", value: 2 },
{
cell: "A21",
css: "highlighting",
format: "common",
value: "YEAR",
},
{ cell: "B21", format: "common", value: "=YEAR(C21)" },
{ cell: "C21", format: "date", value: 44501 },
{
cell: "A22",
css: "highlighting",
format: "common",
value: "YEARFRAC",
},
{
cell: "B22",
format: "common",
value: "=YEARFRAC(C22;D22;E22)",
},
{ cell: "C22", format: "date", value: 44136.125 },
{ cell: "D22", format: "date", value: 44501 },
{ cell: "E22", format: "number", value: 0 },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Financial",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{
cell: "A2",
css: "header",
format: "common",
value: "Financial",
},
{
cell: "A3",
css: "highlighting",
format: "common",
value: "ACCRINT",
},
{
cell: "B3",
format: "common",
value: "=ACCRINT(C3;D3;E3;F3;G3;H3)",
},
{ cell: "C3", format: "number", value: 39508 },
{ cell: "D3", format: "number", value: 39691 },
{ cell: "E3", format: "number", value: 39569 },
{ cell: "F3", format: "number", value: 0.1 },
{ cell: "G3", format: "number", value: 1000 },
{ cell: "H3", format: "number", value: 1 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "BINOM.DIST",
},
{
cell: "B4",
format: "common",
value: "=BINOM.DIST(C4,D4,E4,F4)",
},
{ cell: "C4", format: "number", value: 30 },
{ cell: "D4", format: "number", value: 65 },
{ cell: "E4", format: "percent", value: 0.35 },
{ cell: "F4", format: "number", value: "TRUE" },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "BINOM.DIST.RANGE",
},
{
cell: "B5",
format: "common",
value: "=BINOM.DIST.RANGE(C5,D5,E5)",
},
{ cell: "C5", format: "number", value: 60 },
{ cell: "D5", format: "number", value: 0.75 },
{ cell: "E5", format: "number", value: 48 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "BINOM.INV",
},
{ cell: "B6", format: "common", value: "=BINOM.INV(C6,D6,E6)" },
{ cell: "C6", format: "number", value: 100 },
{ cell: "D6", format: "number", value: 0.5 },
{ cell: "E6", format: "percent", value: 0.2 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "BITLSHIFT",
},
{ cell: "B7", format: "common", value: "=BITLSHIFT(C7,D7)" },
{ cell: "C7", format: "common", value: 4 },
{ cell: "D7", format: "common", value: 2 },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "BITOR",
},
{ cell: "B8", format: "common", value: "=BITOR(C8, D8)" },
{ cell: "C8", format: "common", value: 23 },
{ cell: "D8", format: "common", value: 10 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "BITRSHIFT",
},
{ cell: "B9", format: "common", value: "=BITRSHIFT(C9,D9)" },
{ cell: "C9", format: "common", value: 16 },
{ cell: "D9", format: "common", value: 2 },
{
cell: "A10",
css: "highlighting",
format: "common",
value: "BITXOR",
},
{ cell: "B10", format: "common", value: "=BITXOR(C10,D10)" },
{ cell: "C10", format: "common", value: 5 },
{ cell: "D10", format: "common", value: 3 },
{
cell: "A11",
css: "highlighting",
format: "common",
value: "COMPLEX",
},
{ cell: "B11", format: "common", value: "=COMPLEX(C11,D11)" },
{ cell: "C11", format: "common", value: 5 },
{ cell: "D11", format: "common", value: 2 },
{
cell: "A12",
css: "highlighting",
format: "common",
value: "CORREL",
},
{
cell: "B12",
format: "common",
value: "=CORREL(C12:G12,I12:M12)",
},
{ cell: "C12", format: "common", value: 3 },
{ cell: "D12", format: "common", value: 2 },
{ cell: "E12", format: "common", value: 4 },
{ cell: "F12", format: "common", value: 5 },
{ cell: "G12", format: "common", value: 6 },
{ cell: "I12", format: "common", value: 9 },
{ cell: "J12", format: "common", value: 7 },
{ cell: "K12", format: "common", value: 12 },
{ cell: "L12", format: "common", value: 15 },
{ cell: "M12", format: "common", value: 17 },
{
cell: "A13",
css: "highlighting",
format: "common",
value: "COVAR",
},
{
cell: "B13",
format: "common",
value: "=COVAR(C13:G13,I13:M13)",
},
{
cell: "C13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 3,
},
{
cell: "D13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 2,
},
{
cell: "E13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 4,
},
{
cell: "F13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 5,
},
{
cell: "G13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 6,
},
{
cell: "H13",
css: "dhx_generated_class_u1652788414258",
format: "common",
},
{
cell: "I13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 9,
},
{
cell: "J13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 7,
},
{
cell: "K13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 12,
},
{
cell: "L13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 15,
},
{
cell: "M13",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 17,
},
{
cell: "A14",
css: "highlighting",
format: "common",
value: "COVARIANCE.P",
},
{
cell: "B14",
format: "common",
value: "=COVARIANCE.P(C14:G14,I14:M14)",
},
{
cell: "C14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 3,
},
{
cell: "D14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 2,
},
{
cell: "E14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 4,
},
{
cell: "F14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 5,
},
{
cell: "G14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 6,
},
{
cell: "H14",
css: "dhx_generated_class_u1652776833412",
format: "common",
},
{
cell: "I14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 9,
},
{
cell: "J14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 7,
},
{
cell: "K14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 12,
},
{
cell: "L14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 15,
},
{
cell: "M14",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 17,
},
{
cell: "A15",
css: "highlighting",
format: "common",
value: "COVARIANCE.S",
},
{
cell: "B15",
format: "common",
value: "=COVARIANCE.S(C15:G15,I15:M15)",
},
{
cell: "C15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 3,
},
{
cell: "D15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 2,
},
{
cell: "E15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 4,
},
{
cell: "F15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 5,
},
{
cell: "G15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 6,
},
{
cell: "H15",
css: "dhx_generated_class_u1652776833412",
format: "common",
},
{
cell: "I15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 9,
},
{
cell: "J15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 7,
},
{
cell: "K15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 12,
},
{
cell: "L15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 15,
},
{
cell: "M15",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 17,
},
{
cell: "A16",
css: "highlighting",
format: "common",
value: "DB",
},
{
cell: "B16",
format: "common",
value: "=DB(C16;D16;E16;F16;G16)",
},
{ cell: "C16", format: "number", value: 10000 },
{ cell: "D16", format: "number", value: 5000 },
{ cell: "E16", format: "number", value: 5 },
{ cell: "F16", format: "number", value: 1 },
{ cell: "G16", format: "number", value: 12 },
{
cell: "A17",
css: "highlighting",
format: "common",
value: "DDB",
},
{
cell: "B17",
format: "common",
value: "=DDB(C17;D17;E17;F17;G17)",
},
{ cell: "C17", format: "number", value: 3500 },
{ cell: "D17", format: "number", value: 500 },
{ cell: "E17", format: "number", value: 5 },
{ cell: "F17", format: "number", value: 1 },
{ cell: "G17", format: "number", value: 2 },
{
cell: "A18",
css: "highlighting",
format: "common",
value: "DEC2BIN",
},
{ cell: "B18", format: "common", value: "=DEC2BIN(C18)" },
{ cell: "C18", format: "common", value: 9 },
{
cell: "A19",
css: "highlighting",
format: "common",
value: "DEC2HEX",
},
{ cell: "B19", format: "number", value: "=DEC2HEX(C19)" },
{ cell: "C19", format: "common", value: -54 },
{
cell: "A20",
css: "highlighting",
format: "common",
value: "DEC2OCT",
},
{ cell: "B20", format: "common", value: "=DEC2OCT(C20,D20)" },
{ cell: "C20", format: "common", value: 58 },
{ cell: "D20", format: "common", value: 3 },
{
cell: "A21",
css: "highlighting",
format: "common",
value: "DELTA",
},
{ cell: "B21", format: "common", value: "=DELTA(C21,D21)" },
{ cell: "C21", format: "common", value: 4 },
{ cell: "D21", format: "common", value: 5 },
{
cell: "A22",
css: "highlighting",
format: "common",
value: "DEVSQ",
},
{ cell: "B22", format: "common", value: "=DEVSQ(C22:H22)" },
{ cell: "C22", format: "common", value: 50 },
{ cell: "D22", format: "common", value: 47 },
{ cell: "E22", format: "common", value: 52 },
{ cell: "F22", format: "common", value: 46 },
{ cell: "G22", format: "common", value: 45 },
{ cell: "H22", format: "common", value: 48 },
{
cell: "A23",
css: "highlighting",
format: "common",
value: "DOLLARDE",
},
{ cell: "B23", format: "common", value: "=DOLLARDE(C23;D23)" },
{ cell: "C23", format: "number", value: 1.1 },
{ cell: "D23", format: "number", value: 32 },
{
cell: "A24",
css: "highlighting",
format: "common",
value: "DOLLARFR",
},
{ cell: "B24", format: "common", value: "=DOLLARFR(C24;D24)" },
{ cell: "C24", format: "number", value: 1.125 },
{ cell: "D24", format: "number", value: 16 },
{
cell: "A25",
css: "highlighting",
format: "common",
value: "EFFECT",
},
{ cell: "B25", format: "common", value: "=EFFECT(C25;D25)" },
{ cell: "C25", format: "percent", value: 0.06 },
{ cell: "D25", format: "number", value: 2 },
{
cell: "A26",
css: "highlighting",
format: "common",
value: "ERF",
},
{ cell: "B26", format: "common", value: "=ERF(C26)" },
{ cell: "C26", format: "common", value: 1 },
{
cell: "A27",
css: "highlighting",
format: "common",
value: "ERFC",
},
{ cell: "B27", format: "common", value: "=ERFC(C26)" },
{
cell: "A28",
css: "highlighting",
format: "common",
value: "EXP",
},
{ cell: "B28", format: "common", value: "=EXP(C26)" },
{
cell: "A29",
css: "highlighting",
format: "common",
value: "FISHER",
},
{ cell: "B29", format: "common", value: "=FISHER(C29)" },
{ cell: "C29", format: "common", value: 0.75 },
{
cell: "A30",
css: "highlighting",
format: "common",
value: "FISHERINV",
},
{ cell: "B30", format: "common", value: "=FISHERINV(C30)" },
{ cell: "C30", format: "common", value: 0.97 },
{
cell: "A31",
css: "highlighting",
format: "common",
value: "FV",
},
{
cell: "B31",
format: "common",
value: "=FV(C31/12;D31;E31;F31;G31)",
},
{ cell: "C31", format: "number", value: 0.06 },
{ cell: "D31", format: "number", value: 10 },
{ cell: "E31", format: "number", value: -200 },
{ cell: "F31", format: "number", value: -500 },
{ cell: "G31", format: "number", value: 1 },
{
cell: "A32",
css: "highlighting",
format: "common",
value: "FVSCHEDULE",
},
{
cell: "B32",
format: "common",
value: "=FVSCHEDULE(C32;D32:E32)",
},
{ cell: "C32", format: "number", value: 100 },
{ cell: "D32", format: "percent", value: 0.02 },
{
cell: "E32",
css: "dhx_generated_class_u1652776833412",
format: "percent",
value: 0.02,
},
{
cell: "A33",
css: "highlighting",
format: "common",
value: "GAMMA",
},
{ cell: "B33", format: "common", value: "=GAMMA(C33)" },
{ cell: "C33", format: "common", value: 2.5 },
{
cell: "A34",
css: "highlighting",
format: "common",
value: "GEOMEAN",
},
{ cell: "B34", format: "common", value: "=GEOMEAN(C34:G34)" },
{ cell: "C34", format: "number", value: 1 },
{ cell: "D34", format: "number", value: 2 },
{ cell: "E34", format: "number", value: 3 },
{ cell: "F34", format: "number", value: 4 },
{ cell: "G34", format: "number", value: 5 },
{
cell: "A35",
css: "highlighting",
format: "common",
value: "GESTEP",
},
{ cell: "B35", format: "common", value: "=GESTEP(C35,D35)" },
{ cell: "C35", format: "common", value: 5 },
{ cell: "D35", format: "common", value: 4 },
{
cell: "A36",
css: "highlighting",
format: "common",
value: "HARMEAN",
},
{ cell: "B36", format: "common", value: "=HARMEAN(C36:G36)" },
{
cell: "C36",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 1,
},
{
cell: "D36",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "E36",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 3,
},
{
cell: "F36",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 4,
},
{
cell: "G36",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "A37",
css: "highlighting",
format: "common",
value: "HEX2BIN",
},
{ cell: "B37", format: "common", value: "=HEX2BIN(C37)" },
{ cell: "C37", format: "common", value: "FF" },
{
cell: "A38",
css: "highlighting",
format: "common",
value: "HEX2DEC",
},
{ cell: "B38", format: "common", value: "=HEX2DEC(C38)" },
{ cell: "C38", format: "common", value: 10 },
{
cell: "A39",
css: "highlighting",
format: "common",
value: "HEX2OCT",
},
{ cell: "B39", format: "common", value: "=HEX2OCT(C39)" },
{ cell: "C39", format: "common", value: "3B4E" },
{
cell: "A40",
css: "highlighting",
format: "common",
value: "IMABS",
},
{ cell: "B40", format: "common", value: "=IMABS(C40)" },
{ cell: "C40", format: "common", value: "5+12i" },
{
cell: "A41",
css: "highlighting",
format: "common",
value: "IMAGINARY",
},
{ cell: "B41", format: "common", value: "=IMAGINARY(C41)" },
{ cell: "C41", format: "common", value: "-i" },
{
cell: "A42",
css: "highlighting",
format: "common",
value: "IMCONJUGATE",
},
{ cell: "B42", format: "common", value: "=IMCONJUGATE(C42)" },
{ cell: "C42", format: "common", value: "3+4i" },
{
cell: "A43",
css: "highlighting",
format: "common",
value: "IMCOS",
},
{ cell: "B43", format: "common", value: "=IMCOS(C43)" },
{ cell: "C43", format: "common", value: "1+i" },
{
cell: "A44",
css: "highlighting",
format: "common",
value: "IMCOSH",
},
{ cell: "B44", format: "common", value: "=IMCOSH(C44)" },
{ cell: "C44", format: "common", value: "4+3i" },
{
cell: "A45",
css: "highlighting",
format: "common",
value: "IMCOT",
},
{ cell: "B45", format: "common", value: "=IMCOT(C45)" },
{
cell: "C45",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A46",
css: "highlighting",
format: "common",
value: "IMCSC",
},
{ cell: "B46", format: "common", value: "=IMCSC(C46)" },
{
cell: "C46",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A47",
css: "highlighting",
format: "common",
value: "IMCSCH",
},
{ cell: "B47", format: "common", value: "=IMCSCH(C47)" },
{
cell: "C47",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A48",
css: "highlighting",
format: "common",
value: "IMDIV",
},
{ cell: "B48", format: "common", value: "=IMDIV(C48,D48)" },
{ cell: "C48", format: "common", value: "-238+240i" },
{ cell: "D48", format: "common", value: "10+24i" },
{
cell: "A49",
css: "highlighting",
format: "common",
value: "IMEXP",
},
{ cell: "B49", format: "common", value: "=IMEXP(C49)" },
{ cell: "C49", format: "common", value: "1+i" },
{
cell: "A50",
css: "highlighting",
format: "common",
value: "IMLN",
},
{ cell: "B50", format: "common", value: "=IMLN(C50)" },
{
cell: "C50",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "3+4i",
},
{
cell: "A51",
css: "highlighting",
format: "common",
value: "IMPOWER",
},
{ cell: "B51", format: "common", value: "=IMPOWER(C51,D51)" },
{ cell: "C51", format: "common", value: "2+3i" },
{ cell: "D51", format: "common", value: 3 },
{
cell: "A52",
css: "highlighting",
format: "common",
value: "IMPRODUCT",
},
{ cell: "B52", format: "common", value: "=IMPRODUCT(C52,D52)" },
{ cell: "C52", format: "common", value: "3+4i" },
{ cell: "D52", format: "common", value: "5-3i" },
{
cell: "A53",
css: "highlighting",
format: "common",
value: "IMREAL",
},
{ cell: "B53", format: "common", value: "=IMREAL(C53)" },
{ cell: "C53", format: "common", value: "-i" },
{
cell: "A54",
css: "highlighting",
format: "common",
value: "IMSEC",
},
{ cell: "B54", format: "common", value: "=IMSEC(C54)" },
{
cell: "C54",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A55",
css: "highlighting",
format: "common",
value: "IMSECH",
},
{ cell: "B55", format: "common", value: "=IMSECH(C55)" },
{
cell: "C55",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A56",
css: "highlighting",
format: "common",
value: "IMSIN",
},
{ cell: "B56", format: "common", value: "=IMSIN(C56)" },
{
cell: "C56",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A57",
css: "highlighting",
format: "common",
value: "IMSINH",
},
{ cell: "B57", format: "common", value: "=IMSINH(C57)" },
{
cell: "C57",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "4+3i",
},
{
cell: "A58",
css: "highlighting",
format: "common",
value: "IMSQRT",
},
{ cell: "B58", format: "common", value: "=IMSQRT(C58)" },
{ cell: "C58", format: "common", value: "1+i" },
{
cell: "A59",
css: "highlighting",
format: "common",
value: "IMSUB",
},
{ cell: "B59", format: "common", value: "=IMSUB(C59,D59)" },
{ cell: "C59", format: "common", value: "13+4i" },
{ cell: "D59", format: "common", value: "5+3i" },
{
cell: "A60",
css: "highlighting",
format: "common",
value: "IMSUM",
},
{ cell: "B60", format: "common", value: "=IMSUM(C60,D60,E60)" },
{ cell: "C60", format: "common", value: "3+4i" },
{ cell: "D60", format: "common", value: "5-3i" },
{ cell: "E60", format: "common", value: "1-3i" },
{
cell: "A61",
css: "highlighting",
format: "common",
value: "IMTAN",
},
{ cell: "B61", format: "common", value: "=IMTAN(C61)" },
{ cell: "C61", format: "common", value: "3i" },
{
cell: "A62",
css: "highlighting",
format: "common",
value: "IPMT",
},
{
cell: "B62",
format: "common",
value: "=IPMT(C62/12;D62;E62*12;-F62)",
},
{ cell: "C62", format: "percent", value: 0.03 },
{ cell: "D62", format: "number", value: 24 },
{ cell: "E62", format: "number", value: 5 },
{ cell: "F62", format: "number", value: 10000 },
{
cell: "A63",
css: "highlighting",
format: "common",
value: "IRR",
},
{ cell: "B63", format: "common", value: "=IRR(C63:E63)" },
{ cell: "C63", format: "number", value: -2000 },
{ cell: "D63", format: "number", value: 3000 },
{ cell: "E63", format: "number", value: 4000 },
{
cell: "A64",
css: "highlighting",
format: "common",
value: "ISPMT",
},
{
cell: "B64",
format: "common",
value: "=ISPMT(C64;D64;E64;-F64)",
},
{ cell: "C64", format: "percent", value: 0.1 },
{ cell: "D64", format: "number", value: 0 },
{ cell: "E64", format: "number", value: 4 },
{ cell: "F64", format: "number", value: 4000 },
{
cell: "A65",
css: "highlighting",
format: "common",
value: "LARGE",
},
{ cell: "B65", format: "common", value: "=LARGE(C65:G65,2)" },
{ cell: "C65", format: "number", value: 1 },
{ cell: "D65", format: "number", value: 2 },
{ cell: "E65", format: "number", value: 3 },
{ cell: "F65", format: "number", value: 4 },
{ cell: "G65", format: "number", value: 5 },
{
cell: "A66",
css: "highlighting",
format: "common",
value: "MEDIAN",
},
{ cell: "B66", format: "common", value: "=MEDIAN(C66:G66,3)" },
{
cell: "C66",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 1,
},
{
cell: "D66",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "E66",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 3,
},
{
cell: "F66",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 4,
},
{
cell: "G66",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "A67",
css: "highlighting",
format: "common",
value: "NOMINAL",
},
{ cell: "B67", format: "common", value: "=NOMINAL(C67;D67)" },
{ cell: "C67", format: "percent", value: 0.0609 },
{ cell: "D67", format: "number", value: 2 },
{
cell: "A68",
css: "highlighting",
format: "common",
value: "NPER",
},
{
cell: "B68",
format: "common",
value: "=NPER(C68/12;D68;-E68)",
},
{ cell: "C68", format: "percent", value: 0.045 },
{ cell: "D68", format: "number", value: 93.22 },
{ cell: "E68", format: "number", value: 5000 },
{
cell: "A69",
css: "highlighting",
format: "common",
value: "NPV",
},
{ cell: "B69", format: "common", value: "=NPV(C69;D69;E69)" },
{ cell: "C69", format: "percent", value: 0.1 },
{ cell: "D69", format: "number", value: 100 },
{ cell: "E69", format: "number", value: 200 },
{
cell: "A70",
css: "highlighting",
format: "common",
value: "OCT2BIN",
},
{ cell: "B70", format: "common", value: "=OCT2BIN(C70)" },
{ cell: "C70", format: "number", value: 10 },
{
cell: "A71",
css: "highlighting",
format: "common",
value: "OCT2DEC",
},
{ cell: "B71", format: "common", value: "=OCT2DEC(C71)" },
{
cell: "C71",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 10,
},
{
cell: "A72",
css: "highlighting",
format: "common",
value: "OCT2HEX",
},
{ cell: "B72", format: "common", value: "=OCT2HEX(C72)" },
{ cell: "C72", format: "common", value: 252 },
{
cell: "A73",
css: "highlighting",
format: "common",
value: "PDURATION",
},
{
cell: "B73",
format: "common",
value: "=PDURATION(C73;D73;E73)",
},
{ cell: "C73", format: "percent", value: 0.045 },
{ cell: "D73", format: "number", value: 93.22 },
{ cell: "E73", format: "number", value: 5000 },
{
cell: "A74",
css: "highlighting",
format: "common",
value: "PERCENTILE",
},
{
cell: "B74",
format: "common",
value: "=PERCENTILE(C74:F74,0.3)",
},
{ cell: "C74", format: "number", value: 1 },
{ cell: "D74", format: "number", value: 2 },
{ cell: "E74", format: "number", value: 3 },
{ cell: "F74", format: "number", value: 4 },
{
cell: "A75",
css: "highlighting",
format: "common",
value: "PERCENTILE.EXC",
},
{
cell: "B75",
format: "common",
value: "=PERCENTILE.EXC(C75:K75,0.25)",
},
{
cell: "C75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 1,
},
{
cell: "D75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 2,
},
{
cell: "E75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 3,
},
{
cell: "F75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 4,
},
{
cell: "G75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 5,
},
{
cell: "H75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 6,
},
{
cell: "I75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 7,
},
{
cell: "J75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 8,
},
{
cell: "K75",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 9,
},
{
cell: "A76",
css: "highlighting",
format: "common",
value: "PERCENTILE.INC",
},
{
cell: "B76",
format: "common",
value: "=PERCENTILE.INC(C76:F76,0.3)",
},
{
cell: "C76",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 1,
},
{
cell: "D76",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 2,
},
{
cell: "E76",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 3,
},
{
cell: "F76",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 4,
},
{
cell: "G76",
css: "dhx_generated_class_u1652779118277",
format: "number",
},
{
cell: "H76",
css: "dhx_generated_class_u1652779118277",
format: "number",
},
{
cell: "I76",
css: "dhx_generated_class_u1652779118277",
format: "number",
},
{
cell: "J76",
css: "dhx_generated_class_u1652779118277",
format: "number",
},
{
cell: "K76",
css: "dhx_generated_class_u1652779118277",
format: "number",
},
{
cell: "A77",
css: "highlighting",
format: "common",
value: "PERMUT",
},
{ cell: "B77", format: "common", value: "=PERMUT(C77,D77)" },
{ cell: "C77", format: "common", value: 3 },
{ cell: "D77", format: "common", value: 2 },
{
cell: "A78",
css: "highlighting",
format: "common",
value: "PMT",
},
{
cell: "B78",
format: "common",
value: "=PMT(C78/12;D78*12;-E78)",
},
{ cell: "C78", format: "percent", value: 0.03 },
{ cell: "D78", format: "number", value: 5 },
{ cell: "E78", format: "number", value: 10000 },
{
cell: "A79",
css: "highlighting",
format: "common",
value: "PPMT",
},
{
cell: "B79",
format: "common",
value: "=PPMT(C79/12;D79;E79*12;-F79;G79;)",
},
{ cell: "C79", format: "percent", value: 0.03 },
{ cell: "D79", format: "number", value: 24 },
{ cell: "E79", format: "number", value: 5 },
{ cell: "F79", format: "number", value: 10000 },
{ cell: "G79", format: "number", value: 0 },
{
cell: "A80",
css: "highlighting",
format: "common",
value: "PV",
},
{
cell: "B80",
format: "common",
value: "=PV(C80/12;12*D80;-E80;F80;)",
},
{ cell: "C80", format: "percent", value: 0.08 },
{ cell: "D80", format: "number", value: 20 },
{ cell: "E80", format: "number", value: 500 },
{ cell: "F80", format: "number", value: 0 },
{
cell: "A81",
css: "highlighting",
format: "common",
value: "QUARTILE",
},
{ cell: "B81", format: "common", value: "=QUARTILE(C81:J81,1)" },
{ cell: "C81", format: "common", value: 1 },
{ cell: "D81", format: "common", value: 2 },
{ cell: "E81", format: "common", value: 4 },
{ cell: "F81", format: "common", value: 7 },
{ cell: "G81", format: "common", value: 8 },
{ cell: "H81", format: "common", value: 9 },
{ cell: "I81", format: "common", value: 10 },
{ cell: "J81", format: "common", value: 12 },
{
cell: "A82",
css: "highlighting",
format: "common",
value: "QUARTILE.EXC",
},
{
cell: "B82",
format: "common",
value: "=QUARTILE.EXC(C82:M82,1)",
},
{ cell: "C82", format: "common", value: 6 },
{ cell: "D82", format: "common", value: 7 },
{ cell: "E82", format: "common", value: 15 },
{ cell: "F82", format: "common", value: 36 },
{ cell: "G82", format: "common", value: 39 },
{ cell: "H82", format: "common", value: 40 },
{ cell: "I82", format: "common", value: 41 },
{ cell: "J82", format: "common", value: 42 },
{ cell: "K82", format: "common", value: 43 },
{ cell: "L82", format: "common", value: 47 },
{ cell: "M82", format: "common", value: 49 },
{
cell: "A83",
css: "highlighting",
format: "common",
value: "QUARTILE.INC",
},
{
cell: "B83",
format: "common",
value: "=QUARTILE.INC(C83:J83,1)",
},
{
cell: "C83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 6,
},
{
cell: "D83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 7,
},
{
cell: "E83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 15,
},
{
cell: "F83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 36,
},
{
cell: "G83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 39,
},
{
cell: "H83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 40,
},
{
cell: "I83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 41,
},
{
cell: "J83",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 42,
},
{
cell: "K83",
css: "dhx_generated_class_u1652788414258",
format: "common",
},
{
cell: "L83",
css: "dhx_generated_class_u1652788414258",
format: "common",
},
{
cell: "M83",
css: "dhx_generated_class_u1652788414258",
format: "common",
},
{
cell: "A84",
css: "highlighting",
format: "common",
value: "SIGN",
},
{ cell: "B84", format: "common", value: "=SIGN(C84)" },
{ cell: "C84", format: "common", value: 10 },
{
cell: "A85",
css: "highlighting",
format: "common",
value: "SMALL",
},
{ cell: "B85", format: "common", value: "=SMALL(C85:G85,F85)" },
{ cell: "C85", format: "number", value: 1 },
{ cell: "D85", format: "number", value: 2 },
{ cell: "E85", format: "number", value: 3 },
{ cell: "F85", format: "number", value: 4 },
{ cell: "G85", format: "number", value: 5 },
{
cell: "A86",
css: "highlighting",
format: "common",
value: "STEYX",
},
{
cell: "B86",
format: "common",
value: "=STEYX(H86:L86,C86:G86)",
},
{ cell: "C86", format: "common", value: 6 },
{ cell: "D86", format: "common", value: 7 },
{ cell: "E86", format: "common", value: 8 },
{ cell: "F86", format: "common", value: 9 },
{ cell: "G86", format: "common", value: 10 },
{
cell: "H86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 1,
},
{
cell: "I86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "J86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 3,
},
{
cell: "K86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 4,
},
{
cell: "L86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "A87",
css: "highlighting",
format: "common",
value: "SYD",
},
{ cell: "B87", format: "common", value: "=SYD(C87,D87,E87,F87)" },
{ cell: "C87", format: "common", value: 30000 },
{ cell: "D87", format: "common", value: 7500 },
{ cell: "E87", format: "common", value: 10 },
{ cell: "F87", format: "common", value: 1 },
{
cell: "A88",
css: "highlighting",
format: "common",
value: "TBILLPRICE",
},
{
cell: "B88",
format: "common",
value: "=TBILLPRICE(C88,D88,E88)",
},
{ cell: "C88", format: "date", value: 39538.125 },
{ cell: "D88", format: "date", value: 39600.125 },
{ cell: "E88", format: "common", value: 0.09 },
{
cell: "A89",
css: "highlighting",
format: "common",
value: "TBILLYIELD",
},
{
cell: "B89",
format: "common",
value: "=TBILLYIELD(C89,D89,98.45)",
},
{
cell: "C89",
css: "dhx_generated_class_u1652788414258",
format: "date",
value: 39538.125,
},
{
cell: "D89",
css: "dhx_generated_class_u1652788414258",
format: "date",
value: 39600.125,
},
{
cell: "A90",
css: "highlighting",
format: "common",
value: "WEIBULL",
},
{
cell: "B90",
format: "common",
value: "=WEIBULL(C90,D90,E90,F90)",
},
{ cell: "C90", format: "common", value: 105 },
{ cell: "D90", format: "common", value: 20 },
{ cell: "E90", format: "common", value: 100 },
{ cell: "F90", format: "common", value: 1 },
{
cell: "A91",
css: "highlighting",
format: "common",
value: "WEIBULL.DIST",
},
{
cell: "B91",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: "=WEIBULL.DIST(C91,D91,E91,F91)",
},
{
cell: "C91",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 105,
},
{
cell: "D91",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 20,
},
{
cell: "E91",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 100,
},
{
cell: "F91",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 1,
},
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Information",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{
cell: "A2",
css: "header",
format: "common",
value: "Information",
},
{
cell: "A3",
css: "highlighting",
format: "common",
value: "ISBINARY",
},
{ cell: "B3", format: "common", value: "=ISBINARY(C3)" },
{ cell: "C3", format: "number", value: 0 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "ISBLANK",
},
{ cell: "B4", format: "common", value: "=ISBLANK(C4)" },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "ISEVEN",
},
{ cell: "B5", format: "common", value: "=ISEVEN(C5)" },
{ cell: "C5", format: "number", value: 2 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "ISNONTEXT",
},
{ cell: "B6", format: "common", value: "=ISNONTEXT(C6)" },
{ cell: "C6", format: "number", value: 1 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "ISNUMBER",
},
{ cell: "B7", format: "common", value: "=ISNUMBER(C7)" },
{ cell: "C7", format: "number", value: 1 },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "ISODD",
},
{ cell: "B8", format: "common", value: "=ISODD(C8)" },
{ cell: "C8", format: "number", value: 3 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "ISTEXT",
},
{ cell: "B9", format: "common", value: "=ISTEXT(C9)" },
{ cell: "C9", format: "common", value: "text" },
{
cell: "A10",
css: "highlighting",
format: "common",
value: "N",
},
{ cell: "B10", format: "common", value: "=N(C10)" },
{ cell: "C10", format: "common", value: "TRUE" },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Lookup",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{
cell: "A2",
css: "header",
format: "common",
value: "Lookup",
},
{
cell: "A3",
css: "highlighting",
format: "common",
value: "HLOOKUP",
},
{ cell: "B3", format: "common", value: "=HLOOKUP(3,C3:C4,2)" },
{ cell: "C3", format: "common", value: 1 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "INDEX",
},
{ cell: "B4", format: "common", value: "=INDEX(C4:E4,C4,D4)" },
{ cell: "C4", format: "common", value: 1 },
{ cell: "D4", format: "common", value: 2 },
{ cell: "E4", format: "common", value: 3 },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "LOOKUP",
},
{ cell: "B5", format: "common", value: "=LOOKUP(C5,{11,22,33})" },
{ cell: "C5", format: "common", value: 22 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "MATCH",
},
{ cell: "B6", format: "common", value: "=MATCH(C6,{11,22,33})" },
{ cell: "C6", format: "common", value: 22 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "VLOOKUP",
},
{ cell: "B7", format: "common", value: "=VLOOKUP(1,C7:E7,E7)" },
{
cell: "C7",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 1,
},
{
cell: "D7",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 2,
},
{
cell: "E7",
css: "dhx_generated_class_u1652776833412",
format: "common",
value: 3,
},
{
cell: "A8",
css: "highlighting",
format: "common",
value: "XLOOKUP",
},
{ cell: "B8", format: "common", value: "=LOOKUP(C8,{11,22,33})" },
{
cell: "C8",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: 22,
},
{
cell: "A9",
css: "highlighting",
format: "common",
value: "XMATCH",
},
{ cell: "B9", format: "common", value: "=XMATCH(C9,{11,33})" },
{ cell: "C9", format: "common", value: 11 },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Math",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{ cell: "A2", css: "header", format: "common", value: "Math" },
{
cell: "A3",
css: "highlighting",
format: "common",
value: "ABS",
},
{ cell: "B3", format: "common", value: "=ABS(C3)" },
{ cell: "C3", format: "number", value: -5 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "ACOS",
},
{ cell: "B4", format: "common", value: "=ACOS(C4)" },
{ cell: "C4", format: "number", value: 0.5 },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "ACOSH",
},
{ cell: "B5", format: "common", value: "=ACOSH(C5)" },
{ cell: "C5", format: "number", value: 3 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "ACOT",
},
{ cell: "B6", format: "common", value: "=ACOT(C6)" },
{ cell: "C6", format: "number", value: 2 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "ACOTH",
},
{ cell: "B7", format: "common", value: "=ACOTH(C7)" },
{ cell: "C7", format: "number", value: 6 },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "ADD",
},
{ cell: "B8", format: "common", value: "=ADD(C8;D8)" },
{ cell: "C8", format: "number", value: 1 },
{ cell: "D8", format: "number", value: 100 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "ARABIC",
},
{ cell: "B9", format: "common", value: "=ARABIC(C9)" },
{ cell: "C9", format: "common", value: "LVII" },
{
cell: "A10",
css: "highlighting",
format: "common",
value: "ASIN",
},
{ cell: "B10", format: "common", value: "=ASIN(C10)" },
{ cell: "C10", format: "number", value: -0.5 },
{
cell: "A11",
css: "highlighting",
format: "common",
value: "ASINH",
},
{ cell: "B11", format: "common", value: "=ASINH(C11)" },
{ cell: "C11", format: "number", value: 10 },
{
cell: "A12",
css: "highlighting",
format: "common",
value: "ATAN",
},
{ cell: "B12", format: "common", value: "=ATAN(C12)" },
{ cell: "C12", format: "number", value: 1 },
{
cell: "A13",
css: "highlighting",
format: "common",
value: "ATAN2",
},
{ cell: "B13", format: "common", value: "=ATAN2(C13;D13)" },
{ cell: "C13", format: "number", value: 1 },
{ cell: "D13", format: "number", value: 1 },
{
cell: "A14",
css: "highlighting",
format: "common",
value: "ATANH",
},
{ cell: "B14", format: "common", value: "=ATANH(C14)" },
{ cell: "C14", format: "number", value: 0.76 },
{
cell: "A15",
css: "highlighting",
format: "common",
value: "AVEDEV",
},
{ cell: "B15", format: "common", value: "=AVEDEV(C15:H15)" },
{
cell: "C15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 50,
},
{
cell: "D15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 47,
},
{
cell: "E15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 52,
},
{
cell: "F15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 46,
},
{
cell: "G15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 45,
},
{
cell: "H15",
css: "dhx_generated_class_u1652779118277",
format: "number",
value: 48,
},
{
cell: "A16",
css: "highlighting",
format: "common",
value: "AVERAGE",
},
{ cell: "B16", format: "common", value: "=AVERAGE(C16;D16)" },
{ cell: "C16", format: "number", value: 5 },
{ cell: "D16", format: "number", value: 15 },
{
cell: "A17",
css: "highlighting",
format: "common",
value: "AVERAGEA",
},
{ cell: "B17", format: "common", value: "=AVERAGEA(C17:F17)" },
{ cell: "C17", format: "number", value: 2 },
{ cell: "D17", format: "number", value: 4 },
{ cell: "E17", format: "number", value: 6 },
{ cell: "F17", format: "number", value: 8 },
{
cell: "A18",
css: "highlighting",
format: "common",
value: "BASE",
},
{ cell: "B18", format: "common", value: "=BASE(C18;D18)" },
{ cell: "C18", format: "number", value: 7 },
{ cell: "D18", format: "number", value: 2 },
{
cell: "A19",
css: "highlighting",
format: "common",
value: "BITAND",
},
{ cell: "B19", format: "common", value: "=BITAND(C19,D19)" },
{ cell: "C19", format: "common", value: 1 },
{ cell: "D19", format: "common", value: 5 },
{
cell: "A20",
css: "highlighting",
format: "common",
value: "CEILING",
},
{ cell: "B20", format: "common", value: "=CEILING(C20;D20)" },
{ cell: "C20", format: "number", value: 2.5 },
{ cell: "D20", format: "number", value: 1 },
{
cell: "A21",
css: "highlighting",
format: "common",
value: "COMBIN",
},
{ cell: "B21", format: "common", value: "=COMBIN(C21;D21)" },
{ cell: "C21", format: "number", value: 8 },
{ cell: "D21", format: "number", value: 2 },
{
cell: "A22",
css: "highlighting",
format: "common",
value: "COMBINA",
},
{ cell: "B22", format: "common", value: "=COMBINA(C22;D22)" },
{ cell: "C22", format: "number", value: 4 },
{ cell: "D22", format: "number", value: 3 },
{
cell: "A23",
css: "highlighting",
format: "common",
value: "COS",
},
{ cell: "B23", format: "common", value: "=COS(C23)" },
{ cell: "C23", format: "number", value: 1.021 },
{
cell: "A24",
css: "highlighting",
format: "common",
value: "COSH",
},
{ cell: "B24", format: "common", value: "=COSH(C24)" },
{ cell: "C24", format: "number", value: 4 },
{
cell: "A25",
css: "highlighting",
format: "common",
value: "COT",
},
{ cell: "B25", format: "common", value: "=COT(C25)" },
{ cell: "C25", format: "number", value: 30 },
{
cell: "A26",
css: "highlighting",
format: "common",
value: "COTH",
},
{ cell: "B26", format: "common", value: "=COTH(C26)" },
{ cell: "C26", format: "number", value: 2 },
{
cell: "A27",
css: "highlighting",
format: "common",
value: "COUNT",
},
{ cell: "B27", format: "common", value: "=COUNT(C27;D27)" },
{ cell: "C27", format: "number", value: 5 },
{ cell: "D27", format: "common", value: "some text" },
{
cell: "A28",
css: "highlighting",
format: "common",
value: "COUNTA",
},
{ cell: "B28", format: "common", value: "=COUNTA(C28;D28)" },
{ cell: "C28", format: "number", value: 5 },
{ cell: "D28", format: "common", value: "some text" },
{
cell: "A29",
css: "highlighting",
format: "common",
value: "COUNTBLANK",
},
{ cell: "B29", format: "common", value: "=COUNTBLANK(C29:D29)" },
{ cell: "C29", format: "number", value: 0 },
{
cell: "A30",
css: "highlighting",
format: "common",
value: "CSC",
},
{ cell: "B30", format: "common", value: "=CSC(C30)" },
{ cell: "C30", format: "number", value: 15 },
{
cell: "A31",
css: "highlighting",
format: "common",
value: "CSCH",
},
{ cell: "B31", format: "common", value: "=CSCH(C31)" },
{ cell: "C31", format: "number", value: 1.5 },
{
cell: "A32",
css: "highlighting",
format: "common",
value: "DECIMAL",
},
{ cell: "B32", format: "common", value: "=DECIMAL(C32;D32)" },
{ cell: "C32", format: "number", value: 111 },
{ cell: "D32", format: "number", value: 2 },
{
cell: "A33",
css: "highlighting",
format: "common",
value: "DEGREES",
},
{ cell: "B33", format: "common", value: "=DEGREES(C33)" },
{ cell: "C33", format: "number", value: 5 },
{
cell: "A34",
css: "highlighting",
format: "common",
value: "DIVIDE",
},
{ cell: "B34", format: "common", value: "=DIVIDE(C34;D34)" },
{ cell: "C34", format: "number", value: 4 },
{ cell: "D34", format: "number", value: 2 },
{
cell: "A35",
css: "highlighting",
format: "common",
value: "EQ",
},
{
cell: "B35",
format: "common",
value: '=IF(C35=D35,"Yes","No")',
},
{ cell: "C35", format: "number", value: 3 },
{ cell: "D35", format: "number", value: 3 },
{
cell: "A36",
css: "highlighting",
format: "common",
value: "EVEN",
},
{ cell: "B36", format: "common", value: "=EVEN(C36)" },
{ cell: "C36", format: "number", value: 5.7 },
{
cell: "A37",
css: "highlighting",
format: "common",
value: "FACT",
},
{ cell: "B37", format: "common", value: "=FACT(C37)" },
{ cell: "C37", format: "number", value: 5 },
{
cell: "A38",
css: "highlighting",
format: "common",
value: "FACTDOUBLE",
},
{ cell: "B38", format: "common", value: "=FACTDOUBLE(C38)" },
{ cell: "C38", format: "number", value: 6 },
{
cell: "A39",
css: "highlighting",
format: "common",
value: "FLOOR",
},
{ cell: "B39", format: "common", value: "=FLOOR(C39;D39)" },
{ cell: "C39", format: "number", value: 3.7 },
{ cell: "D39", format: "number", value: 2 },
{
cell: "A40",
css: "highlighting",
format: "common",
value: "GCD",
},
{ cell: "B40", format: "common", value: "=GCD(C40;D40)" },
{ cell: "C40", format: "number", value: 60 },
{ cell: "D40", format: "number", value: 36 },
{
cell: "A41",
css: "highlighting",
format: "common",
value: "GT",
},
{
cell: "B41",
format: "common",
value: '=IF(C41>D41,"Yes","No")',
},
{ cell: "C41", format: "number", value: 2 },
{ cell: "D41", format: "number", value: 3 },
{
cell: "A42",
css: "highlighting",
format: "common",
value: "GTE",
},
{
cell: "B42",
format: "common",
value: '=IF(C42>=D42,"Yes","No")',
},
{ cell: "C42", format: "number", value: 3 },
{ cell: "D42", format: "number", value: 3 },
{
cell: "A43",
css: "highlighting",
format: "common",
value: "INT",
},
{ cell: "B43", format: "common", value: "=INT(C43)" },
{ cell: "C43", format: "number", value: 5.7 },
{
cell: "A44",
css: "highlighting",
format: "common",
value: "LN",
},
{ cell: "B44", format: "common", value: "=LN(C44)" },
{ cell: "C44", format: "number", value: 86 },
{
cell: "A45",
css: "highlighting",
format: "common",
value: "LOG",
},
{ cell: "B45", format: "common", value: "=LOG(C45)" },
{ cell: "C45", format: "number", value: 10 },
{
cell: "A46",
css: "highlighting",
format: "common",
value: "LOG10",
},
{ cell: "B46", format: "common", value: "=LOG10(C46)" },
{ cell: "C46", format: "number", value: 10 },
{
cell: "A47",
css: "highlighting",
format: "common",
value: "LT",
},
{
cell: "B47",
format: "common",
value: '=IF(C47<D47,"Yes","No")',
},
{ cell: "C47", format: "number", value: 2 },
{ cell: "D47", format: "number", value: 3 },
{
cell: "A48",
css: "highlighting",
format: "common",
value: "LTE",
},
{
cell: "B48",
format: "common",
value: '=IF(C48<=D48,"Yes","No")',
},
{ cell: "C48", format: "number", value: 3 },
{ cell: "D48", format: "number", value: 3 },
{
cell: "A49",
css: "highlighting",
format: "common",
value: "MAX",
},
{ cell: "B49", format: "common", value: "=MAX(C49;D49)" },
{ cell: "C49", format: "number", value: 5 },
{ cell: "D49", format: "number", value: 15 },
{
cell: "A50",
css: "highlighting",
format: "common",
value: "MIN",
},
{ cell: "B50", format: "common", value: "=MIN(C50;D50)" },
{ cell: "C50", format: "number", value: 5 },
{ cell: "D50", format: "number", value: 2 },
{
cell: "A51",
css: "highlighting",
format: "common",
value: "MINUS",
},
{ cell: "B51", format: "common", value: "=MINUS(C51;D51)" },
{ cell: "C51", format: "number", value: 1 },
{ cell: "D51", format: "number", value: 100 },
{
cell: "A52",
css: "highlighting",
format: "common",
value: "MOD",
},
{ cell: "B52", format: "common", value: "=MOD(C52;D52)" },
{ cell: "C52", format: "number", value: 3 },
{ cell: "D52", format: "number", value: 2 },
{
cell: "A53",
css: "highlighting",
format: "common",
value: "MROUND",
},
{ cell: "B53", format: "common", value: "=MROUND(C53;D53)" },
{ cell: "C53", format: "number", value: 10 },
{ cell: "D53", format: "number", value: 3 },
{
cell: "A54",
css: "highlighting",
format: "common",
value: "MULTINOMIAL",
},
{ cell: "B54", format: "common", value: "=MULTINOMIAL(C54;D54)" },
{ cell: "C54", format: "number", value: 2 },
{ cell: "D54", format: "number", value: 3 },
{
cell: "A55",
css: "highlighting",
format: "common",
value: "MULTIPLY",
},
{ cell: "B55", format: "common", value: "=MULTIPLY(C55;D55)" },
{ cell: "C55", format: "number", value: 2 },
{ cell: "D55", format: "number", value: 2 },
{
cell: "A56",
css: "highlighting",
format: "common",
value: "NE",
},
{ cell: "B56", format: "common", value: "=NE(C56,D56)" },
{ cell: "C56", format: "number", value: 3 },
{ cell: "D56", format: "number", value: 3 },
{
cell: "A57",
css: "highlighting",
format: "common",
value: "ODD",
},
{ cell: "B57", format: "common", value: "=ODD(C57)" },
{ cell: "C57", format: "number", value: 5 },
{
cell: "A58",
css: "highlighting",
format: "common",
value: "PI",
},
{ cell: "B58", format: "common", value: "=PI()" },
{
cell: "A59",
css: "highlighting",
format: "common",
value: "POW",
},
{ cell: "B59", format: "common", value: "=POW(C59;D59)" },
{ cell: "C59", format: "number", value: 5 },
{ cell: "D59", format: "number", value: 2 },
{
cell: "A60",
css: "highlighting",
format: "common",
value: "POWER",
},
{ cell: "B60", format: "common", value: "=POWER(C60;D60)" },
{ cell: "C60", format: "number", value: 5 },
{ cell: "D60", format: "number", value: 2 },
{
cell: "A61",
css: "highlighting",
format: "common",
value: "PRODUCT",
},
{ cell: "B61", format: "common", value: "=PRODUCT(C61;D61)" },
{ cell: "C61", format: "number", value: 5 },
{ cell: "D61", format: "number", value: 2 },
{
cell: "A62",
css: "highlighting",
format: "common",
value: "QUOTIENT",
},
{ cell: "B62", format: "common", value: "=QUOTIENT(C62;D62)" },
{ cell: "C62", format: "number", value: 5 },
{ cell: "D62", format: "number", value: 2 },
{
cell: "A63",
css: "highlighting",
format: "common",
value: "RADIANS",
},
{ cell: "B63", format: "common", value: "=RADIANS(C63)" },
{ cell: "C63", format: "number", value: 270 },
{
cell: "A64",
css: "highlighting",
format: "common",
value: "RAND",
},
{ cell: "B64", format: "common", value: "=RAND()" },
{
cell: "A65",
css: "highlighting",
format: "common",
value: "RANDBETWEEN",
},
{ cell: "B65", format: "common", value: "=RANDBETWEEN(C65;D65)" },
{ cell: "C65", format: "number", value: 1 },
{ cell: "D65", format: "number", value: 100 },
{
cell: "A66",
css: "highlighting",
format: "common",
value: "ROMAN",
},
{ cell: "B66", format: "common", value: "=ROMAN(C66)" },
{ cell: "C66", format: "number", value: 10 },
{
cell: "A67",
css: "highlighting",
format: "common",
value: "ROUND",
},
{ cell: "B67", format: "common", value: "=ROUND(C67;D67)" },
{ cell: "C67", format: "number", value: 5.73758 },
{ cell: "D67", format: "number", value: 2 },
{
cell: "A68",
css: "highlighting",
format: "common",
value: "ROUNDDOWN",
},
{ cell: "B68", format: "common", value: "=ROUNDDOWN(C68;D68)" },
{ cell: "C68", format: "number", value: 5.73758 },
{ cell: "D68", format: "number", value: 2 },
{
cell: "A69",
css: "highlighting",
format: "common",
value: "ROUNDUP",
},
{ cell: "B69", format: "common", value: "=ROUNDUP(C69;D69)" },
{ cell: "C69", format: "number", value: 5.78658 },
{ cell: "D69", format: "number", value: 2 },
{
cell: "A70",
css: "highlighting",
format: "common",
value: "SEC",
},
{ cell: "B70", format: "common", value: "=SEC(C70)" },
{ cell: "C70", format: "number", value: 45 },
{
cell: "A71",
css: "highlighting",
format: "common",
value: "SECH",
},
{ cell: "B71", format: "common", value: "=SECH(C71)" },
{ cell: "C71", format: "number", value: 45 },
{
cell: "A72",
css: "highlighting",
format: "common",
value: "SIN",
},
{ cell: "B72", format: "common", value: "=SIN(C72)" },
{ cell: "C72", format: "number", value: 30 },
{
cell: "A73",
css: "highlighting",
format: "common",
value: "SINH",
},
{ cell: "B73", format: "common", value: "=SINH(C73)" },
{ cell: "C73", format: "number", value: 1 },
{
cell: "A74",
css: "highlighting",
format: "common",
value: "SQRT",
},
{ cell: "B74", format: "common", value: "=SQRT(C74)" },
{ cell: "C74", format: "number", value: 5 },
{
cell: "A75",
css: "highlighting",
format: "common",
value: "SQRTPI",
},
{ cell: "B75", format: "common", value: "=SQRTPI(C75)" },
{ cell: "C75", format: "number", value: 1 },
{
cell: "A76",
css: "highlighting",
format: "common",
value: "STDEV",
},
{ cell: "B76", format: "common", value: "=STDEV(C76;D76)" },
{ cell: "C76", format: "number", value: 5 },
{ cell: "D76", format: "number", value: 2 },
{
cell: "A77",
css: "highlighting",
format: "percent",
value: "STDEV.S",
},
{ cell: "B77", format: "common", value: "=STDEV.S(C77:I77)" },
{ cell: "C77", format: "number", value: 102 },
{ cell: "D77", format: "number", value: 99 },
{ cell: "E77", format: "number", value: 97 },
{ cell: "F77", format: "number", value: 100 },
{ cell: "G77", format: "number", value: 98 },
{ cell: "H77", format: "number", value: 105 },
{ cell: "I77", format: "number", value: 102 },
{
cell: "A78",
css: "highlighting",
format: "common",
value: "STDEVA",
},
{ cell: "B78", format: "common", value: "=STDEVA(C78;D78)" },
{ cell: "C78", format: "number", value: 5 },
{ cell: "D78", format: "number", value: 2 },
{
cell: "A79",
css: "highlighting",
format: "common",
value: "STDEVP",
},
{ cell: "B79", format: "common", value: "=STDEVP(C79;D79)" },
{ cell: "C79", format: "number", value: 5 },
{ cell: "D79", format: "number", value: 2 },
{
cell: "A80",
css: "highlighting",
format: "common",
value: "STDEVPA",
},
{ cell: "B80", format: "common", value: "=STDEVPA(C80;D80)" },
{ cell: "C80", format: "number", value: 5 },
{ cell: "D80", format: "number", value: 2 },
{
cell: "A81",
css: "highlighting",
format: "common",
value: "SUBTOTAL",
},
{
cell: "B81",
format: "common",
value: "=SUBTOTAL(C81;D81:F81)",
},
{ cell: "C81", format: "common", value: 9 },
{ cell: "D81", format: "number", value: 115 },
{ cell: "E81", format: "number", value: 10 },
{ cell: "F81", format: "number", value: 90 },
{
cell: "A82",
css: "highlighting",
format: "common",
value: "SUM",
},
{ cell: "B82", format: "common", value: "=SUM(C82;D82)" },
{ cell: "C82", format: "number", value: 5 },
{ cell: "D82", format: "number", value: 2 },
{
cell: "A83",
css: "highlighting",
format: "common",
value: "SUMPRODUCT",
},
{
cell: "B83",
format: "common",
value: "=SUMPRODUCT(C83:E83;F83:H83)",
},
{
cell: "C83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "D83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "E83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "F83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "G83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "H83",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "A84",
css: "highlighting",
format: "common",
value: "SUMSQ",
},
{ cell: "B84", format: "common", value: "=SUMSQ(C84;D84)" },
{ cell: "C84", format: "number", value: 5 },
{ cell: "D84", format: "number", value: 2 },
{
cell: "A85",
css: "highlighting",
format: "common",
value: "SUMX2MY2",
},
{
cell: "B85",
format: "common",
value: "=SUMX2MY2(C85:F85;G85:J85)",
},
{
cell: "C85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "D85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "E85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "F85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "G85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "H85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "I85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "J85",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "A86",
css: "highlighting",
format: "common",
value: "SUMX2PY2",
},
{
cell: "B86",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "=SUMX2PY2(C86:G86,H86:L86)",
},
{
cell: "C86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "D86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "E86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "F86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "G86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "H86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "I86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "J86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "K86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "L86",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "A87",
css: "highlighting",
format: "common",
value: "SUMXMY2",
},
{
cell: "B87",
css: "dhx_generated_class_u1652788414258",
format: "common",
value: "=SUMXMY2(C87:G87,H87:L87)",
},
{
cell: "C87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "D87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "E87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "F87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "G87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 5,
},
{
cell: "H87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "I87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "J87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "K87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "L87",
css: "dhx_generated_class_u1652788414258",
format: "number",
value: 2,
},
{
cell: "A88",
css: "highlighting",
format: "common",
value: "TAN",
},
{ cell: "B88", format: "common", value: "=TAN(C88)" },
{ cell: "C88", format: "number", value: 0.785 },
{
cell: "A89",
css: "highlighting",
format: "common",
value: "TANH",
},
{ cell: "B89", format: "common", value: "=TANH(C89)" },
{ cell: "C89", format: "number", value: 0.5 },
{
cell: "A90",
css: "highlighting",
format: "common",
value: "TRUNC",
},
{ cell: "B90", format: "common", value: "=TRUNC(C90)" },
{ cell: "C90", format: "number", value: 5.64 },
{
cell: "A91",
css: "highlighting",
format: "common",
value: "VAR",
},
{ cell: "B91", format: "common", value: "=VAR(C91:F91)" },
{ cell: "C91", format: "number", value: 5 },
{ cell: "D91", format: "number", value: 2 },
{ cell: "E91", format: "number", value: 5 },
{ cell: "F91", format: "number", value: 2 },
{
cell: "A92",
css: "highlighting",
format: "common",
value: "VAR.S",
},
{ cell: "B92", format: "common", value: "=VAR.S(C92:G92)" },
{ cell: "C92", format: "number", value: 50 },
{ cell: "D92", format: "number", value: 47 },
{ cell: "E92", format: "number", value: 52 },
{ cell: "F92", format: "number", value: 46 },
{ cell: "G92", format: "number", value: 45 },
{
cell: "A93",
css: "highlighting",
format: "common",
value: "VARA",
},
{ cell: "B93", format: "common", value: "=VARA(C93:F93)" },
{ cell: "C93", format: "number", value: 5 },
{ cell: "D93", format: "number", value: 2 },
{ cell: "E93", format: "number", value: 5 },
{ cell: "F93", format: "number", value: 2 },
{
cell: "A94",
css: "highlighting",
format: "common",
value: "VARP",
},
{ cell: "B94", format: "common", value: "=VARP(C94:F94)" },
{ cell: "C94", format: "number", value: 5 },
{ cell: "D94", format: "number", value: 2 },
{ cell: "E94", format: "number", value: 5 },
{ cell: "F94", format: "number", value: 2 },
{
cell: "A95",
css: "highlighting",
format: "common",
value: "VARPA",
},
{ cell: "B95", format: "common", value: "=VARPA(C95;D95)" },
{ cell: "C95", format: "number", value: 5 },
{ cell: "D95", format: "number", value: 2 },
{ cell: "A96", css: "highlighting", format: "common", value: "AVERAGEIF" },
{ cell: "B96", format: "common", value: '=AVERAGEIF(C96:C101;">3";D96:D101)' },
{ cell: "C96", format: "number", value: 1 },
{ cell: "D96", format: "number", value: 10 },
{ cell: "C97", format: "number", value: 5 },
{ cell: "D97", format: "number", value: 20 },
{ cell: "C98", format: "number", value: 2 },
{ cell: "D98", format: "number", value: 30 },
{ cell: "C99", format: "number", value: 7 },
{ cell: "D99", format: "number", value: 40 },
{ cell: "C100", format: "number", value: 4 },
{ cell: "D100", format: "number", value: 50 },
{ cell: "C101", format: "number", value: 8 },
{ cell: "D101", format: "number", value: 60 },
{ cell: "A102", css: "highlighting", format: "common", value: "AVERAGEIFS" },
{
cell: "B102",
format: "common",
value: '=AVERAGEIFS(D96:D101;C96:C101;">3";D96:D101;">20")',
},
{ cell: "A103", css: "highlighting", format: "common", value: "COUNTIF" },
{ cell: "B103", format: "common", value: '=COUNTIF(C96:C101;">3")' },
{ cell: "A104", css: "highlighting", format: "common", value: "COUNTIFS" },
{ cell: "B104", format: "common", value: '=COUNTIFS(C96:C101;">3";D96:D101;">30")' },
{ cell: "A105", css: "highlighting", format: "common", value: "MAXIFS" },
{ cell: "B105", format: "common", value: '=MAXIFS(D96:D101;C96:C101;">3")' },
{ cell: "A106", css: "highlighting", format: "common", value: "MINIFS" },
{ cell: "B106", format: "common", value: '=MINIFS(D96:D101;C96:C101;">3")' },
{ cell: "A107", css: "highlighting", format: "common", value: "SUMIF" },
{ cell: "B107", format: "common", value: '=SUMIF(C96:C101;">3";D96:D101)' },
{ cell: "A108", css: "highlighting", format: "common", value: "SUMIFS" },
{ cell: "B108", format: "common", value: '=SUMIFS(D96:D101;C96:C101;">3";D96:D101;">20")' },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Regex",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{ cell: "A2", css: "header", format: "common", value: "Regex" },
{
cell: "A3",
css: "highlighting",
format: "common",
value: "REGEXEXTRACT",
},
{ cell: "B3", format: "common", value: "=REGEXEXTRACT(C3;D3)" },
{ cell: "C3", format: "text", value: "DHTMLX Spreadsheet" },
{ cell: "D3", format: "text", value: "DHTMLX" },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "REGEXMATCH",
},
{ cell: "B4", format: "common", value: "=REGEXMATCH(C4;D4)" },
{ cell: "C4", format: "text", value: "You are happy" },
{ cell: "D4", format: "text", value: "happy" },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "REGEXREPLACE",
},
{
cell: "B5",
format: "common",
value: '=REGEXREPLACE(C5, "[0-9]+", "777")',
},
{ cell: "C5", format: "common", value: "Google Doc 101" },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "String",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{
cell: "A2",
css: "header",
format: "common",
value: "String",
},
{
cell: "A3",
css: "highlighting",
format: "common",
value: "ARRAYTOTEXT",
},
{ cell: "B3", format: "common", value: "=ARRAYTOTEXT(C3:E3)" },
{ cell: "C3", format: "common", value: "RED" },
{ cell: "D3", format: "common", value: "BLUE" },
{ cell: "E3", format: "common", value: "GREEN" },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "CHAR",
},
{ cell: "B4", format: "common", value: "=CHAR(C4)" },
{ cell: "C4", format: "number", value: 67 },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "CLEAN",
},
{ cell: "B5", format: "common", value: "=CLEAN(C5)" },
{ cell: "C5", format: "common", value: "some" },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "CODE",
},
{ cell: "B6", format: "common", value: "=CODE(C6)" },
{ cell: "C6", format: "common", value: "a" },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "CONCATENATE",
},
{
cell: "B7",
format: "common",
value: '=CONCATENATE(C7;" ";D7)',
},
{ cell: "C7", format: "common", value: "some" },
{ cell: "D7", format: "common", value: "text" },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "DOLLAR",
},
{ cell: "B8", format: "common", value: "=DOLLAR(C8;D8)" },
{ cell: "C8", format: "number", value: 120.1 },
{ cell: "D8", format: "number", value: 0 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "EXACT",
},
{ cell: "B9", format: "common", value: "=EXACT(C9;D9)" },
{ cell: "C9", format: "common", value: "text" },
{ cell: "D9", format: "common", value: "text" },
{
cell: "A10",
css: "highlighting",
format: "common",
value: "FIND",
},
{ cell: "B10", format: "common", value: "=FIND(C10;D10)" },
{ cell: "C10", format: "common", value: "t" },
{ cell: "D10", format: "common", value: "text" },
{
cell: "A11",
css: "highlighting",
format: "common",
value: "FIXED",
},
{ cell: "B11", format: "common", value: "=FIXED(C11;D11)" },
{ cell: "C11", format: "number", value: 1234.567 },
{ cell: "D11", format: "number", value: 1 },
{
cell: "A12",
css: "highlighting",
format: "common",
value: "JOIN",
},
{ cell: "B12", format: "common", value: '=JOIN("_",C12,D12)' },
{ cell: "C12", format: "common", value: "DHTMLX" },
{ cell: "D12", format: "common", value: "Docs" },
{
cell: "A13",
css: "highlighting",
format: "common",
value: "LEFT",
},
{ cell: "B13", format: "common", value: "=LEFT(C13; 2)" },
{ cell: "C13", format: "common", value: "some" },
{
cell: "A14",
css: "highlighting",
format: "common",
value: "LEN",
},
{ cell: "B14", format: "common", value: "=LEN(C14)" },
{ cell: "C14", format: "common", value: "some" },
{
cell: "A15",
css: "highlighting",
format: "common",
value: "LOWER",
},
{ cell: "B15", format: "common", value: "=LOWER(C15)" },
{ cell: "C15", format: "common", value: "SOME" },
{
cell: "A16",
css: "highlighting",
format: "common",
value: "MID",
},
{ cell: "B16", format: "common", value: "=MID(C16; 2; 2)" },
{ cell: "C16", format: "common", value: "some" },
{
cell: "A17",
css: "highlighting",
format: "common",
value: "NUMBERVALUE",
},
{
cell: "B17",
format: "common",
value: "=NUMBERVALUE(C17,E17,D17)",
},
{ cell: "C17", format: "common", value: '="3/521,48"' },
{ cell: "D17", format: "common", value: "/" },
{ cell: "E17", format: "common", value: "," },
{
cell: "A18",
css: "highlighting",
format: "common",
value: "PROPER",
},
{ cell: "B18", format: "common", value: "=PROPER(C18)" },
{ cell: "C18", format: "common", value: "some" },
{
cell: "A19",
css: "highlighting",
format: "common",
value: "REPLACE",
},
{
cell: "B19",
format: "common",
value: '=REPLACE(C19,6,5,"*")',
},
{ cell: "C19", format: "number", value: "abcdefghijk" },
{
cell: "A20",
css: "highlighting",
format: "common",
value: "REPT",
},
{ cell: "B20", format: "common", value: "=REPT(C20;D20)" },
{ cell: "C20", format: "common", value: "s" },
{ cell: "D20", format: "number", value: 3 },
{
cell: "A21",
css: "highlighting",
format: "common",
value: "RIGHT",
},
{ cell: "B21", format: "common", value: "=RIGHT(C21; 2)" },
{ cell: "C21", format: "common", value: "some" },
{
cell: "A22",
css: "highlighting",
format: "common",
value: "SEARCH",
},
{ cell: "B22", format: "common", value: "=SEARCH(C22;D22)" },
{ cell: "C22", format: "common", value: "m" },
{ cell: "D22", format: "common", value: "some" },
{
cell: "A23",
css: "highlighting",
format: "common",
value: "SUBSTITUTE",
},
{
cell: "B23",
format: "common",
value: "=SUBSTITUTE(C23;D23;E23)",
},
{ cell: "C23", format: "common", value: "text" },
{ cell: "D23", format: "common", value: "t" },
{ cell: "E23", format: "common", value: "s" },
{
cell: "A24",
css: "highlighting",
format: "common",
value: "T",
},
{ cell: "B24", format: "common", value: "=T(C24)" },
{ cell: "C24", format: "common", value: "Start" },
{
cell: "A25",
css: "highlighting",
format: "common",
value: "TRIM",
},
{ cell: "B25", format: "common", value: "=TRIM(C25)" },
{ cell: "C25", format: "common", value: "some" },
{
cell: "A26",
css: "highlighting",
format: "common",
value: "UPPER",
},
{ cell: "B26", format: "common", value: "=UPPER(C26)" },
{ cell: "C26", format: "common", value: "some" },
],
cols: [{ width: 180 }],
rows: [],
},
{
name: "Arrays",
data: [
{ cell: "A1", css: "header", value: "Formula name" },
{ cell: "B1", css: "header", value: "Formula example" },
{ cell: "G1", css: "header", value: "Data for formula" },
{ cell: "A2", css: "header", value: "Array functions" },
{ cell: "A3", css: "highlighting", value: "CHOOSECOLS" },
{ cell: "B3", value: "=CHOOSECOLS(G3:I5,1,3)" },
{ cell: "G3", value: 1 },
{ cell: "H3", value: 2 },
{ cell: "I3", value: 3 },
{ cell: "G4", value: 4 },
{ cell: "H4", value: 5 },
{ cell: "I4", value: 6 },
{ cell: "G5", value: 7 },
{ cell: "H5", value: 8 },
{ cell: "I5", value: 9 },
{ cell: "A8", css: "highlighting", value: "CHOOSEROWS" },
{ cell: "B8", value: "=CHOOSEROWS(G8:H10,1,3)" },
{ cell: "G8", value: "R1-C1" },
{ cell: "H8", value: "R1-C2" },
{ cell: "G9", value: "R2-C1" },
{ cell: "H9", value: "R2-C2" },
{ cell: "G10", value: "R3-C1" },
{ cell: "H10", value: "R3-C2" },
{ cell: "A13", css: "highlighting", value: "DROP" },
{ cell: "B13", value: "=DROP(G13:H15,1)" },
{ cell: "G13", value: "Del" },
{ cell: "H13", value: "Del" },
{ cell: "G14", value: "Keep" },
{ cell: "H14", value: "Keep" },
{ cell: "G15", value: "Keep" },
{ cell: "H15", value: "Keep" },
{ cell: "A18", css: "highlighting", value: "EXPAND" },
{ cell: "B18", value: "=EXPAND(G18:H18,3,3,0)" },
{ cell: "G18", value: 1 },
{ cell: "H18", value: 2 },
{ cell: "A22", css: "highlighting", value: "RANDARRAY" },
{ cell: "B22", value: "=RANDARRAY(3,2,1,100,TRUE())" },
{ cell: "A27", css: "highlighting", value: "SEQUENCE" },
{ cell: "B27", value: "=SEQUENCE(5,1,1,1)" },
{ cell: "A34", css: "highlighting", value: "SORT" },
{ cell: "B34", value: "=SORT(G34:G38,1,-1)" },
{ cell: "G34", value: 10 },
{ cell: "G35", value: 50 },
{ cell: "G36", value: 20 },
{ cell: "G37", value: 40 },
{ cell: "G38", value: 30 },
{ cell: "A40", css: "highlighting", value: "SORTBY" },
{ cell: "B40", value: "=SORTBY(G40:G42,H40:H42,1)" },
{ cell: "G40", value: "Item A" },
{ cell: "H40", value: 3 },
{ cell: "G41", value: "Item B" },
{ cell: "H41", value: 1 },
{ cell: "G42", value: "Item C" },
{ cell: "H42", value: 2 },
{ cell: "A44", css: "highlighting", value: "TAKE" },
{ cell: "B44", value: "=TAKE(G44:I46,2)" },
{ cell: "G44", value: "A" },
{ cell: "H44", value: "A" },
{ cell: "I44", value: "A" },
{ cell: "G45", value: "B" },
{ cell: "H45", value: "B" },
{ cell: "I45", value: "B" },
{ cell: "G46", value: "C" },
{ cell: "H46", value: "C" },
{ cell: "I46", value: "C" },
{ cell: "A49", css: "highlighting", value: "TEXTSPLIT" },
{ cell: "B49", value: '=TEXTSPLIT(G49,",")' },
{ cell: "G49", value: "Apple,Orange,Banana" },
{ cell: "A52", css: "highlighting", value: "TOCOL" },
{ cell: "B52", value: "=TOCOL(G52:H53)" },
{ cell: "G52", value: "R1C1" },
{ cell: "H52", value: "R1C2" },
{ cell: "G53", value: "R2C1" },
{ cell: "H53", value: "R2C2" },
{ cell: "A57", css: "highlighting", value: "TOROW" },
{ cell: "B57", value: "=TOROW(G57:G59)" },
{ cell: "G57", value: 1 },
{ cell: "G58", value: 2 },
{ cell: "G59", value: 3 },
{ cell: "A61", css: "highlighting", value: "UNIQUE" },
{ cell: "B61", value: "=UNIQUE(G61:G65)" },
{ cell: "G61", value: 1 },
{ cell: "G62", value: 2 },
{ cell: "G63", value: 2 },
{ cell: "G64", value: 3 },
{ cell: "G65", value: 1 },
{ cell: "A67", css: "highlighting", value: "WRAPCOLS" },
{ cell: "B67", value: "=WRAPCOLS(G67:G72,2)" },
{ cell: "G67", value: 1 },
{ cell: "G68", value: 2 },
{ cell: "G69", value: 3 },
{ cell: "G70", value: 4 },
{ cell: "G71", value: 5 },
{ cell: "G72", value: 6 },
{ cell: "A73", css: "highlighting", value: "WRAPROWS" },
{ cell: "B73", value: "=WRAPROWS(G73:G78,3)" },
{ cell: "G73", value: "a" },
{ cell: "G74", value: "b" },
{ cell: "G75", value: "c" },
{ cell: "G76", value: "d" },
{ cell: "G77", value: "e" },
{ cell: "G78", value: "f" },
],
cols: [{ width: 180 }, { width: 300 }],
rows: [],
},
{
name: "Other",
data: [
{
cell: "A1",
css: "header",
format: "common",
value: "Formula name",
},
{
cell: "B1",
css: "header",
format: "common",
value: "Formula example",
},
{
cell: "C1",
css: "header",
format: "common",
value: "Data for formula",
},
{ cell: "A2", css: "header", format: "common", value: "Other" },
{
cell: "A3",
css: "highlighting",
format: "common",
value: "AND",
},
{ cell: "B3", format: "common", value: "=AND(C3>D3;C3<E3)" },
{ cell: "C3", format: "number", value: 70 },
{ cell: "D3", format: "number", value: 60 },
{ cell: "E3", format: "number", value: 100 },
{
cell: "A4",
css: "highlighting",
format: "common",
value: "CHOOSE",
},
{ cell: "B4", format: "common", value: "=CHOOSE(C4;D4;E4;F4)" },
{ cell: "C4", format: "number", value: 2 },
{ cell: "D4", format: "number", value: 60 },
{ cell: "E4", format: "common", value: "some" },
{ cell: "F4", format: "number", value: 2 },
{
cell: "A5",
css: "highlighting",
format: "common",
value: "FALSE",
},
{ cell: "B5", format: "common", value: "=IF(C5>D5),FALSE()" },
{ cell: "C5", format: "number", value: 5 },
{ cell: "D5", format: "number", value: 7 },
{
cell: "A6",
css: "highlighting",
format: "common",
value: "IF",
},
{
cell: "B6",
format: "common",
value: '=IF(C6>D6;"Pass","Fail")',
},
{ cell: "C6", format: "number", value: 70 },
{ cell: "D6", format: "number", value: 60 },
{
cell: "A7",
css: "highlighting",
format: "common",
value: "NOT",
},
{ cell: "B7", format: "common", value: "=NOT(C7=D7)" },
{ cell: "C7", format: "common", value: "text" },
{ cell: "D7", format: "common", value: "some" },
{
cell: "A8",
css: "highlighting",
format: "common",
value: "OR",
},
{ cell: "B8", format: "common", value: "=OR(C8>D8;C8=D8)" },
{ cell: "C8", format: "number", value: 5 },
{ cell: "D8", format: "number", value: 2 },
{
cell: "A9",
css: "highlighting",
format: "common",
value: "TRUE",
},
{ cell: "B9", format: "common", value: "=IF(C9>D9),TRUE()" },
{ cell: "C9", format: "number", value: 7 },
{ cell: "D9", format: "number", value: 5 },
],
cols: [{ width: 180 }],
rows: [],
},
],
styles: {
header: { "font-weight": "bold", "white-space": "nowrap" },
highlighting: { color: "#0b72a5", "white-space": "nowrap" },
dhx_generated_class_u1652788414258: { "white-space": "nowrap" },
dhx_generated_class_u1652776833412: { "white-space": "nowrap" },
dhx_generated_class_u1652779118277: { "white-space": "nowrap" },
},
formats: [
{ name: "Common", id: "common", mask: "", example: "1500.31" },
{
name: "Number",
id: "number",
mask: "#,##0.00",
example: "1500.31",
},
{
name: "Percent",
id: "percent",
mask: "#,##0.00%",
example: "15.0031",
},
{
name: "Currency",
id: "currency",
mask: "$#,##0.00",
example: "1500.31",
},
{
name: "Date",
id: "date",
mask: "mm-dd-yy",
example: "44490",
dateFormat: "%d/%m/%Y",
},
{
name: "Time",
id: "time",
mask: "h:mm:ss am/pm",
example: "0.5625",
timeFormat: 12,
},
{ name: "Text", id: "text", mask: "@", example: "some text" },
],
};
</script>Developers evaluating a JavaScript spreadsheet component need to know which formulas it supports before committing to an implementation. A live cheat sheet with working examples of each function category lets teams verify compatibility with their data processing requirements without writing test code.
This example loads a prepared multi-sheet formulas dataset that contains working examples across categories such as Boolean, Date, Financial, Information, Lookup, Math, Regex, String, and Other. The Spreadsheet then sorts those sheets so the cheat sheet is easier to browse.
Solution overview
- Create the Spreadsheet with
new dhx.Spreadsheet("spreadsheet", { menu: true }) - Load the prepared formulas reference with
spreadsheet.parse(formulas) - Sort the reference ranges with
spreadsheet.sortCells(...)so entries appear in order on each sheet - Browse the computed formulas and sample inputs across the loaded sheets
Key points
- Cross-cell references: Formulas can reference individual cells (
A1), ranges (A1:A10), and cells on other sheets (Sheet2!A1) - Multi-sheet reference: The sample organizes formula examples by category across multiple sheets such as Boolean, Date, Math, and String
- Sorted presentation: The sample uses
sortCells(...)after parsing to keep the cheat-sheet entries ordered on each sheet - Boolean operators: Standard comparison operators (
=,<>,>,<,>=,<=) work inside formula expressions
API reference
- parse(): Loads the prepared formulas dataset into Spreadsheet.
- sortCells(): Sorts the loaded reference ranges across the sample sheets.