Excels Dynamic Array Formula and the Mysterious @ sign

Recently (I'm not sure when), Microsoft made a very big change to formula functionality with Dynamic Array Formulas:

Microsoft Link

Link

Dynamic array formulas enable a function to "spill" its results to adjacent cells, if that function returns more than one value. This gave Microsoft a problem - what to do with 3rd party functions where Excel doesn't know if it returns multiple values? Their answer is the '@' prefix , which may appear A LOT on saved workbooks that you open in a newish version of Excel. Do not worry! The sign is just telling Excel "if this function returns multiple values, just use the first one". That is fine. You can also safely REMOVE the @ sign for Business Functions.

As you know, Business Functions has used Excels traditional array function methodology for many years. This had a slightly cumbersome "Ctrl-Enter" method of inputting and editing. THIS STILL WORKS FINE. The Business Functions method works great because it is still the native Excel method and it is quite robust because it determines (it doesn't guess) whether its an array function or a normal (single cell) function. It KNOWS if the function call is coming from multiple cells, and it calls a completely different function that returns the multiple values.

The new Microsoft method may take some time to adjust to, personally I am nervous. It is designed to be very user friendly but doesn't feel all that rigorous to me. A single cell function call that can spill sideways and downwards? It feels a bit like cheating! Anyway, if it stands the test of time it may mean that Business Functions array functions are adapted. But for now, the Business Functions way is tried and tested and robust. It has the by product that its hard to mess up an array formula once its programmed in, which I quite like.

Anyway, the takeaway is that the @ sign is to cope with a change Microsoft made to formula behaviour, and if mysteriously appears, it means Excel was in 2 minds what your formula does and is playing safe, and it most cases you can just remove it.

Version 1.63

Maintenance Release. After taking the Regular Expression functions out in version 1.62, we actually put them back in this release because they have recently been incorporated into the basic C++ library. So I upgraded to latest version of C++ (20) and completed the removal of Boost library dependencies that didn't get sorted out in 1.62. Also, the help file has a listing of the source code in C++ for the curious. It won't explain everything but may help in understanding.

New Version And Now Free

We're proud to release version 1.62 after a lull of 5 years!

We're also excited to say BF is now FREE for now and the foreseeable future (it never actually did pay for that yacht!), so no more License Codes or nag screens.

There have been no feature changes except we retired the Regular Expression section, it was too much hassle to maintain with the Boost Libraries and syntax changes. There are no plans to remove any other features.

But it has been recompiled with the latest compiler, tidied up in places, and tested against all the example files.

There are 3 "versions" you can download. The LITE version is the old free version, that has a fairly minimal menu and of course the library of functions. The MODEL BUILDER version is the old paid version which has a few extra utilities and tools to make business forecasting hopefully easier. The LIBRARY XLL only version is the ultra lightweight package that just has the barebones library with no menu or help file.

If I was you I would probably download the Model Builder version, thats what I use every day.

If you have a problem and need to go back to a previous version, version 1.61 is still downloadable, as indeed are all the previous versions.

Excel 2016

Business Functions works with Excel 2016 for Windows. BF does not work with Excel for Mac or Android because these platforms have never fully supported add-ins.

There is a new function in Excel 2016 called Switch which clashes with the BF of the same name! The argument lists are nearly compatibly but not quite. BF has the default value at the beginning whereas the Excel function puts it at the end. Therefore you have 2 options:
1. Convert your models to MS's switch function simply by rearranging the arguments.
2. As soon as we have renamed the BF function BFSwitch, you can simply edit your switch formulae with the new function name.

Residual Cash and Debt Functions

The residual cash functions in BF are really useful but they are a bit trickier to use.

There is a very reasonable reason for this in that they nearly always exist as 3 lines that work together - interest, principal and balance.

However, they are really useful, and the more advanced "array function" versions do things almost certainly faster and cleverer than you could yourself.

So I made this little chart to help you through choosing which of the basic Residual Cash functions fits your needs. It helped me anyway!

(Click it to enlarge)