Business Functions Library for Excel

    1. Version 1.66
    2. Version 1.65
    3. Excels Dynamic Array Formula and the Mysterious @ sign
    4. Version 1.63
    5. New Version And Now Free
    1. Library Only Install
    2. Manual Install
    3. What does the install program do?
    4. Now supporting 64 bits!
    1. New Web site virtually completed
    1. Installer error: if you get 'Access denied'
    2. BF and 64 bits
    1. Error Reports
    2. BF and 64 bits

Excels Dynamic Array Formula and the Mysterious @ sign

Wed 23 Mar 22

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.

Business Functions Ltd, London, UK Website Design: Webpure