Formula Functions Reference
Formulas are an optional feature not available to all customers. If you're interested in using formulas for advanced pricing, BOM calculations, or dynamic values, please contact us to enable this functionality.
Mimeeq supports Excel-like formulas for dynamic calculations in pricing patterns, SKU codes, quantity fields, BOM parameters, and variables. This reference covers all supported functions, syntax, and real-world examples.
Where Formulas Are Used
Formulas are supported in fields processed on the backend:
| Location | Purpose | Example |
|---|---|---|
| Pricing Pattern | Dynamic price code generation | =CONCATENATE({color#code}, "-", {size#code}) |
| Pricing Code Quantity | Calculate quantity for price codes | =IF({bulk#variable}="true", 10, 1) |
| Variables (Pricing & BOM) | Intermediate calculations | =COUNT_CHAR({embossing#inputValue}) |
| BOM Item Fields | Name, code, quantity patterns | =CONCATENATE("PART-", {size#code}) |
| BOM Row Quantity | Calculate component quantities | =IF({config#code}="DOUBLE", 2, 1) |
| BOM Custom Fields (Text) | Dynamic text field values | =IF({finish#code}="MATTE", "M", "G") |
| Bundles | Various bundle configuration fields | — |
Some code input fields only support string mode (no formulas, no variables): SKU/Code structure, rules, 2D images, 2D layers in 3D, external models, download tab patterns. These fields support direct option references like {color#code} but cannot use variables or formula expressions.
For business context on where these are configured, see Guide to Setting Up Pricing and Custom Fields.
Syntax Basics
Formula vs String Mode
Patterns starting with = are evaluated as formulas. Without =, the value is treated as a literal string.
=SUM(10, 20) → 30 (formula mode)
SUM(10, 20) → "SUM(10, 20)" (string mode)
Referencing Values
Formulas can reference dynamic values using the code input syntax: {fieldName#suffix}
The suffix (e.g., #variable, #code, #bom, #inputValue) determines which field or data source is referenced. These references are resolved before the formula is evaluated. See Code Input Reference for all available suffixes.
=IF({HAS_LINING#variable}="true", "L", "N")
=CONCATENATE({color#code}, "-", {size#code})
=TEXT({coverColor#bom}, "00")
Operators
| Operator | Description | Example |
|---|---|---|
+ | Addition | =5 + 3 → 8 |
- | Subtraction | =10 - 4 → 6 |
* | Multiplication | =6 * 7 → 42 |
/ | Division | =20 / 4 → 5 |
^ | Exponentiation | =2 ^ 3 → 8 |
& | Text concatenation | ="Hello" & " World" → "Hello World" |
= | Equal | =IF(5=5, "yes", "no") → "yes" |
<> | Not equal | =IF(5<>3, "yes", "no") → "yes" |
> < >= <= | Comparison | =IF(10>5, "bigger", "smaller") |
String Comparisons
When comparing variables to string values, wrap the value in quotes:
=IF({HAS_LINING#variable}="true", "L", "N")
=IF({SIZE_FORMAT#variable}="a4", "A4", "Other")
Math Functions
ABS
Returns the absolute value of a number.
Syntax: ABS(number)
=ABS(-5) → 5
=ABS(3.14) → 3.14
BASE
Converts a number to text in a specified base (radix).
Syntax: BASE(number, radix, [min_length])
radix: Base to convert to (2-36)min_length: Minimum length, padded with zeros
=BASE(255, 16) → "FF"
=BASE(10, 2) → "1010"
=BASE(10, 2, 8) → "00001010"
CEILING
Rounds a number up to the nearest multiple of significance.
Syntax: CEILING(number, significance)
=CEILING(2.5, 1) → 3
=CEILING(4.42, 0.1) → 4.5
=CEILING(-2.5, -2) → -4
CEILING.MATH
Rounds a number up, with control over direction for negative numbers.
Syntax: CEILING.MATH(number, [significance], [mode])
mode: If non-zero, negative numbers round away from zero
=CEILING.MATH(6.3) → 7
=CEILING.MATH(-6.7, 2) → -6
=CEILING.MATH(-6.7, 2, 1) → -8
CEILING.PRECISE
Rounds a number up to the nearest integer or multiple, ignoring the sign of significance.
Syntax: CEILING.PRECISE(number, [significance])
=CEILING.PRECISE(4.3) → 5
=CEILING.PRECISE(-4.3) → -4
=CEILING.PRECISE(4.3, 2) → 6
DECIMAL
Converts a text representation in a given base to a decimal number.
Syntax: DECIMAL(text, radix)
=DECIMAL("FF", 16) → 255
=DECIMAL("1010", 2) → 10
DEGREES
Converts radians to degrees.
Syntax: DEGREES(radians)
=DEGREES(PI()) → 180
=DEGREES(1) → 57.2957...
EXP
Returns e raised to the power of a number.
Syntax: EXP(number)
=EXP(1) → 2.71828...
=EXP(2) → 7.38905...
FLOOR
Rounds a number down to the nearest multiple of significance.
Syntax: FLOOR(number, significance)
=FLOOR(3.7, 1) → 3
=FLOOR(2.5, 1) → 2
=FLOOR(-2.5, -2) → -2
FLOOR.MATH
Rounds a number down, with control over direction for negative numbers.
Syntax: FLOOR.MATH(number, [significance], [mode])
=FLOOR.MATH(6.7) → 6
=FLOOR.MATH(-6.7, 2) → -8
=FLOOR.MATH(-6.7, 2, 1) → -6
FLOOR.PRECISE
Rounds a number down to the nearest integer or multiple, ignoring sign of significance.
Syntax: FLOOR.PRECISE(number, [significance])
=FLOOR.PRECISE(4.7) → 4
=FLOOR.PRECISE(-4.7) → -5
GCD
Returns the greatest common divisor of two or more integers.
Syntax: GCD(number1, [number2], ...)
=GCD(12, 18) → 6
=GCD(24, 36, 48) → 12
INT
Rounds a number down to the nearest integer.
Syntax: INT(number)
=INT(8.9) → 8
=INT(-8.9) → -9
ISO.CEILING
Rounds a number up following ISO rules (same as CEILING.PRECISE).
Syntax: ISO.CEILING(number, [significance])
=ISO.CEILING(4.3) → 5
=ISO.CEILING(-4.3) → -4
LCM
Returns the least common multiple of integers.
Syntax: LCM(number1, [number2], ...)
=LCM(4, 6) → 12
=LCM(5, 7, 3) → 105
LN
Returns the natural logarithm of a number.
Syntax: LN(number)
=LN(2.71828) → 1
=LN(10) → 2.302585...
LOG
Returns the logarithm of a number to a specified base.
Syntax: LOG(number, [base])
base: Defaults to 10 if omitted
=LOG(100) → 2
=LOG(8, 2) → 3
LOG10
Returns the base-10 logarithm of a number.
Syntax: LOG10(number)
=LOG10(100) → 2
=LOG10(1000) → 3
MAX
Returns the largest value from a set of numbers.
Syntax: MAX(number1, [number2], ...)
=MAX(1, 5, 3) → 5
=MAX(10, -20, 15) → 15
MIN
Returns the smallest value from a set of numbers.
Syntax: MIN(number1, [number2], ...)
=MIN(1, 5, 3) → 1
=MIN(10, -20, 15) → -20
MOD
Returns the remainder after division.
Syntax: MOD(number, divisor)
=MOD(10, 3) → 1
=MOD(7, 2) → 1
MROUND
Rounds a number to the nearest multiple.
Syntax: MROUND(number, multiple)
=MROUND(10, 3) → 9
=MROUND(7, 2.5) → 7.5
ODD
Rounds a number up to the nearest odd integer.
Syntax: ODD(number)
=ODD(2) → 3
=ODD(3) → 3
=ODD(-1.5) → -3
PI
Returns the value of π (pi).
Syntax: PI()
=PI() → 3.14159265...
POWER
Returns a number raised to a power.
Syntax: POWER(number, power)
=POWER(2, 3) → 8
=POWER(5, 2) → 25
PRODUCT
Multiplies all arguments together.
Syntax: PRODUCT(number1, [number2], ...)
=PRODUCT(2, 3, 4) → 24
=PRODUCT(5, 10) → 50
QUOTIENT
Returns the integer portion of a division.
Syntax: QUOTIENT(numerator, denominator)
=QUOTIENT(10, 3) → 3
=QUOTIENT(7, 2) → 3
RAND
Returns a random number between 0 and 1.
Syntax: RAND()
=RAND() → 0.7234... (varies)
RANDBETWEEN
Returns a random integer between two values.
Syntax: RANDBETWEEN(bottom, top)
=RANDBETWEEN(1, 100) → 47 (varies)
=RANDBETWEEN(10, 20) → 15 (varies)
ROUND
Rounds a number to a specified number of digits.
Syntax: ROUND(number, num_digits)
=ROUND(2.15, 1) → 2.2
=ROUND(2.149, 1) → 2.1
=ROUND(1234, -2) → 1200
ROUNDDOWN
Rounds a number down (toward zero).
Syntax: ROUNDDOWN(number, num_digits)
=ROUNDDOWN(3.7, 0) → 3
=ROUNDDOWN(3.14159, 3) → 3.141
ROUNDUP
Rounds a number up (away from zero).
Syntax: ROUNDUP(number, num_digits)
=ROUNDUP(3.2, 0) → 4
=ROUNDUP(3.14159, 3) → 3.142
SIGN
Returns the sign of a number (-1, 0, or 1).
Syntax: SIGN(number)
=SIGN(-5) → -1
=SIGN(0) → 0
=SIGN(10) → 1
SQRT
Returns the square root of a number.
Syntax: SQRT(number)
=SQRT(16) → 4
=SQRT(2) → 1.41421...
SUM
Adds all numbers in the arguments.
Syntax: SUM(number1, [number2], ...)
=SUM(1, 2, 3) → 6
=SUM(10, 20, 30, 40) → 100
SUMIF
Sums values that meet a specified criteria.
Syntax: SUMIF(range, criteria, [sum_range])
=SUMIF(A1:A5, ">10") → Sum of values > 10
=SUMIF(A1:A5, "red", B1:B5) → Sum B where A = "red"
SUMPRODUCT
Returns the sum of products of corresponding array elements.
Syntax: SUMPRODUCT(array1, [array2], ...)
=SUMPRODUCT({1,2,3}, {4,5,6}) → 32 (1*4 + 2*5 + 3*6)
SUMSQ
Returns the sum of squares of the arguments.
Syntax: SUMSQ(number1, [number2], ...)
=SUMSQ(3, 4) → 25 (9 + 16)
TRUNC
Truncates a number to an integer by removing the decimal part.
Syntax: TRUNC(number)
=TRUNC(4.9) → 4
=TRUNC(-4.9) → -4
Text Functions
CHAR
Returns the character for a given ASCII code.
Syntax: CHAR(number)
=CHAR(65) → "A"
=CHAR(97) → "a"
CLEAN
Removes non-printable characters from text.
Syntax: CLEAN(text)
=CLEAN("Hello World") → "Hello World" (removes control chars)
CODE
Returns the ASCII code of the first character.
Syntax: CODE(text)
=CODE("A") → 65
=CODE("abc") → 97
CONCAT
Joins multiple text strings into one.
Syntax: CONCAT(text1, [text2], ...)
=CONCAT("Hello", " ", "World") → "Hello World"
CONCATENATE
Joins multiple text strings into one (same as CONCAT).
Syntax: CONCATENATE(text1, [text2], ...)
=CONCATENATE("A", "-", "001") → "A-001"
=CONCATENATE({color#code}, "_", {size#code})
COUNT_CHAR
Custom Function - Counts characters excluding spaces.
Syntax: COUNT_CHAR(text)
=COUNT_CHAR("Hello World") → 10
=COUNT_CHAR("A B C") → 3
=COUNT_CHAR({embossing#inputValue})
This is a custom Mimeeq function not available in standard Excel.
COUNT_WORD
Custom Function - Counts the number of words in text.
Syntax: COUNT_WORD(text)
=COUNT_WORD("Hello World") → 2
=COUNT_WORD("One two three") → 3
This is a custom Mimeeq function not available in standard Excel.
DOLLAR
Converts a number to text in currency format.
Syntax: DOLLAR(number, [decimals])
=DOLLAR(1234.567, 2) → "$1,234.57"
=DOLLAR(1234.567) → "$1,234.57"
EXACT
Checks if two text strings are exactly identical (case-sensitive).
Syntax: EXACT(text1, text2)
=EXACT("Hello", "Hello") → TRUE
=EXACT("Hello", "hello") → FALSE
FIND
Finds the position of text within another text (case-sensitive).
Syntax: FIND(find_text, within_text, [start_num])
- Returns
#VALUE!if not found
=FIND("o", "Hello World") → 5
=FIND("o", "Hello World", 6) → 8
LEFT
Returns the leftmost characters from a text string.
Syntax: LEFT(text, [num_chars])
num_chars: Defaults to 1
=LEFT("Hello", 3) → "Hel"
=LEFT("World") → "W"
LEN
Returns the length of a text string (including spaces).
Syntax: LEN(text)
=LEN("Hello World") → 11
=LEN("ABC") → 3
LOWER
Converts text to lowercase.
Syntax: LOWER(text)
=LOWER("HELLO") → "hello"
=LOWER("Hello World") → "hello world"
MID
Returns characters from the middle of a text string.
Syntax: MID(text, start_num, num_chars)
=MID("Hello World", 7, 5) → "World"
=MID("ABCDEF", 2, 3) → "BCD"
NUMBERVALUE
Converts text to a number, with locale-independent formatting.
Syntax: NUMBERVALUE(text, [decimal_separator], [group_separator])
=NUMBERVALUE("1,234.56", ".", ",") → 1234.56
=NUMBERVALUE("1.234,56", ",", ".") → 1234.56
PROPER
Capitalizes the first letter of each word.
Syntax: PROPER(text)
=PROPER("hello world") → "Hello World"
=PROPER("JOHN DOE") → "John Doe"
REPLACE
Replaces part of a text string with different text.
Syntax: REPLACE(old_text, start_num, num_chars, new_text)
=REPLACE("Hello", 1, 2, "XX") → "XXllo"
=REPLACE("2024", 3, 2, "25") → "2025"
REPT
Repeats text a specified number of times.
Syntax: REPT(text, number_times)
=REPT("*", 5) → "*****"
=REPT("Ab", 3) → "AbAbAb"
RIGHT
Returns the rightmost characters from a text string.
Syntax: RIGHT(text, [num_chars])
=RIGHT("Hello", 3) → "llo"
=RIGHT("World") → "d"
SEARCH
Finds text within another text (case-insensitive, supports wildcards).
Syntax: SEARCH(find_text, within_text, [start_num])
*matches any sequence of characters?matches any single character
=SEARCH("o", "Hello World") → 5
=SEARCH("WORLD", "Hello World") → 7
=SEARCH("w*d", "Hello World") → 7
SUBSTITUTE
Replaces occurrences of old text with new text.
Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])
- If
instance_numis omitted, all occurrences are replaced (differs from Excel's default)
=SUBSTITUTE("Hello World", "World", "There") → "Hello There"
=SUBSTITUTE("aaa", "a", "b") → "bbb"
=SUBSTITUTE("aaa", "a", "b", 2) → "aba"
Unlike Excel (which replaces only the first occurrence by default), Mimeeq's SUBSTITUTE replaces all occurrences when instance_num is omitted.
T
Returns the text if the value is text, otherwise returns empty string.
Syntax: T(value)
=T("Hello") → "Hello"
=T(123) → ""
=T(TRUE) → ""
TEXT
Formats a number as text with a specified format.
Syntax: TEXT(value, format_text)
=TEXT(1234.567, "0.00") → "1234.57"
=TEXT(0.25, "0%") → "25%"
=TEXT(5, "00") → "05"
=TEXT({coverColor#bom}, "00") → "05" (pads with zero)
Common format codes:
| Format | Example Input | Output |
|---|---|---|
"0" | 5.7 | "6" |
"0.00" | 5.7 | "5.70" |
"00" | 5 | "05" |
"000" | 5 | "005" |
"#,##0" | 1234 | "1,234" |
"0%" | 0.25 | "25%" |
TRIM
Removes leading, trailing, and excess spaces from text.
Syntax: TRIM(text)
=TRIM(" Hello World ") → "Hello World"
UNICHAR
Returns the Unicode character for a given code point.
Syntax: UNICHAR(number)
=UNICHAR(65) → "A"
=UNICHAR(8364) → "€"
UNICODE
Returns the Unicode code point of the first character.
Syntax: UNICODE(text)
=UNICODE("A") → 65
=UNICODE("€") → 8364
UPPER
Converts text to uppercase.
Syntax: UPPER(text)
=UPPER("hello") → "HELLO"
Logical Functions
AND
Returns TRUE if all arguments are TRUE.
Syntax: AND(logical1, [logical2], ...)
=AND(TRUE, TRUE) → TRUE
=AND(TRUE, FALSE) → FALSE
=AND(5>3, 10>5) → TRUE
=AND({HAS_LINING#variable}="true", {HAS_POCKETS#variable}="true")
FALSE
Returns the logical value FALSE.
Syntax: FALSE()
=FALSE() → FALSE
IF
Returns one value if a condition is TRUE, another if FALSE.
Syntax: IF(logical_test, value_if_true, [value_if_false])
=IF(5>3, "Yes", "No") → "Yes"
=IF({HAS_LINING#variable}="true", "L", "N")
=IF(COUNT_CHAR({embossing#inputValue})>3, 10, 3)
IFERROR
Returns a specified value if the expression results in an error.
Syntax: IFERROR(value, value_if_error)
=IFERROR(10/0, "Error") → "Error"
=IFERROR(10/2, "Error") → 5
IFNA
Returns a specified value if the expression results in #N/A.
Syntax: IFNA(value, value_if_na)
=IFNA(MATCH("x", A1:A5, 0), "Not found")
IFS
Checks multiple conditions and returns the value for the first TRUE condition.
Syntax: IFS(condition1, value1, [condition2, value2], ...)
=IFS(
{SIZE#variable}="small", "S",
{SIZE#variable}="medium", "M",
{SIZE#variable}="large", "L",
TRUE, "Unknown"
)
Use TRUE as the last condition to provide a default value (similar to "else").
NOT
Reverses the logical value of its argument.
Syntax: NOT(logical)
=NOT(TRUE) → FALSE
=NOT(FALSE) → TRUE
=NOT({SIZE_FORMAT#variable}="a4")
OR
Returns TRUE if any argument is TRUE.
Syntax: OR(logical1, [logical2], ...)
=OR(TRUE, FALSE) → TRUE
=OR(FALSE, FALSE) → FALSE
=OR({SIZE_FORMAT#variable}="xl", {SIZE_FORMAT#variable}="")
TRUE
Returns the logical value TRUE.
Syntax: TRUE()
=TRUE() → TRUE
Date & Time Functions
DATE
Creates a date from year, month, and day components.
Syntax: DATE(year, month, day)
=DATE(2024, 12, 25) → December 25, 2024
=DATE(2024, 13, 1) → January 1, 2025 (auto-adjusts)
DATEDIF
Calculates the difference between two dates.
Syntax: DATEDIF(start_date, end_date, unit)
| Unit | Returns |
|---|---|
"Y" | Complete years |
"M" | Complete months |
"D" | Days |
"MD" | Days ignoring months and years |
"YM" | Months ignoring years |
"YD" | Days ignoring years |
=DATEDIF(DATE(2020,1,1), DATE(2024,6,15), "Y") → 4
=DATEDIF(DATE(2020,1,1), DATE(2024,6,15), "M") → 53
DATEVALUE
Converts a date string to a serial number.
Syntax: DATEVALUE(date_text)
=DATEVALUE("2024-12-25") → 45651
DAY
Returns the day of the month (1-31).
Syntax: DAY(date)
=DAY(DATE(2024, 12, 25)) → 25
=DAY(TODAY()) → (current day)
DAYS
Returns the number of days between two dates.
Syntax: DAYS(end_date, start_date)
=DAYS(DATE(2024,12,31), DATE(2024,1,1)) → 365
EDATE
Returns a date a specified number of months before or after a date.
Syntax: EDATE(start_date, months)
=EDATE(DATE(2024,1,15), 3) → April 15, 2024
=EDATE(DATE(2024,1,15), -1) → December 15, 2023
EOMONTH
Returns the last day of a month, a specified number of months away.
Syntax: EOMONTH(start_date, months)
=EOMONTH(DATE(2024,1,15), 0) → January 31, 2024
=EOMONTH(DATE(2024,1,15), 1) → February 29, 2024
HOUR
Returns the hour (0-23) from a time value.
Syntax: HOUR(time)
=HOUR(TIME(14, 30, 0)) → 14
ISOWEEKNUM
Returns the ISO week number for a date.
Syntax: ISOWEEKNUM(date)
=ISOWEEKNUM(DATE(2024,1,1)) → 1
MINUTE
Returns the minute (0-59) from a time value.
Syntax: MINUTE(time)
=MINUTE(TIME(14, 30, 45)) → 30
MONTH
Returns the month (1-12) from a date.
Syntax: MONTH(date)
=MONTH(DATE(2024, 12, 25)) → 12
=MONTH(TODAY()) → (current month)
NETWORKDAYS
Returns the number of working days between two dates.
Syntax: NETWORKDAYS(start_date, end_date, [holidays])
=NETWORKDAYS(DATE(2024,1,1), DATE(2024,1,31)) → 23
NETWORKDAYS.INTL
Returns working days with custom weekend specification.
Syntax: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
| Weekend | Days Off |
|---|---|
| 1 | Saturday, Sunday |
| 2 | Sunday, Monday |
| 11 | Sunday only |
| 17 | Saturday only |
NOW
Returns the current date and time.
Syntax: NOW()
=NOW() → Current date and time
SECOND
Returns the second (0-59) from a time value.
Syntax: SECOND(time)
=SECOND(TIME(14, 30, 45)) → 45
TIME
Creates a time value from hours, minutes, and seconds.
Syntax: TIME(hour, minute, second)
=TIME(14, 30, 0) → 2:30 PM
TIMEVALUE
Converts a time string to a decimal number.
Syntax: TIMEVALUE(time_text)
=TIMEVALUE("6:00 AM") → 0.25
TODAY
Returns the current date.
Syntax: TODAY()
=TODAY() → Current date
WEEKDAY
Returns the day of the week for a date.
Syntax: WEEKDAY(date, [return_type])
| Return Type | Sunday | Monday | ... | Saturday |
|---|---|---|---|---|
| 1 (default) | 1 | 2 | ... | 7 |
| 2 | 7 | 1 | ... | 6 |
| 3 | 6 | 0 | ... | 5 |
=WEEKDAY(DATE(2024,12,25)) → 4 (Wednesday)
WEEKNUM
Returns the week number of a date.
Syntax: WEEKNUM(date, [return_type])
=WEEKNUM(DATE(2024,1,15)) → 3
WORKDAY
Returns a date a specified number of working days away.
Syntax: WORKDAY(start_date, days, [holidays])
=WORKDAY(DATE(2024,1,1), 10) → January 15, 2024
WORKDAY.INTL
Returns a workday with custom weekend specification.
Syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays])
YEAR
Returns the year from a date.
Syntax: YEAR(date)
=YEAR(DATE(2024, 12, 25)) → 2024
=YEAR(TODAY()) → (current year)
YEARFRAC
Returns the fraction of the year between two dates.
Syntax: YEARFRAC(start_date, end_date, [basis])
=YEARFRAC(DATE(2024,1,1), DATE(2024,7,1)) → 0.5
Statistical Functions
AVERAGE
Returns the arithmetic mean of numbers.
Syntax: AVERAGE(number1, [number2], ...)
=AVERAGE(1, 2, 3, 4, 5) → 3
=AVERAGE(10, 20) → 15
AVERAGEA
Returns the average, including text (as 0) and logical values.
Syntax: AVERAGEA(value1, [value2], ...)
=AVERAGEA(1, 2, TRUE, FALSE) → 1 (TRUE=1, FALSE=0)
AVERAGEIF
Returns the average of cells that meet a criteria.
Syntax: AVERAGEIF(range, criteria, [average_range])
=AVERAGEIF(A1:A10, ">5")
=AVERAGEIF(A1:A10, "red", B1:B10)
COUNT
Counts how many numbers are in the arguments.
Syntax: COUNT(value1, [value2], ...)
=COUNT(1, 2, "text", 4) → 3
COUNTIF
Counts cells that meet a criteria.
Syntax: COUNTIF(range, criteria)
Criteria options:
">10"- Greater than 10"<>0"- Not equal to 0"red"- Equals "red""*text*"- Contains "text"
=COUNTIF(A1:A10, ">5")
=COUNTIF(A1:A10, "red")
UNIQUE_COUNT
Custom Function - Counts unique values in a range.
Syntax: UNIQUE_COUNT(value1, [value2], ...)
=UNIQUE_COUNT("a", "b", "a", "c") → 3
=UNIQUE_COUNT(1, 2, 2, 3, 3, 3) → 3
This is a custom Mimeeq function not available in standard Excel.
Information Functions
ISBLANK
Returns TRUE if the cell is empty.
Syntax: ISBLANK(value)
=ISBLANK("") → TRUE
=ISBLANK("text") → FALSE
ISERR
Returns TRUE if the value is any error except #N/A.
Syntax: ISERR(value)
=ISERR(1/0) → TRUE
=ISERR(#N/A) → FALSE
ISERROR
Returns TRUE if the value is any error.
Syntax: ISERROR(value)
=ISERROR(1/0) → TRUE
=ISERROR(#N/A) → TRUE
ISEVEN
Returns TRUE if the number is even.
Syntax: ISEVEN(number)
=ISEVEN(4) → TRUE
=ISEVEN(5) → FALSE
ISLOGICAL
Returns TRUE if the value is a logical value (TRUE or FALSE).
Syntax: ISLOGICAL(value)
=ISLOGICAL(TRUE) → TRUE
=ISLOGICAL("TRUE") → FALSE
ISNA
Returns TRUE if the value is the #N/A error.
Syntax: ISNA(value)
=ISNA(#N/A) → TRUE
=ISNA(#VALUE!) → FALSE
ISNONTEXT
Returns TRUE if the value is not text.
Syntax: ISNONTEXT(value)
=ISNONTEXT(123) → TRUE
=ISNONTEXT("text") → FALSE
ISNUMBER
Returns TRUE if the value is a number.
Syntax: ISNUMBER(value)
=ISNUMBER(123) → TRUE
=ISNUMBER("123") → FALSE
ISTEXT
Returns TRUE if the value is text.
Syntax: ISTEXT(value)
=ISTEXT("hello") → TRUE
=ISTEXT(123) → FALSE
Engineering Functions
DELTA
Tests whether two values are equal.
Syntax: DELTA(number1, [number2])
Returns 1 if equal, 0 otherwise.
=DELTA(5, 5) → 1
=DELTA(5, 4) → 0
GESTEP
Tests whether a number is greater than or equal to a threshold.
Syntax: GESTEP(number, [step])
Returns 1 if number >= step, otherwise 0.
=GESTEP(5, 4) → 1
=GESTEP(3, 4) → 0
Real-World Examples
Example 1: Character Count Pricing
Calculate embossing price based on character count:
=IF(COUNT_CHAR({embosingbrc#inputValue})>3, 10, 3)
If embossing text has more than 3 characters (excluding spaces), charge 10; otherwise charge 3.
Example 2: Complex Product Code Logic
Determine product code based on multiple conditions:
=IF(
{HAS_LINING#variable}="false",
IF(
{HAS_POCKETS#variable}="false",
IF({IS_A5_A6#variable}="true", "lp", "lc"),
"lp"
),
IF(
AND(
{HAS_POCKETS#variable}="false",
OR(
{SIZE_FORMAT#variable}="xl",
{SIZE_FORMAT#variable}="",
{SIZE_FORMAT#variable}="a4"
)
),
"lc",
"lp"
)
)
Example 3: Conditional Character Counter
Count characters only if field has content:
=IF(
{embosingbrca6pocket#inputValue}="",
0,
COUNT_CHAR({embosingbrca6pocket#inputValue})
)
Example 4: Multi-Field Selection
Select the first non-empty embossing value from multiple fields:
=IF(
{CORNER_A4_CHAR_COUNT#variable}>0,
{embosingbrca4#inputValue},
IF(
{CORNER_POCKET_CHAR_COUNT#variable}>0,
{embosingbrca6pocket#inputValue},
IF(
{CORNER_CHAR_COUNT#variable}>0,
{embosingbrc#inputValue},
""
)
)
)
Example 5: Dynamic Code Generation with TEXT Formatting
Generate padded codes with conditional suffixes:
=IF(
AND({SIZE_FORMAT#variable}="a4", {FLAP_A4_LINE2_CHAR_COUNT#variable}>0),
CONCATENATE({embosingcolourflapa4#code}, "04"),
IF(
AND(
OR({SIZE_FORMAT#variable}="", {SIZE_FORMAT#variable}="a6"),
{FLAP_A6_LINE2_CHAR_COUNT#variable}>0
),
CONCATENATE({embosingcolourflapa6#code}, "04"),
IF(
AND(
{FLAP_LINE2_CHAR_COUNT#variable}>0,
NOT({SIZE_FORMAT#variable}="a4"),
NOT({SIZE_FORMAT#variable}=""),
NOT({SIZE_FORMAT#variable}="a6")
),
CONCATENATE({embosingcolourflap#code}, "04"),
""
)
)
)
Example 6: BOM Item Code with Zero-Padded Numbers
Generate BOM codes with formatted numeric values:
=IF(
{leftpocket#code}="PB",
CONCATENATE("lp", {SIZE_FORMAT#variable}, "_", TEXT({Bpocketcolour#bom}, "00"), "_B_nom"),
IF(
{leftpocket#code}="PE",
CONCATENATE("lp", {SIZE_FORMAT#variable}, "_", TEXT({Bpocketcolour#bom}, "00"), "_B"),
""
)
)
Example 7: Conditional Thread Color Code
Include thread color only when lining or pockets are present:
=IF(
OR({HAS_LINING#variable}="true", {HAS_POCKETS#variable}="true"),
{chooseyourthreadscolour#code},
""
)
Example 8: Double Ribbon Configuration
Generate secondary ribbon code only for double ribbon option:
=IF(
{numberofribbonsinside#code}="double",
CONCATENATE(
{chooseyourribboncolourinside2#code},
"_",
{INTERNAL_ELASTIC_LENGTH#variable}
),
""
)
Example 9: Complex SKU Assembly
Build a complete SKU with conditional parts:
=IF(
{NEEDS_SPLIT#variable}="true",
IF(
OR({SIZE_FORMAT#variable}="", {SIZE_FORMAT#variable}="xl"),
CONCATENATE(
{BASE_PREFIX#variable},
{SIZE_FORMAT#variable},
TEXT({chooseyourcovercolour#bom}, "00"),
IF({NEEDS_A_SUFFIX#variable}="true", "_A", ""),
{FEATURES_CODE#variable},
"_as"
),
CONCATENATE(
{BASE_PREFIX#variable},
{SIZE_FORMAT#variable},
"_",
TEXT({chooseyourcovercolour#bom}, "00"),
IF({NEEDS_A_SUFFIX#variable}="true", "_A", ""),
{FEATURES_CODE#variable},
"_as"
)
),
""
)
Testing & Debugging Formulas
The Admin Panel provides built-in debuggers to test and verify your formulas before publishing.
Pricing Debugger
Located in Product Preview → Pricing Debugger, this tool shows:
- Input Pattern → Output Pattern: See how your pricing pattern resolves
- Input Quantity → Output Quantity: Verify quantity formula results
- Variables: View input patterns and resolved output values for each variable
- Additional Price Codes: See all extracted price codes
Use the Pricing Debugger to verify that your pricing patterns and variables produce the expected codes for different product configurations.
BOM Debugger
Located in Product Preview → BOM Debugger, this tool shows:
- Name Pattern → Output Name: Verify BOM item name formulas
- Item Code Pattern → Output Item Code: Check code generation
- Input Quantity → Output Quantity: Test quantity calculations
- Variables: View input and output values
- Sub-components: Inspect nested BOM items
- Custom Fields: See resolved custom field values
Use the BOM Debugger to ensure your Bill of Materials generates correctly for all product configurations.
Always test your formulas with multiple product configurations to ensure they handle all edge cases correctly.
Tips & Best Practices
Nesting IF Statements
For multiple conditions, consider:
- Using
IFS()for cleaner syntax - Breaking complex logic into separate variables
- Using
AND()andOR()to combine conditions
String Comparisons
Always use quotes around string values in comparisons:
✓ =IF({variable#variable}="true", ...)
✗ =IF({variable#variable}=true, ...)
Handling Empty Values
Check for empty strings before processing:
=IF({field#inputValue}="", 0, COUNT_CHAR({field#inputValue}))
Number Formatting
Use TEXT() to ensure consistent number formatting in codes:
=TEXT({color#bom}, "00") → "05" instead of "5"
Case Sensitivity
FIND()is case-sensitiveSEARCH()is case-insensitive- String comparisons (
=,<>) are case-sensitive
Error Reference
| Error | Cause |
|---|---|
#VALUE! | Wrong data type (e.g., text where number expected) |
#DIV/0! | Division by zero |
#NAME? | Unknown function or variable name |
#NUM! | Invalid numeric value |
#N/A | Value not found (in lookup functions) |
#REF! | Invalid reference |
See Also
- Code Input Reference - Syntax and available suffixes
- Variables Reference - Named calculations for reusable logic