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