Questions Array functions - request for feedback

Thanks for sharing this Gabriel. This is an exciting feature indeed. At first glance, it's just 7 new formulas in Excel (SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY, and SINGLE). But, digging deaper, there is even more to it.

In the video that you shared, there is a link to a free 66 page book from Bill Jelen, AKA "Mr Excel", who goes in depth about how great these new Dynamic Array formulas are:

https://www.mrexcel.com/download-center/books/2018/ExcelDynamicArraysStraightToThePoint.pdf

First, it's way easier to use than the old array forumlas in Excel which were complicated.

Second, it's a whole lot more. Here is an excerpt from the beginning of Bill's 66 page book with my red highlight:

When Bill Jelen says something like this, I pay attention. Bill is the one who said a long time ago that Power BI (called Power Pivot at the time) was the greatest thing to happen to Excel in 20 years. I paid attention then. And I'm paying attention now with his comment about the calculation engine.

Personally, I think that this is in reality Power BI feeding back into Excel. What has made Power BI so great is that Microsoft tapped into the Excel gridheads who know how to make Excel useful in the real world. Microsoft has made a concerted effort to keep Excel and Power BI joined at the hip moving forward which is an awesome strategy. I think this is why you are seeing Power BI stuff like this feed back into Excel.

If Bill is right that this is just the beginning, then I suspect that we will be able to use Dynamic Arrays on data stored in the Power BI model in Excel in the future. That would be really cool.

But back to your post Gabriel. These must be exciting times for products like Velixo Reports that are strapped to the Excel rocket, hanging on for the ride in the modern Power BI / Excel era. You made a good investment in Excel, one that I expect will pay off for you.

If I understand your post correctly, I think you are asking for useful Velixo Reports formulas that return arrays like the new Dynamic Array formulas do.

You already outlined a few new potential Velixo Reports formulas:
ACCOUNTS
ACCOUNTSBYTYPE
ACCOUNTBYCLASS
ACCOUNTCLASSES
SUBACCOUNTS
BRANCHES
FINANCIALPERIODS
ACCOUNTGROUPS
INVENTORYITEMS
COSTCODES
PROJECTS
PROJECTTASKS

Now, everyone's brain categorizes things differently. So this might not be a good idea for most people, but, personally, my brain wants to see these all sorted together so I know that they all return an array.

Again, not sure what others think here, but my brain would be happy if these all began with the same thing, like "LIST":
LISTACCOUNTS
LISTACCOUNTSBYTYPE
LISTACCOUNTBYCLASS
LISTACCOUNTCLASSES
LISTSUBACCOUNTS
LISTBRANCHES
LISTFINANCIALPERIODS
LISTACCOUNTGROUPS
LISTINVENTORYITEMS
LISTCOSTCODES
LISTPROJECTS
LISTPROJECTTASKS

Here are some use cases in Velixo Reports that I can think of:

1. Using these new Velixo Reports formulas that you proposed in validated drop down boxes like you pointed out in your video. One thing I'm wondering though is if, in your video, you can use =ACCOUNTLIST("Demo") instead of =$A$1# in the Data Validation window. That would prevent you from having to dump the list somewhere in Excel just to use it for data validation. Can you try it?

2. Using the new proposed PROJECTTASKS formula to design a Project Budget to Actual report that automatically grows as new Tasks are added to the Project. Combine it with the new SORT or SORTBY formulas in Excel to sort the results however you want. Or even combine it with the new FILTER formula to do something like only display Tasks that are over budget.

As Bill Jelen points out in Chapter 10 of the 66 page book above, you can use these new formulas (especially SEQUENCE) with regular Excel formulas (and I would assume regular Velixo Reports formulas as well) to turn regular Excel formulas into array formulas. So what about combining formulas to do things like the following.

3. Automatically apply currency formatting (using ACCOUNTS, ACCOUNTSTURNOVER, and DOLLAR).

4. Expand financial periods as columns (using FINANCIALPERIODS and TRANSPOSE). Or just use EOMONTH, DATE, and SEQUENCE as described in the GENERATING A SERIES OF MONTHS section of Bill's book to do this without a Velixo formula as long as you have a regular calendar and not a fiscal calendar.

5. Return the top 3 GL Expense Accounts (using ACCOUNTTURNOVER, LARGE, and SEQUENCE).

6. Use the SUMMING ALL VLOOKUPS technique described in Bill's book to retrieve the total value sitting in the GL Account in a list that you define. It could be a list of any length.

7. Use the APPLYING UP/FLAT/DOWN ICONS BY USING THE SIGN FUNCTION technique described in Bill's book to automatically apply green up or red down arrows based in whether the value went up or down from last month to this month.

My head is exploding with ideas. There is a lot of potential with this. I'll stop here for now and let others chime in.

Hi Tim,

You came up with quite a few use cases and creative ways to use this tool! I tried using the function directly as list validation source, but Excel gives an error saying that this type of reference can't be used as list validation source.

I like the idea of having a consistent naming scheme, and using =LIST*** as prefix for functions is something I'll definitely consider. The thing i'm still not sure and would like feedback on is whether we need distinct functions to get accounts by class/type/every account or if it's intuitive enough to have a single LISTACCOUNTS function with optional parameters (pretty much like what we have today for ACCOUNTTURNOVER and other related functions - when you leave any of the optional parameters blank it means you include everything)

Another partner suggested to add the ability to get account classes by account type, something like =LISTACCOUNTCLASSESBYTYPE("Demo", "I") for every income class. 

Personally, I like the idea of less formulas with more arguments, like you're doing with the existing formulas.