What functions are available in Openbox?
Openbox recognizes most common Excel function names, and when you start typing it will suggest from a list of available functions that include the letters you type. If you type ‘F’ for example, it will show you functions such as IF and SUMIF.
Here is a list of all the functions Openbox recognizes.
FUNCTIONS THAT WORK LIKE THOSE IN EXCEL
ABS
AND
AVERAGE
CEILING
COMBIN
CONCATENATE
CONVERT
COUNT
COUNTA
COUNTBLANK
DATE
DATEDIF
DAY
DAYS360
EDTAE
EOMONTH
EXP
FIND
FLOOR
GETPIVOTDATA
IFERROR
IFNA
INT
INTERCEPT
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISNA
ISNUMBER
LEFT
LN
LOG
LOG10
MAX
MIN
MMULT
MOD
MONTH
NA
NORM.DIST
NORMINV
NOT
NPV
OR
PMT
POWER
PPMT
PRODUCT
RAND
RANDBETWEEN
RIGHT
ROUND
ROUNDDOWN
ROUNDUP
SLN
SLOPE
SUBTOTAL
SUM
TIM
WEBSERVICE
WEEKNUM
WEIBULL.DIST
XIRR
XNPV
YEAR
YEARFRAC
OPENBOX SPECIFIC FUNCTIONS FOR SELECTING SOMETHING
SWITCH
SWITCH(<value to switch on>, <value 1>, <result 1> <value 2>, <result 2>, …)
Match the value to switch on against the list of values, and return the corresponding result
CHOOSE
CHOOSE(<value to switch on>, <option 1>, <option 2>, …
Pick one of a number of values, based on the first value
SUMPRODUCT
SUMPRODUCT(<value 1>, <value 2>, …) – multiplies each element of value1 and value2 and returns the total. Often used with ALLVALUES e.g. SUMPRODUCT(ALLVALUES([Some flag]), ALLVALUES([Cash flow])) returns the total cashflow on the dates that the flag applies
INDEX
INDEX( <item>, <row number>, <column number>, <area number> ) – returns the value at the given location for the value
MATCH
MATCH( lookup value, lookup array, match type) – looks in the given array for a value, and returns the position in the array {example}
LOOKUP
LOOKUP(<line item>,<lookup value>,<lookup array>) – looks for the lookup value in the first array of the specified item, and returns the value for that item. Optionally, you can specify which array to look in
https://openboxmodels.com/documentation/lookup-function/
SELECT
SELECT(<line item>,<index of array to choose>,<array name> – look for the value in array name that is at the specific position (index) specified. The <array name> parameter is only required if there is more than one array
https://openboxmodels.com/documentation/select-function/
INTERSECT
INTERSECT(<item>,<row label>,<column label>) – choose a value from a table, specifying the row and column label
SUMIF and SUMIFS
SUMIF(<item>, <criteria>, <sum item>) – as for SUMIF in Excel, although you might find the ARRAYVALUES and ARRAYELEMENTS functions useful here. <example>
COUNTIF and COUNTIFS
COUNTIF(<item>,<criteria>,<item to count>) – as for COUNTIF in Excel
COUNTIFS(<item>,<criteria 1>, …) – counts the number of cells that match the given criteria, across several different cell range
OPENBOX SPECIFIC FUNCTIONS THAT RELATE TO TIME
SECONDARYAXIS
SECONDARYAXIS(<item>) – gets the values from the secondary axis version of this item
PRIMARYAXIS
PRIMARYAXIS(<item>) – gets the values from the primary axis version of this item
DELAY
DELAY(<item>, <length of delay>, < value before start> ) – returns the value of the given item a number of periods ago (based on length of delay). <value before start> is used when the delay would take you to before the start of the model. If you leave out <item>, the current item, that the formula is for, is used
PREVIOUSVALUE
PREVIOUSVALUE(<item>) – returns the previous value of the given item, or the previous value of the current item if no item is specified”. See https://openboxmodels.com/documentation/previousvalue-function/
NEXTVALUE
NEXTVALUE(<item>) – returns the next value of the given item, or the next value of the current item if no item is specified. Note this can easily lead to circularities. E.g. NEXTVALUE([Cashflow]) is the cashflow in the next
BEG
BEG(<balance name>) – returns the opening value of the balance. If you don’t specify the name, it returns the opening value of the balance it is is. https://openboxmodels.com/documentation/beg-function/
PREVIOUSVALUES
PREVIOUSVALUES() – returns the cumulative total of all values to the left”
PASTVALUES
PASTVALUES(<item>, <include current period>, <number of periods to include>) – historic values for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included
e.g. PASTVALUES([Cash flow],1,6) returns the cashflow for the 6 months leading up to and including this period
PASTMIN
PASTMIN(<item>, <include current period>, <number of periods to include>) – the minimum historic value for the specified item, looking over the specified periods. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included
PASTMAX
PASTMAX(<item>, <include current period>, <number of periods to include>) – the maximum historic value for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included
PASTMEAN
PASTMEAN(<item>, <include current period>, <number of periods to include>) – the average historic value for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all historic periods will be included
SUMOVER
SUMOVER(<item>,<array to sum over>) – – gets the total over all items in the named array, or over all arrays if you don’t name one.
MAXOVER
MAXOVER(<item>,<array to look for the maximum over>) – gets the maximum over all items in the named array, or over all arrays if you don’t name one
MINOVER
MINOVER(<item>,<array to look for the minimum over> – gets the minimum over all items in the named array, or over all arrays if you don’t name one )
MEANOVER
MEANOVER(<item>,<array to look for the average over> – gets the mean (average) over all items in the named array, or over all arrays if you don’t name one
FINALVALUE
FINALVALUE(<item>) – the value of the line item in the last time period
FUTUREVALUES
FUTUREVALUES(<item>, <include current period>, <number of periods to include>) – future values for the specified item. If Include Current Period=1, the current value will be included. If you don’t specify number of periods to include, all future periods will be included.
ALLVALUES
ALLVALUES(<item>,…) – the value of the specified line item(s) for all time periods https://openboxmodels.com/documentation/allvalues-function/
OPENBOX SPECIFIC FUNCTIONS RELATING TO ARRAYS
ARRAYVALUES
ARRAYVALUES(<item>, <array>) – returns all the values for the specified item, for the specified array, as a range. Useful with SUMIF
ELEMENTNAME
ELEMENTNAME(<array>)
Makes the function vary by the specified array, and returns the name of the element, one per row
OTHER FUNCTIONS
RECURSE
RECURSE(<arguments>) – applies a function to itself. Used only for creating Lambdas
LAMBDA
LAMBDA(<parameter list>,<function description>) – create a LAMBDA function to include in Excel
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67
LABEL
LABEL(<line item>) – returns the name of the item e.g. LABEL([Revenue]) returns ‘Revenue’