{"__v":20,"_id":"5633020e62c48a0d00334df3","category":{"__v":21,"_id":"5614a5076ebd340d006a1bd0","pages":["5614a5086ebd340d006a1bd2","5614a68fb283fb0d00a487d5","5614aab0163a7d2100ab5f02","563301637b563e0d00904900","56330184b904a10d0032f797","563302048c9cda0d002526ff","5633020e62c48a0d00334df3","5633022c10b6040d0087946b","5633024010b6040d0087946d","5633026fb904a10d0032f799","5633034538f8aa0d00d31002","5633035062c48a0d00334df5","5633036949e16d0d00122537","5633037b38f8aa0d00d31005","5633038d38f8aa0d00d31007","5633039bb904a10d0032f79b","56330b63b904a10d0032f7a6","56330e587b563e0d00904908","56370adb0704070d00f06c0c","563bc913dfa1282b000dd33e","56ad51b279011b0d00c32aba"],"project":"5614a5066ebd340d006a1bcc","version":"5614a5076ebd340d006a1bcf","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2015-10-07T04:52:23.567Z","from_sync":false,"order":1,"slug":"reference","title":"Reference"},"parentDoc":null,"project":"5614a5066ebd340d006a1bcc","user":"5614a4b8b283fb0d00a487d3","version":{"__v":5,"_id":"5614a5076ebd340d006a1bcf","project":"5614a5066ebd340d006a1bcc","createdAt":"2015-10-07T04:52:23.031Z","releaseDate":"2015-10-07T04:52:23.031Z","categories":["5614a5076ebd340d006a1bd0","5614aa6253a1830d0098e962","5633048649e16d0d00122539","563305b08c9cda0d00252704","563305dc8c9cda0d00252705"],"is_deprecated":false,"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"","version_clean":"1.0.0","version":"1.0"},"updates":[],"next":{"pages":[],"description":""},"createdAt":"2015-10-30T05:37:18.450Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":6,"body":"Fieldbook lets you set a column to a formula, like spreadsheets—but with some key differences.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Formulas use column names\"\n}\n[/block]\nInstead of cell references like A1 or M1187, Fieldbook formulas use column names.\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/R046LJJPQ5yGgX59QUu5_Screen%20Shot%202015-11-01%20at%2011.38.28%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.38.28 PM.png\",\n        \"744\",\n        \"258\",\n        \"#299831\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"A formula applies to the whole column\"\n}\n[/block]\nWhen you set a formula on a column, the same formula applies to the whole column. You don't have to fill the formula down, and there's no way to make an error by not having the same formula throughout a column.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Setting a formula\"\n}\n[/block]\nYou can set a formula on a column using the column menu—or, just start typing an equals sign `=` like you would in a spreadsheet, and the formula editor will pop right up.\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/2qnIbo1KStOfqwEgPNwU_Screen%20Shot%202015-11-01%20at%2011.40.48%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.40.48 PM.png\",\n        \"401\",\n        \"358\",\n        \"#339b39\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Summary functions at the bottom of a column\"\n}\n[/block]\nThe bottom cell in a column can contain a summary function. By default, this is the sum if a column has numeric values, and blank otherwise. You can set it to one of several functions, or turn it off—just right-click on the bottom cell (or find the “Display formula at bottom” option in the menu).\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/92z7WJeeTSaFLxopilZ6_Screen%20Shot%202015-11-01%20at%2011.42.14%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.42.14 PM.png\",\n        \"248\",\n        \"478\",\n        \"#3ca641\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Comparisons\"\n}\n[/block]\nYou can compare values with expressions like:\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Comparison operator\",\n    \"h-1\": \"Meaning on numbers\",\n    \"h-2\": \"Meaning on dates\",\n    \"h-3\": \"Meaning on text\",\n    \"0-0\": \"A = B\",\n    \"0-1\": \"A equals B\",\n    \"0-2\": \"A is the same date as B\",\n    \"0-3\": \"A is the same text as B\",\n    \"1-0\": \"A < B\",\n    \"1-1\": \"A is less than B\",\n    \"1-2\": \"A comes before B\",\n    \"1-3\": \"A comes before B alphabetically\",\n    \"2-0\": \"A > B\",\n    \"2-1\": \"A is greater than B\",\n    \"2-2\": \"A comes after B\",\n    \"2-3\": \"A comes after B alphabetically\",\n    \"3-0\": \"A <= B\",\n    \"3-1\": \"A is less than or equal to B\",\n    \"3-2\": \"A comes before, or is the same as, B\",\n    \"3-3\": \"A comes before B alphabetically, or is the same as B\",\n    \"4-0\": \"A >= B\",\n    \"4-1\": \"A is greater than or equal to B\",\n    \"4-2\": \"A comes after, or is the same as, B\",\n    \"4-3\": \"A comes after B alphabetically, or is the same as B\",\n    \"5-0\": \"A <> B or A != B\",\n    \"5-1\": \"A is not equal to B\",\n    \"5-2\": \"A is not the same date as B\",\n    \"5-3\": \"A is not the same text as B\"\n  },\n  \"cols\": 4,\n  \"rows\": 6\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Functions in formulas\"\n}\n[/block]\nYou can use functions in formulas and apply them to columns:\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/i6Ejn0RkTjuR6WNfycWg_Screen%20Shot%202015-11-03%20at%207.44.28%20PM.png\",\n        \"Screen Shot 2015-11-03 at 7.44.28 PM.png\",\n        \"644\",\n        \"309\",\n        \"#2b9336\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\nSee below for a full [function reference](#function-reference).\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Dates in formulas\"\n}\n[/block]\nDates can be used in formulas:\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Date expression\",\n    \"h-1\": \"Meaning\",\n    \"h-2\": \"Example\",\n    \"0-0\": \"date + N\",\n    \"0-1\": \"N days after the date\",\n    \"0-2\": \"6/3/15 + 5 = 6/8/15\",\n    \"1-0\": \"date - N\",\n    \"1-1\": \"N days before the date\",\n    \"1-2\": \"9/30/15 - 7 = 9/23/15\",\n    \"2-0\": \"date1 - date2\",\n    \"2-1\": \"Number of days between the two dates\",\n    \"2-2\": \"12/15/15 - 12/10/15 = 5\",\n    \"3-0\": \"date1 < date2\",\n    \"3-1\": \"Whether date1 comes before date2\",\n    \"3-2\": \"1/20/15 < 1/23/15 = true\",\n    \"4-0\": \"date1 > date2\",\n    \"4-1\": \"Whether date2 comes after date2\",\n    \"4-2\": \"1/20/15 > 1/23/15 = false\"\n  },\n  \"cols\": 3,\n  \"rows\": 5\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"If-then-else formulas\"\n}\n[/block]\nYou can use if statements in formulas using the pattern: `if condition then value1 else value2`\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/L1rOeNR3SG2UJP3URpgL_Screen%20Shot%202015-11-03%20at%207.40.14%20PM.png\",\n        \"Screen Shot 2015-11-03 at 7.40.14 PM.png\",\n        \"721\",\n        \"476\",\n        \"#2b8f3a\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Lookup formulas\"\n}\n[/block]\nIf a sheet has linked columns (see [Linking sheets](doc:linking-sheets)), you can reference data from the linked rows. Just type the name of the linked column, then a dot `.`, then the name of the column in the linked sheet.\n\nFor instance, this Projects sheet has a billing formula that multiplies `hours` worked times the rate for the employee:\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/xNbUvUINTTKaezKgYlXO_Screen%20Shot%202015-11-01%20at%2011.50.19%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.50.19 PM.png\",\n        \"850\",\n        \"398\",\n        \"#299731\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\nThe rate is looked up from the Employees sheet:\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/dszDvvlTkyRPq4dUOYNv_Screen%20Shot%202015-11-01%20at%2011.43.45%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.43.45 PM.png\",\n        \"557\",\n        \"274\",\n        \"#3faa46\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Rollup formulas\"\n}\n[/block]\nIf a sheet has a linked column with multiple links per cell, you can sum, count, average, etc. the linked values. To continue the previous example, this formula in the Employees sheet sums up the hours from all projects the employee is assigned to:\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/jTC7YqyqQ8yIf52a5JdP_Screen%20Shot%202015-11-01%20at%2011.49.40%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.49.40 PM.png\",\n        \"702\",\n        \"410\",\n        \"#2a9832\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\nYou can see that this is the same as the sum at the bottom of the Hours column on the Projects subsheet of the employee's detail page:\n[block:image]\n{\n  \"images\": [\n    {\n      \"image\": [\n        \"https://files.readme.io/PJAoKQJiT3ShAdggljJh_Screen%20Shot%202015-11-01%20at%2011.52.25%20PM.png\",\n        \"Screen Shot 2015-11-01 at 11.52.25 PM.png\",\n        \"677\",\n        \"513\",\n        \"#36a73d\",\n        \"\"\n      ]\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Function reference\"\n}\n[/block]\n## Text\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Function\",\n    \"h-1\": \"Definition\",\n    \"h-2\": \"Examples\",\n    \"0-0\": \"length(string)\",\n    \"0-1\": \"String length\",\n    \"0-2\": \"length(\\\"Fieldbook\\\") = 9\",\n    \"1-0\": \"isblank(value)\",\n    \"1-1\": \"True if the field or value is blank, false otherwise\",\n    \"1-2\": \"isblank(\\\"something\\\") = false\\nisblank(\\\"\\\") = true\",\n    \"2-0\": \"concat(separator, value1, value2...)\",\n    \"2-1\": \"Concatenates/joins multiple fields with a separator\",\n    \"2-2\": \"concat(\\\"-\\\", \\\"Alpha\\\", \\\"1\\\") = \\\"Alpha-1\\\"\"\n  },\n  \"cols\": 3,\n  \"rows\": 3\n}\n[/block]\n## Date\n[block:parameters]\n{\n  \"data\": {\n    \"0-0\": \"year(date)\",\n    \"h-0\": \"Function\",\n    \"h-1\": \"Definition\",\n    \"h-2\": \"Examples\",\n    \"0-1\": \"Year of the input\",\n    \"0-2\": \"year(\\\"12/31/2015\\\") = 2015\",\n    \"1-0\": \"month(date)\",\n    \"2-0\": \"week(date)\",\n    \"3-0\": \"day(date)\",\n    \"4-0\": \"today()\",\n    \"4-1\": \"Today's date\",\n    \"1-1\": \"Month number\",\n    \"1-2\": \"month(\\\"12/31/2015\\\") = 12\",\n    \"2-1\": \"Week number\",\n    \"2-2\": \"week(\\\"12/31/2015\\\") = 1\",\n    \"3-1\": \"Day number\",\n    \"3-2\": \"day(\\\"12/31/2015\\\") = 31\",\n    \"5-0\": \"date(year, month, day)\",\n    \"5-1\": \"A date composed of the given year, month and day\",\n    \"5-2\": \"date(2015, 12, 31) = 12/31/2015\"\n  },\n  \"cols\": 3,\n  \"rows\": 6\n}\n[/block]\n## Rollups\n\nRollup functions can be used on linked records. For instance, suppose we have a sheet of Customers linked to a sheet of Orders. The following formulas could be used in the Customers sheet:\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Function\",\n    \"h-1\": \"Definition\",\n    \"h-2\": \"Example usage\",\n    \"0-0\": \"count()\",\n    \"0-1\": \"Counts linked records\",\n    \"0-2\": \"count(orders) = the number of orders a customer has\",\n    \"1-0\": \"sum()\",\n    \"1-1\": \"Sums one field of linked records\",\n    \"1-2\": \"sum(orders.price) = the total price of all a customer's orders\",\n    \"2-0\": \"min()\",\n    \"2-1\": \"Minimum of a field on linked records. (Can be applied to dates to find the earliest)\",\n    \"2-2\": \"min(orders.date) = the date of a customer's first order\",\n    \"3-0\": \"max()\",\n    \"3-1\": \"Maximum of a field on linked records. (Can be applied to dates to find the latest)\",\n    \"3-2\": \"max(orders.date) = the date of a customer's latest order\",\n    \"4-0\": \"avg()\",\n    \"4-1\": \"Average of a field on linked records\",\n    \"4-2\": \"avg(orders.price) = the average order price for a customer\",\n    \"5-0\": \"blank()\",\n    \"5-1\": \"Count of blank values in a column\",\n    \"5-2\": \"blank(orders.status) = the number of orders that have a blank status\",\n    \"6-0\": \"non_blank()\",\n    \"6-1\": \"Count of non-blank values in a  column\",\n    \"6-2\": \"non_blank(orders.status) = the number of orders that have a non-blank status\"\n  },\n  \"cols\": 3,\n  \"rows\": 7\n}\n[/block]\n## Math\n\nMath functions all have the same definitions as [JavaScript's math functions](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math).\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Function\",\n    \"h-1\": \"Definition\",\n    \"0-0\": \"abs(x)\",\n    \"0-1\": \"Absolute value\",\n    \"1-0\": \"acos(x)\",\n    \"1-1\": \"Arccosine\",\n    \"2-0\": \"asin(x)\",\n    \"2-1\": \"Arcsine\",\n    \"3-0\": \"atan(x)\",\n    \"3-1\": \"Arctangent\",\n    \"4-0\": \"atan2(y, x)\",\n    \"4-1\": \"Arctangent of the quotient\",\n    \"5-0\": \"ceil(x)\",\n    \"5-1\": \"Ceiling\",\n    \"6-0\": \"cos(x)\",\n    \"6-1\": \"Cosine\",\n    \"7-0\": \"exp(x)\",\n    \"7-1\": \"Exponent (e^x)\",\n    \"8-0\": \"floor(x)\",\n    \"8-1\": \"Floor\",\n    \"9-0\": \"log(x)\",\n    \"9-1\": \"Natural logarithm\",\n    \"10-0\": \"pow(x, y)\",\n    \"10-1\": \"Exponential (x^y)\",\n    \"11-0\": \"random()\",\n    \"11-1\": \"Psuedo-random number from 0 to 1\",\n    \"12-0\": \"round(x)\",\n    \"12-1\": \"Round to nearest integer\",\n    \"13-0\": \"sin(x)\",\n    \"13-1\": \"Sine\",\n    \"14-0\": \"sqrt(x)\",\n    \"14-1\": \"Square root\",\n    \"15-0\": \"tan(x)\",\n    \"15-1\": \"Tangent\"\n  },\n  \"cols\": 2,\n  \"rows\": 16\n}\n[/block]\n## Formatting\n\nThese special functions can be used to force formatting on the output of a formula.\n[block:parameters]\n{\n  \"data\": {\n    \"0-0\": \"percent(value)\",\n    \"1-0\": \"currency(value)\",\n    \"h-0\": \"Function\",\n    \"h-1\": \"Example\",\n    \"0-1\": \"percent(1/5) = 20%\",\n    \"1-1\": \"currency(5) = $5\"\n  },\n  \"cols\": 2,\n  \"rows\": 2\n}\n[/block]\n## Error\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Function\",\n    \"h-1\": \"Definition\",\n    \"h-2\": \"Examples\",\n    \"0-0\": \"iserror(value)\",\n    \"0-1\": \"True if the value is an error; false otherwise\",\n    \"0-2\": \"iserror(3) = false\\niserror(1/0) = true (divide by zero)\"\n  },\n  \"cols\": 3,\n  \"rows\": 1\n}\n[/block]","excerpt":"","slug":"formulas","type":"basic","title":"Formulas"}
Fieldbook lets you set a column to a formula, like spreadsheets—but with some key differences. [block:api-header] { "type": "basic", "title": "Formulas use column names" } [/block] Instead of cell references like A1 or M1187, Fieldbook formulas use column names. [block:image] { "images": [ { "image": [ "https://files.readme.io/R046LJJPQ5yGgX59QUu5_Screen%20Shot%202015-11-01%20at%2011.38.28%20PM.png", "Screen Shot 2015-11-01 at 11.38.28 PM.png", "744", "258", "#299831", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "A formula applies to the whole column" } [/block] When you set a formula on a column, the same formula applies to the whole column. You don't have to fill the formula down, and there's no way to make an error by not having the same formula throughout a column. [block:api-header] { "type": "basic", "title": "Setting a formula" } [/block] You can set a formula on a column using the column menu—or, just start typing an equals sign `=` like you would in a spreadsheet, and the formula editor will pop right up. [block:image] { "images": [ { "image": [ "https://files.readme.io/2qnIbo1KStOfqwEgPNwU_Screen%20Shot%202015-11-01%20at%2011.40.48%20PM.png", "Screen Shot 2015-11-01 at 11.40.48 PM.png", "401", "358", "#339b39", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "Summary functions at the bottom of a column" } [/block] The bottom cell in a column can contain a summary function. By default, this is the sum if a column has numeric values, and blank otherwise. You can set it to one of several functions, or turn it off—just right-click on the bottom cell (or find the “Display formula at bottom” option in the menu). [block:image] { "images": [ { "image": [ "https://files.readme.io/92z7WJeeTSaFLxopilZ6_Screen%20Shot%202015-11-01%20at%2011.42.14%20PM.png", "Screen Shot 2015-11-01 at 11.42.14 PM.png", "248", "478", "#3ca641", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "Comparisons" } [/block] You can compare values with expressions like: [block:parameters] { "data": { "h-0": "Comparison operator", "h-1": "Meaning on numbers", "h-2": "Meaning on dates", "h-3": "Meaning on text", "0-0": "A = B", "0-1": "A equals B", "0-2": "A is the same date as B", "0-3": "A is the same text as B", "1-0": "A < B", "1-1": "A is less than B", "1-2": "A comes before B", "1-3": "A comes before B alphabetically", "2-0": "A > B", "2-1": "A is greater than B", "2-2": "A comes after B", "2-3": "A comes after B alphabetically", "3-0": "A <= B", "3-1": "A is less than or equal to B", "3-2": "A comes before, or is the same as, B", "3-3": "A comes before B alphabetically, or is the same as B", "4-0": "A >= B", "4-1": "A is greater than or equal to B", "4-2": "A comes after, or is the same as, B", "4-3": "A comes after B alphabetically, or is the same as B", "5-0": "A <> B or A != B", "5-1": "A is not equal to B", "5-2": "A is not the same date as B", "5-3": "A is not the same text as B" }, "cols": 4, "rows": 6 } [/block] [block:api-header] { "type": "basic", "title": "Functions in formulas" } [/block] You can use functions in formulas and apply them to columns: [block:image] { "images": [ { "image": [ "https://files.readme.io/i6Ejn0RkTjuR6WNfycWg_Screen%20Shot%202015-11-03%20at%207.44.28%20PM.png", "Screen Shot 2015-11-03 at 7.44.28 PM.png", "644", "309", "#2b9336", "" ] } ] } [/block] See below for a full [function reference](#function-reference). [block:api-header] { "type": "basic", "title": "Dates in formulas" } [/block] Dates can be used in formulas: [block:parameters] { "data": { "h-0": "Date expression", "h-1": "Meaning", "h-2": "Example", "0-0": "date + N", "0-1": "N days after the date", "0-2": "6/3/15 + 5 = 6/8/15", "1-0": "date - N", "1-1": "N days before the date", "1-2": "9/30/15 - 7 = 9/23/15", "2-0": "date1 - date2", "2-1": "Number of days between the two dates", "2-2": "12/15/15 - 12/10/15 = 5", "3-0": "date1 < date2", "3-1": "Whether date1 comes before date2", "3-2": "1/20/15 < 1/23/15 = true", "4-0": "date1 > date2", "4-1": "Whether date2 comes after date2", "4-2": "1/20/15 > 1/23/15 = false" }, "cols": 3, "rows": 5 } [/block] [block:api-header] { "type": "basic", "title": "If-then-else formulas" } [/block] You can use if statements in formulas using the pattern: `if condition then value1 else value2` [block:image] { "images": [ { "image": [ "https://files.readme.io/L1rOeNR3SG2UJP3URpgL_Screen%20Shot%202015-11-03%20at%207.40.14%20PM.png", "Screen Shot 2015-11-03 at 7.40.14 PM.png", "721", "476", "#2b8f3a", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "Lookup formulas" } [/block] If a sheet has linked columns (see [Linking sheets](doc:linking-sheets)), you can reference data from the linked rows. Just type the name of the linked column, then a dot `.`, then the name of the column in the linked sheet. For instance, this Projects sheet has a billing formula that multiplies `hours` worked times the rate for the employee: [block:image] { "images": [ { "image": [ "https://files.readme.io/xNbUvUINTTKaezKgYlXO_Screen%20Shot%202015-11-01%20at%2011.50.19%20PM.png", "Screen Shot 2015-11-01 at 11.50.19 PM.png", "850", "398", "#299731", "" ] } ] } [/block] The rate is looked up from the Employees sheet: [block:image] { "images": [ { "image": [ "https://files.readme.io/dszDvvlTkyRPq4dUOYNv_Screen%20Shot%202015-11-01%20at%2011.43.45%20PM.png", "Screen Shot 2015-11-01 at 11.43.45 PM.png", "557", "274", "#3faa46", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "Rollup formulas" } [/block] If a sheet has a linked column with multiple links per cell, you can sum, count, average, etc. the linked values. To continue the previous example, this formula in the Employees sheet sums up the hours from all projects the employee is assigned to: [block:image] { "images": [ { "image": [ "https://files.readme.io/jTC7YqyqQ8yIf52a5JdP_Screen%20Shot%202015-11-01%20at%2011.49.40%20PM.png", "Screen Shot 2015-11-01 at 11.49.40 PM.png", "702", "410", "#2a9832", "" ] } ] } [/block] You can see that this is the same as the sum at the bottom of the Hours column on the Projects subsheet of the employee's detail page: [block:image] { "images": [ { "image": [ "https://files.readme.io/PJAoKQJiT3ShAdggljJh_Screen%20Shot%202015-11-01%20at%2011.52.25%20PM.png", "Screen Shot 2015-11-01 at 11.52.25 PM.png", "677", "513", "#36a73d", "" ] } ] } [/block] [block:api-header] { "type": "basic", "title": "Function reference" } [/block] ## Text [block:parameters] { "data": { "h-0": "Function", "h-1": "Definition", "h-2": "Examples", "0-0": "length(string)", "0-1": "String length", "0-2": "length(\"Fieldbook\") = 9", "1-0": "isblank(value)", "1-1": "True if the field or value is blank, false otherwise", "1-2": "isblank(\"something\") = false\nisblank(\"\") = true", "2-0": "concat(separator, value1, value2...)", "2-1": "Concatenates/joins multiple fields with a separator", "2-2": "concat(\"-\", \"Alpha\", \"1\") = \"Alpha-1\"" }, "cols": 3, "rows": 3 } [/block] ## Date [block:parameters] { "data": { "0-0": "year(date)", "h-0": "Function", "h-1": "Definition", "h-2": "Examples", "0-1": "Year of the input", "0-2": "year(\"12/31/2015\") = 2015", "1-0": "month(date)", "2-0": "week(date)", "3-0": "day(date)", "4-0": "today()", "4-1": "Today's date", "1-1": "Month number", "1-2": "month(\"12/31/2015\") = 12", "2-1": "Week number", "2-2": "week(\"12/31/2015\") = 1", "3-1": "Day number", "3-2": "day(\"12/31/2015\") = 31", "5-0": "date(year, month, day)", "5-1": "A date composed of the given year, month and day", "5-2": "date(2015, 12, 31) = 12/31/2015" }, "cols": 3, "rows": 6 } [/block] ## Rollups Rollup functions can be used on linked records. For instance, suppose we have a sheet of Customers linked to a sheet of Orders. The following formulas could be used in the Customers sheet: [block:parameters] { "data": { "h-0": "Function", "h-1": "Definition", "h-2": "Example usage", "0-0": "count()", "0-1": "Counts linked records", "0-2": "count(orders) = the number of orders a customer has", "1-0": "sum()", "1-1": "Sums one field of linked records", "1-2": "sum(orders.price) = the total price of all a customer's orders", "2-0": "min()", "2-1": "Minimum of a field on linked records. (Can be applied to dates to find the earliest)", "2-2": "min(orders.date) = the date of a customer's first order", "3-0": "max()", "3-1": "Maximum of a field on linked records. (Can be applied to dates to find the latest)", "3-2": "max(orders.date) = the date of a customer's latest order", "4-0": "avg()", "4-1": "Average of a field on linked records", "4-2": "avg(orders.price) = the average order price for a customer", "5-0": "blank()", "5-1": "Count of blank values in a column", "5-2": "blank(orders.status) = the number of orders that have a blank status", "6-0": "non_blank()", "6-1": "Count of non-blank values in a column", "6-2": "non_blank(orders.status) = the number of orders that have a non-blank status" }, "cols": 3, "rows": 7 } [/block] ## Math Math functions all have the same definitions as [JavaScript's math functions](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math). [block:parameters] { "data": { "h-0": "Function", "h-1": "Definition", "0-0": "abs(x)", "0-1": "Absolute value", "1-0": "acos(x)", "1-1": "Arccosine", "2-0": "asin(x)", "2-1": "Arcsine", "3-0": "atan(x)", "3-1": "Arctangent", "4-0": "atan2(y, x)", "4-1": "Arctangent of the quotient", "5-0": "ceil(x)", "5-1": "Ceiling", "6-0": "cos(x)", "6-1": "Cosine", "7-0": "exp(x)", "7-1": "Exponent (e^x)", "8-0": "floor(x)", "8-1": "Floor", "9-0": "log(x)", "9-1": "Natural logarithm", "10-0": "pow(x, y)", "10-1": "Exponential (x^y)", "11-0": "random()", "11-1": "Psuedo-random number from 0 to 1", "12-0": "round(x)", "12-1": "Round to nearest integer", "13-0": "sin(x)", "13-1": "Sine", "14-0": "sqrt(x)", "14-1": "Square root", "15-0": "tan(x)", "15-1": "Tangent" }, "cols": 2, "rows": 16 } [/block] ## Formatting These special functions can be used to force formatting on the output of a formula. [block:parameters] { "data": { "0-0": "percent(value)", "1-0": "currency(value)", "h-0": "Function", "h-1": "Example", "0-1": "percent(1/5) = 20%", "1-1": "currency(5) = $5" }, "cols": 2, "rows": 2 } [/block] ## Error [block:parameters] { "data": { "h-0": "Function", "h-1": "Definition", "h-2": "Examples", "0-0": "iserror(value)", "0-1": "True if the value is an error; false otherwise", "0-2": "iserror(3) = false\niserror(1/0) = true (divide by zero)" }, "cols": 3, "rows": 1 } [/block]