Openbox

Try Openbox

Error 2349 when installing or updating

Iain Morrow on May 24, 2023

Sometimes when installing Openbox, or updating it, you might see a message about an unexpected error or a file error, with the error code 2349.

This means that for some reason the installation file is damaged. Most likely, it failed to download correctly. We suggest you download a new copy from www.openboxmodels.com/download-page/ and try again.

If the issue persists, it may be because of anti-virus software. You may want to turn this off temporarily while you download and install.

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’

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:

  1. Create a new Openbox model
  2. Add the calculations you want.
  3. Save it in your Openbox component library, in the main folder. This will usually be the “OBXComponents” directory in your Documents folder.
  4. 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”

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:

  1. In the Openbox window, click “Help and settings” then “Openbox settings”
  2. Click “Other”
  3. Make sure the box “Show preview in new window (requires restart)” is ticked
  4. Press OK.
  5. 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.