Design Guide
Design Guide
Formulas for calculating numerical values and amounts, such as obtaining sums, averages, maximum and minimum values, etc.
Below is the list of the supported formulas. Please note that the following formulas are case-sensitive.
| Formula | Description |
|---|---|
| SUM(value1,[value2],...) | Returns the sum of (adds) all the field values. You can also directly use the format value+value2+...to represent it. |
| AVG(value1, value2,...) | Returns the average (arithmetic mean) of all the listed field values. Note that this function also works for Subtables, where the average of all referenced field values will be added to the calculation. |
| AVERAGE(value1, value2,...) | Returns the average (arithmetic mean) of all the listed field values. Note that this function also works for Subtables, where the average of all referenced field values will be added to the calculation. |
| MIN(value) | Returns the smallest number in a set of field values. This function also works for Subtables. |
| MAX(value) | Returns the largest numeric value in a range of field values. This function also works for Subtables. |
| MODE.SNGL(value1,[value2],...) | Returns the most common value in a range of field values. This function works for independent fields, Subtables, and global constants. |
| MODE.MULT(value1,[value2],...) | Returns multiple most common values in a range of field values. This function works for independent fields, Subtables, and global constants. |
| ABS(value) | Returns the absolute value of a number. The absolute value of a number is the number without its sign. |
| CEILING(value,[significance]) | Rounds number up, away from zero, to the nearest multiple of significance. Significance is optional; if not specified, round up to the nearest integer. Example: CEILING(2.5) will return 3; CEILING(1.5, 0.1) will return 1.5. |
| FLOOR(value,[significance]) | Rounds number down, toward zero, to the nearest multiple of significance. Significance is optional; if not specified, round down to the nearest integer. Example: FLOOR(2.5) will return 2; FLOOR(1.58, 0.1) will return 1.5. |
| ROUND(value) | Rounds a number to the nearest integer. |
| ROUND(value,N) | Rounds a number to N decimal place. |
| ROUNDUP(value,N) | Rounds up a number (away from zero) to N decimal place. |
| ROUNDDOWN(value,N) | Rounds down a number (toward zero) to N decimal place. |
| MROUND(number,N) | Rounds a number to the nearest multiple of N |
| SQRT(value) | Returns the square root of a number. |
| COUNT(value1,value2,...) | Returns the total number of field values. Empty values will not be counted when referencing independent fields but will be counted when referencing Subtable fields. |
| PI() | Returns the number 3.14159265358979, the mathematical constant pi, and the ratio of the circumference of a circle to its diameter, accurate to 15 digits. |
| RAND() | Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated. For details, click here. |
| POWER(value,power) | Returns the result of a number value raised to a power. |
| MOD(value,divisor) | Returns the remainder after a number value is divided by a divisor. The result has the same sign as the divisor. For details, click here. |
| GCD(value1,[value2],...) | Returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides the specified number of values without a remainder. For details, click here. |
| LCM(value1,[value2],...) | Returns the least common multiple of integers. The least common multiple is the smallest positive integer, which is a multiple of all integer arguments value1, value2, and so on. Use LCM to add fractions with different denominators. For details, click here. |
| PRODUCT() | Multiplies all the numerical values in referenced fields (neglecting empty and text values). You can also reference a Subtable field to multiply all the numeric values of that field. For details, click here. |
| PMT(rate, nper, pv, [fv], [type]) | Calculates the payment for a loan.
rate (Required): The interest rate. |
Returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random value is generated each time the sheet is recalculated. This function is typically used for random sorting, lotteries, or simulation testing.
| Formula | Syntax |
|---|---|
| RAND | RAND() |
Example: Using RAND() for a Random Draw
1. In the "Draw" sheet, add a "Participants" Subtable. Besides participant details, create a "Draw Variable" field with the formula RAND(). The system will automatically generate a random value between 0 and 1 for each Subtable record.
2. Create a "Winners List" field and apply a formula that sorts by the "Draw Variable". To select one winner, enter the formula LARGE("Draw Variable", 1, "Participant Name") to return the participant name corresponding to the top-ranked record. To select multiple winners, create additional "Winners List" fields to retrieve the top few records accordingly.
Returns the remainder of a value divided by a divisor. Commonly used for grouping, alternating numbering, or checking multiples, with applications in product packaging, batch processing, and shift scheduling.
| Formula | Syntax |
|---|---|
| MOD | MOD(value,divisor) |
Arguments:
value (required): The number to divide. You can enter a field reference or a fixed number.
divisor (required): The divisor, which must be greater than 0. You can enter a field reference or a fixed number.
Example: You can use the MOD function in production packaging to determine how many full boxes can be packed and how many items remain, helping with production planning and packaging operations.
In the "Production Record" sheet, use FLOOR("Production Quantity" / "Units per Box") in the "Boxes Produced" field to calculate the number of full boxes.
Then, use MOD("Total Production Quantity", "Units per Box") in the "Unboxed Units" field to find the remaining units that do not fill a complete box.
Returns the Greatest Common Divisor of two or more integers, which is the largest integer that divides all specified numbers without a remainder. It is commonly used for ratio simplification, batch packaging, and checking whether quantities can be evenly divided.
| Formula | Syntax |
|---|---|
| GCD | GCD(value1,[value2],...) |
Arguments:
value1 (required): The first positive integer or field for the calculation.
value2, … (required): At least one more number or field is needed to compute the Greatest Common Divisor; additional integers may be added as needed.
Example: Gift Set Packaging Calculation
In the "Gift Set Packaging" sheet, apply the formula GCD(A2, A3, A4) in the "Total Number of Gift Sets" field to calculate how many complete gift sets can be assembled from three products: cookies, wafers, and candies. A2, A3, and A4 represent the quantity fields for each product.
For example, if the factory produces "504 packs of cookies", "756 packs of wafers", and "630 packs of candies", the system will return a greatest common divisor of 126, meaning the products can be evenly divided into 126 gift sets. Each set would then contain "4 packs of cookies", "6 packs of wafers", and "5 packs of candies".
If the result is 1, it indicates the quantities cannot be evenly divided, helping confirm packaging feasibility and enabling staff to plan the packing process more efficiently.
Returns the Least Common Multiple of two or more integers, which is the smallest positive integer evenly divisible by all specified numbers. Commonly used for scheduling synchronization, batch shipment planning, quantity grouping, and recurring event organization.
| Formula | Syntax |
|---|---|
| LCM | LCM(value1,[value2],...) |
Arguments:
value1 (required): The first positive integer or field for the calculation.
value2, … (required): At least one more number or field is needed to compute the Least Common Multiple; additional integers may be added as needed.
Example: Recurring Event Scheduling
Suppose a company runs three recurring promotions: a weekly sale every 7 days, a giveaway every 10 days, and a major discount event every 30 days. In the "Next Joint Promotion Day" field, enter the formula LCM(A2, A3, A4), where A2, A3, and A4 represent the event cycle fields. The system will return a least common multiple of 210.
This means all three promotions will align every 210 days, allowing the company to host a combined event and coordinate marketing, logistics, and inventory in advance for smoother operations.
The PRODUCT function multiplies all specified numeric values and automatically ignores empty or text values. It is commonly used for cumulative calculations, ratio formulas, and compounding discounts or interest rates. In addition to referencing independent fields, when referencing Subtable fields, the function automatically multiplies all numeric values in that field, removing the need for manual calculations.
| Formula | Syntax |
|---|---|
| PRODUCT | PRODUCT() |
Arguments:
Independent Fields: Enter numbers directly, for example, PRODUCT(25,38,13), or reference multiple fields, for example, PRODUCT(A2,A3,A4).
Subtable Fields: Reference a Subtable field, for example, PRODUCT(C7), and the system will multiply all numeric values in that field. You can also reference multiple Subtable fields to multiply all values together.
Example: Calculating a Cumulative Order Discount
If an order qualifies for multiple discounts listed in the "Discount List" subtable, create a field named "Final Discount" and apply the formula PRODUCT("Discount Rate"). The system will automatically multiply all discount rates to calculate the total cumulative discount for the order.
The PMT function calculates the periodic payment amount for loans, installments, or subscription plans. It determines the amount to be paid or invested each period based on the interest rate, number of periods, present value (loan amount or target amount), and optionally, the future value and payment timing.
| Formula | Syntax |
|---|---|
| PMT | PMT(rate, nper, pv, [fv], [type]) |
Arguments:
rate (required): Interest rate per period, for example, a monthly or installment rate. Enter a number or reference a field.
nper (required): Total number of periods, for example, loan terms. Enter a number or reference a field.
pv (required): Present value or principal. Enter a number or reference a field.
fv (optional): Future value. Defaults to 0, meaning the loan is fully paid off at the end. Enter a value to leave a remaining balance.
type (optional): Payment timing. Enter 0 for end-of-period (default) or 1 for beginning-of-period payments.
Example: Monthly Mortgage Payment Calculation
Create a mortgage calculator where users enter the "Loan Amount", "Annual Interest Rate", and "Loan Term".
In the "Monthly Payment" field, apply the formula:
PMT("Annual Interest Rate"/12, "Loan Term"*12, "Loan Amount")
The system will automatically calculate the monthly mortgage payment, helping users quickly estimate and plan their home financing.
Thank you for your valuable feedback!
