What functions are available in Openbox?
Iain Morrow
on
May 12, 2023
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’
Is it possible to work on two – or more than one – Openbox models at the same time?
Iain Morrow
on
May 12, 2023
No, this is not yet possible. It is on our product roadmap for the future, so watch this space.
How do I set a calculation to be on one timeline or another?
Iain Morrow
on
May 11, 2023
By putting it on a sheet with that timeline.
Each sheet in Openbox has a timeline associated with it. You can see this by the letter at the top right of the sheet. In the image below, the “Ops” sheet is on a Quarterly basis, and the “Construction” sheet is on a Monthly basis.
If no letter is shown, your model only has one timeline.
If you put a calculation on a sheet that has a monthly timeline, that calculation will be done monthly. If you move the calculation to another sheet with (say) a quarterly timeline, the calculation will now be done quarterly.
The easiest way to set a sheet’s timeline is to right click on it and choose “Put on secondary axis” or “Put on primary axis”.
For inputs, you can set the timeline individually. Just right click on each input and choose “Flip to primary axis” or “Flip to secondary axis”.
How do I change the file that Openbox starts a new model with?
Iain Morrow
on
May 10, 2023
When you click “New” in Openbox, you get a window with a dropdown labelled “Choose template”. Each Openbox model starts with a template with some basic calculations. But suppose you always build models for one sector. Why not start with a template that has standard calculations for that sector? You can create a new template and add it to this list as follows:
- Create a new Openbox model
- Add the calculations you want.
- Save it in your Openbox component library, in the main folder. This will usually be the “OBXComponents” directory in your Documents folder.
- Close and restart Excel
Now when you click “New” in Openbox, the model you created will be available as a starting template in that list.
What does the setting “show formula name on all rows” do or mean?
Iain Morrow
on
April 27, 2023
Suppose you have a calculation block that varies by an array. Maybe you have “Revenue by product”, and it shows five rows, one for each product. Some people like to show “Revenue” on each of those five rows, whereas others like to show it only in the top row, and in the rows below, just show the names of the products.
Openbox defaults to the first approach (show name on all rows) but you can switch to the second by going to the “Layout” table of Openbox settings and unchecking “Show formula name on all rows”
How do I add blank or spacer rows to a report?
Iain Morrow
on
April 27, 2023
Click the report then click “Insert” and “New heading”. Then edit the name of the heading to be a space. This will show up as a blank row. Then drag it to the place you want it to go.
Why aren’t the styles in my template being applied?
Iain Morrow
on
April 27, 2023
This may be because Openbox is set to ignore these colours and styles and use the FAST standard ones instead.
To check this, in the main Openbox window, click “Help and Settings” then “Openbox settings”. Click the “Standards” tab then make sure that “Apply FAST…” is unchecked. Click OK to close the settings window.
How do I get rid of the input scenarios?
Iain Morrow
on
April 27, 2023
In the main Openbox window, click “Help and Settings” then “Openbox settings”. Click the “Layout” tab then select “One sheet” in the Input Layout section. Click OK to close the settings window.
Can I show the preview spreadsheet on my second screen?
Iain Morrow
on
April 24, 2023
You want to see the main Openbox view on one screen and the preview spreadsheet on another. To do this:
- In the Openbox window, click “Help and settings” then “Openbox settings”
- Click “Other”
- Make sure the box “Show preview in new window (requires restart)” is ticked
- Press OK.
- Close and restart Excel
When you next create the preview it will appear as a separate window to the main Openbox window. This means you can move it around, including to a second (or third) screen if you have one.