Operis’ latest version of OAK5 provides a complete solution to the problems that arise from dynamic arrays in Excel. Ensuring you have 100% confidence in your spreadsheets and models, both for clients, and internally.
Why you need to care about dynamic arrays?
- Dynamic arrays can appear where none existed before. If multiple people are working on a spreadsheet using different versions of Excel dynamic arrays can occasionally appear and alter the numbers in the spreadsheet.
- Value positions are no longer fixed at design time. When using dynamic arrays, the location is determined afresh every time the end-user recalculates the spreadsheet.
- ‘Spilling’. A cell can be in range of several spilling formulae and there’s no way to predict which one affected the value. Therefore, it is essential to understand the structure of the spreadsheet, identify and locate dynamic arrays and potential spilling. OAK 5 can assist in achieving this.
Why you need to upgrade to OAK5
OAK 4 relied on the notion that a formula can be identified with a particular cell on a worksheet, which is no longer the case. The move to dynamic arrays means that a report has the potential to change if the model inputs are altered.
Therefore, it is now possible for two worksheets to be identical as to their formulas, but, due to a small change in their input assumptions, have dynamic array results of very different range dimensions.
However, with OAK5’s new release – Version 5 – we have modified the reports to list which formulae are active in which cells given the current inputs. Thus, OAK 5 provides consistency with the OAK behaviour that customers are well used to and give users the confidence of the results presented by their spreadsheet.
How OAK 5 solves the problem
- The new workbook summary shows the number dynamic array formulas as well as spilled formulas.
- The ‘distinct formulas’ and ‘risk analysis’ reports will distinguish ‘normal’ formulas from dynamic array formulas.
- Map tool will now show dynamic array formulas and corresponding spill using a different shade making the distinction to normal formulas very easy.
- The compare tool reports in OAK5 recognises where workbooks differ independently of being normal, dynamic array or spilled formulas.
