Type
Name
Syntax
Example
Pricing
ADDMARGIN(Cost,Margin)
Margin must be <1 or <100%
Description
Math
Returns the Sell Price from a Cost Price and a Margin percentage.
ADDMARGIN(100, .3) = 142.857
OR; Where cell in Cost column has value $100 and cell in Margin column has value 30% then ADDMARGIN($cost, $margin) = 142.857
ADDMARGIN
ROUNDDOWN(3.333, 2) = 3.33
OR; Where cell in Sell Price column has value $3.333 then ROUNDDOWN($sellPrice, 2) = $3.3
ROUNDDOWN
Statistical
COUNTIF
ROUNDDOWN(number, places)
Reduces the number of decimal places and rounds down.
COUNTIF([‘one’, ‘two’, ‘three’] ‘o’) = 2
OR; Where cells in columns, Item1, Item2 and Item have values one, two and three then COUNTIF([$item1, $item2, $item3] ‘o’) = 2
COUNTIF(['string1', 'string2', 'string3', 'string4'], ‘condition')
[square brackets] = array
Returns a count of the items that meet the condition.
Logical
AND
AND(logical_expression1, [logical_expression2, ...])
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false
AND(2>1, 3>2, 4>3) = true
OR; Where cells in columns, Item1, Item2 and Item have values 2, 3 and 4 then AND($item1 >1, $item2 >2, $item3 >3) = true
ProductEngine Functions
Top 20
Pricing
ADDMARKUP
ADDMARKUP(Cost,Markup)
Returns the Sell Price from a Cost Price and a Markup percentage.
ADDMARKUP(100, .3) = 130.00
OR; Where cell in Cost column has value $100 and cell in Markup column has value 30% then ADDMARKUP($cost, $margin) = 130.00
Pricing
PROFIT
PROFIT(Cost,Sell_Price,[Expense])
[square brackets] = optional input
Returns the Profit as a number from the Sell Price less Cost, less any additional Expense.
PROFIT(100,300) = 200.00
OR; Where cell in Cost column has value $100 and cell in Sell Price column has value $300 then PROFIT($cost, $sellPrice) = 200.00
Pricing
MARGIN
MARGIN(Cost,Sell_Price,[Expense])
[square brackets] = optional input
Returns the Margin as a percentage from the Sell Price less Cost, less any additional Expense.
MARGIN(100,200) = .5
OR; Where cell in Cost column has value $100 and cell in Sell Price column has value $200 then MARGIN($cost, $sellPrice) = 50%
Math
CEILING
CEILING(number, factor)
Rounds up the value to the nearest multiple of the factor.
CEILING(473, 5) = 475
OR; Where cell in Sell Price column has value $473.00 then CEILING($sellPrice, 5) = $475
FLOOR(473, 5) = 470
OR; Where cell in Sell Price column has value $473.00 then FLOOR($sellPrice, 5) = $470
Math
FLOOR
FLOOR(number, factor)
Rounds down the value to the nearest multiple of the factor.
ROUNDUP(3.333, 2) = 3.34
OR; Where cell in Sell Price column has value $3.333 then ROUNDUP($sellPrice, 2) = $3.34
Math
ROUNDUP
ROUNDUP(number, places)
Reduces the number of decimal places and rounds up.
SUM(100, 50, 25) = 175
OR; Where cells in cost columns, Cost1, Cost2 and Cost3 have values 100, 50 and 25 then SUM($cost1, $cost2, $cost3) = $175
Math
SUM
SUM(number1, number2, [number…])
[square brackets] = optional input/s
Returns the sum of a series of numbers.
SUMIF([100, 50, 25], ‘>49’) = 150
OR; Where cells in cost columns, Cost1, Cost2 and Cost3 have values 100, 50 and 25 then SUMIF([$cost1, $cost2, $cost3], ‘>49’) = $150
Math
SUMIF
SUMIF([number1,number2,…], ‘criterion’)
[square brackets] = array
Returns the sum of all values that meet the criterion.
COUNT(100, 50, 25) = 3
OR; Where cells in cost columns, Cost1, Cost2 and Cost3 have values 100, 50 and 25 then COUNT($cost1, $cost2, $cost3) = 3
Statistical
COUNT
COUNT(number1,number2, …])
Returns a count of the number of numeric values.
CONCATENATE('one', ' ', ‘three') = one three
OR; Where cells in columns, Item1, and Item2 have values one and three then CONCATENATE($item1, ' ', $item2) = one three
Text
CONCATENATE
CONCATENATE(‘string1', ‘string2')
Appends strings to one another.
IF(2>1, Yes, No) = Yes
OR; Where cell in column Item, has value 2 then IF($item >1, Yes, No) = Yes
Logical
IF
IF(logical_expression, value_if_true, value_if_false)
Returns one value if a logical expression is `TRUE` and another if it is `FALSE`
Logical
NOT
NOT(logical_expression)
Returns the opposite of a logical value - `NOT(TRUE)` returns `FALSE`; `NOT(FALSE)` returns `TRUE`
NOT(2>1) = false
OR; Where cell in column Item, has value 2 then NOT($item >1) = false
Logical
OR
OR(logical_expression1, [logical_expression2, ...])
[square brackets] = optional input/s
Returns true if any of the provided arguments are logically true, and false if all of the provided arguments are logically false.
OR(2>1, 2>2, 2>3) = true
OR; Where cells in columns, Item1, Item2 and Item have values 2, 2 and 2 then AND($item1 >1, $item2 >2, $item3 >3) = true
Text
LOWER
LOWER(‘text’)
Converts a specified string to lowercase.
LOWER(‘EXAMPLE’) = example
OR; Where cell in column, Text has value EXAMPLE then LOWER($text) = example
Text
UPPER
UPPER(‘text’)
Converts a specified string to uppercase.
UPPER(‘example’) = EXAMPLE
OR; Where cell in column, Text has value example then UPPER($text) = EXAMPLE
Text
PROPER
PROPER(‘text’)
Returns the text with the first letter in each word capitalised and all other letters in lowercase.
PROPER(‘this is an examPLE’) = This Is An Example
OR; Where cell in column, Text has value this is an examPLE then PROPER($text) = This Is An Example