# Built-in functions, with examples > Every function a formula can call without defining it, from if() and round() to num(), qty() and lookup(), with worked examples in real units. Source: https://www.speclatch.com/guide/units-formulas/functions Plan: Every plan. Last verified: 2026-11-11 Real formulas need more than `+ - * / ^`: a choice between two values, a test pressure rounded up to the next 25 psi, the lower of two ratings. The built-in functions cover those, and every formula can call them without defining anything. The table lists them all; the sections after it work through each family with an example in real units. Click **Try it** on an example to change its inputs and watch the result follow. [How formulas work](/guide/units-formulas/formula-language) covers the rules they follow. On this page: [Where you find them](#where-you-find-them) · [Every built-in function](#every-built-in-function) · [Tests and choices](#tests-and-choices) · [Rounding](#rounding) · [Square roots and powers](#square-roots-and-powers) · [Smallest, largest, first filled](#smallest-largest-first-filled) · [Straight lines](#straight-lines) · [Angles and pi](#angles-and-pi) · [Units: num and qty](#units-num-and-qty) · [Reference tables: lookup](#reference-tables-lookup) ## Where you find them - In the formula editor, **Insert** lists them under *Math*. Picking one types its name and parentheses, with the cursor inside. - You can also type the first letters of a name, such as `coa`, and press Enter or Tab to take `coalesce()`. - Inside the parentheses, the line under the box shows the call and highlights the value you're on, so you always know which input comes next. - On **Functions**, the **Built in** row at the end of the list opens the same table as below. Anyone can open **Functions**; Admin and Engineering members write formulas. ![A formula box holding a round() call, with a line under it showing round(x, digits) and the digits value highlighted.](https://www.speclatch.com/guide/units-formulas/functions-1.png) *Inside a call, the line under the box names the value you're typing.* ## Every built-in function - `if(test, then, else)`: The value of "then" when the test is true, else the last value, e.g. if(pressure > qty(1000, "psi"), 1.5, 2). Add pairs for bands: if(r < 0.4, "Pass", r < 0.8, "Check", "Fail") — the first true test wins. Tests compare with < <= > >= = <>; a choice compares to a label in quotes (material = "316 SS"). - `and(test, test, …)`: True when every test is true. - `or(test, test, …)`: True when at least one test is true. - `not(test)`: True when the test is false, e.g. not(ul_listed) for a Yes/No property. - `lookup(table, x)`: Reads a reference table (Data model → Reference tables): lookup(allowable_stress, temperature) interpolates between rows; lookup(flange_rating, flange_class, temperature) matches the choice first. Name the column in quotes when the table has several: lookup(steel, t, "modulus"). Outside the rows the value is blank. - `num(x, "in")`: Reads a unit-aware value as a plain number in the unit you name. Use it for formulas whose units can't be stored, like areas or empirical constants. - `qty(x, "ft")`: Gives a plain number a unit, so the result can be stored as a length, a flow, a pressure… - `min(a, b, …)`: The smallest value. All values must be the same kind. - `max(a, b, …)`: The largest value. All values must be the same kind. - `coalesce(a, b, …)`: The first value that isn't blank, e.g. min(p_well, coalesce(p_flange, p_well)) when only some records have a flange. All values must be the same kind. - `abs(x)`: Absolute value. Keeps the unit. - `interpolate(x, x1, y1, x2, y2)`: Straight line through (x1, y1) and (x2, y2), read at x. The x values share a kind, the y values share a kind. - `round(x, digits)`: Rounds a plain number to a number of decimals (0 if omitted; −1 rounds to tens). - `ceil(x, step)`: Rounds a plain number UP to a multiple of step (1 if omitted), e.g. the next standard size. - `floor(x, step)`: Rounds a plain number DOWN to a multiple of step (1 if omitted). - `sqrt(x)`: Square root of a plain number. - `sin(angle)`: Sine. The angle is in degrees: a plain number or an angle field. - `cos(angle)`: Cosine, angle in degrees. - `tan(angle)`: Tangent, angle in degrees. - `asin(x)`: Arcsine, in degrees. - `acos(x)`: Arccosine, in degrees. - `atan(x)`: Arctangent, in degrees (e.g. an incline from rise ÷ run). - `pi()`: π = 3.14159… > **No text functions.** Formulas read numbers, choices and yes/no values. No function joins or cuts text, and text, date and files fields can't be read. A formula can still return labels in quotes, which makes its result a choice. ## Tests and choices `if()`, `and()`, `or()` and `not()` turn conditions into values, so a rule from a standard or a datasheet becomes part of the formula. **Example: Proof pressure of a 0–300 psi transmitter** - Inputs: `range_max` = 300 psi - Formula: `range_max * if(range_max <= qty(1000, "psi"), 2, 1.5)` - Result: 600 psi (41.4 bar) - Also: `range_max` = 3,000 psi → 4,500 psi Up to 1,000 psi the proof pressure is twice the upper range limit; above that, 1.5 times. A **Yes / no** field works as a test by itself: `and(pressure_rating >= qty(200, "psi"), dead_end_full_rating)` needs both the rating and the tick. `and()` is false as soon as one test is false, even when another input is blank, and `or()` is true as soon as one test is true. ## Rounding `round()`, `ceil()` and `floor()` take plain numbers. To round a value with a unit, read it with `num()` and give the unit back with `qty()`, because rounding to 25 means something different in psi and in bar. **Example: Shell test of a 2 in ball valve rated 275 psi** - Inputs: `pressure_rating` = 275 psi - Formula: `qty(ceil(num(pressure_rating, "psi") * 1.5, 25), "psi")` - Result: 425 psi (29.3 bar) - Also: `pressure_rating` = 150 psi → 225 psi The shell test is 1.5 times the rating, rounded up to the next 25 psi: 1.5 × 275 is 412.5, so 425. A ratio of two values of the same kind is already a plain number, so `round()` takes it directly: `round(max_bhp / std_motor_hp * 100, 0)` gives a motor loading of 104 % for a pump drawing 5.22 HP on a 5 HP motor. A negative number of digits rounds to tens or hundreds: `round(1234, -1)` is 1230. Halves round away from zero, so 2.5 becomes 3, and `floor(2.6, 0.25)` is 2.5. > **Tip:** To show fewer decimals without changing the stored value, set the field's *Decimals* instead of using `round()`. ## Square roots and powers `sqrt()` and `^` work on plain numbers, so read values with a unit through `num()` first. **Example: Water through a 2 in ball valve at a 4 psi drop** - Inputs: `cv_rated` = 380, `pressure_drop` = 4 psi, `sg` = 1 - Formula: `qty(cv_rated * sqrt(num(pressure_drop, "psi") / sg), "GPM")` - Result: 760 GPM (173 m³/h) - Also: `pressure_drop` = 9 psi → 1,140 GPM `sg` is the specific gravity. Flow grows with the square root of the drop, so 9 psi gives 1.5 times the flow at 4 psi. The same valve's resistance coefficient uses a fourth power: `round(891 * num(pipe_id, "in") ^ 4 / cv_rated ^ 2, 3)` gives K = 0.113 for a 2.067 in reference pipe. `sqrt()` of a negative number leaves the result blank, with the reason. ## Smallest, largest, first filled `min()` and `max()` compare values of one kind, in any mix of units. `coalesce()` gives the first value that isn't blank, so a measured value can replace a catalog value as soon as someone enters it. **Example: The working pressure of a valve, limited by its flanges** - Inputs: `body_rating` = 300 psi, `flange_rating` = 19.6 bar - Formula: `min(body_rating, flange_rating)` - Result: 284 psi (19.6 bar) The bar value converts before the comparison, so the lower rating wins whatever unit each field stores. `coalesce(measured_weight, catalog_weight)` gives the catalog weight, such as 42 lb, until someone enters a measured one. It also works on choices, such as `coalesce(seal_override, seal_standard)`. ## Straight lines `interpolate(x, x1, y1, x2, y2)` reads the straight line through two points at x. The x values share a kind, and so do the y values. **Example: The output of a 0–300 psi, 4–20 mA transmitter at 75 psi** - Inputs: `test_pressure` = 75 psi, `range_min` = 0 psi, `range_max` = 300 psi - Formula: `interpolate(test_pressure, range_min, qty(4, "mA"), range_max, qty(20, "mA"))` - Result: 8 mA - Also: `test_pressure` = 400 psi → 25.3 mA The line continues past the two points, which is why 400 psi gives more than 20 mA. A reference table read with `lookup()` stays blank outside its rows instead. ## Angles and pi `sin()`, `cos()` and `tan()` take an angle in degrees: an angle field or a plain number. `asin()`, `acos()` and `atan()` give degrees back, and `pi()` takes no values. **Example: The lift of an inclined conveyor 50 ft long at 15°** - Inputs: `conveyor_length` = 50 ft, `incline` = 15 ° - Formula: `conveyor_length * sin(incline)` - Result: 12.9 ft (3.94 m) - Also: `incline` = 30 ° → 25 ft `sin()` reads the angle in degrees, so an angle field needs no conversion. `pi() * pulley_dia` gives a 12 in pulley's circumference, 37.7 in (958 mm). The other way round, `qty(atan(rise / run), "°")` turns a 10 ft rise over 40 ft into an angle of about 14°. ## Units: num and qty `num(x, "unit")` reads a value with a unit as a plain number in the unit you name, and `qty(x, "unit")` gives a plain number a unit. Use them for engineering formulas whose constants assume particular units, so the formula stays right whatever unit each field stores. **Example: The flow through a 2 in pipe at 10 ft/s** - Inputs: `pipe_id` = 2.067 in - Formula: `qty(2.448 * num(pipe_id, "in") ^ 2 * 10, "GPM")` - Result: 105 GPM (23.8 m³/h) - Also: `pipe_id` = 52.5 mm → 105 GPM The constant 2.448 assumes inches and gives gpm, so `num()` reads the diameter in inches and `qty()` makes the result a flow. A diameter stored in mm gives the same answer. The same pattern gives a conveyor's power to lift material: `qty(num(capacity, "TPH") * num(lift, "ft") / 990, "HP")` is 2.53 HP for 250 TPH up 10 ft. `num()` on a plain number, or `qty()` on a value that already has a unit, doesn't save, and the message says which one to use. ## Reference tables: lookup `lookup(table, x)` reads a table from **Reference tables**, such as motor efficiencies from a standard. Pass the inputs in the table's order. A choice input matches a row's label exactly. A number input converts to the table's unit, then reads between rows on a straight line. **Example: The efficiency of a 4-pole motor rated 7.5 kW** - Inputs: `poles` = 4-pole, `rated_power` = 7.5 kW - Formula: `lookup(motor_efficiency, poles, rated_power)` - Result: 91.7 - Also: `rated_power` = 12.5 HP → 92.1 The table lists 91.7 at 10 HP and 92.4 at 15 HP for 4-pole motors. 7.5 kW is 10.06 HP, just past the 10 HP row. Outside the rows, the result stays blank with the reason. In a table of ball valve openings that ends at 90°, a 95° opening gives a blank, and the reason reads *95 ° is above Ball valve inherent characteristic (up to 90 °)*. A label with no row is blank too. When a table has several output columns, add the column's key or label in quotes last, such as `lookup(motor_efficiency, poles, rated_power, "efficiency")`. `lookup()` works in field formulas and calculated curve lines, but not inside your own functions. > **Tip:** Need the same math in many fields? [Define a reusable function](/guide/units-formulas/define-a-function) once. Its key can't be a built-in name such as `round`.