Had to vent somewhere; spent a few week on and off developing a compensation system in Excel and PowerBI for a company with several divisions. Pretty simple stuff; they just wanted to benchmark and log compensations across different geographies.
In order to populate some dropdowns I used the functions SORT, UNIQUE and FILTER for the first time. Was told all clients use Excel 2019 whereas I have a office 365 subscription. Well it turns out these functions are not available in excel 2019 home and business; only in the Office 365 version. (Even though I checked the version numbers of excel and we were all using the same).
What kind of money grabbing scheme is this… Was a simple workaround by creating the same functionality with a few lines of VBA but even so…
It’s as bad as giving Excel 2021 the LAMBDA function in an update, waiting for people to use it, then removing it in another update so that their new workbooks no longer work.
I still have access to LAMBDA in my install of office 2021. I may not have updated. Now I just won’t.
Wait they really did that… Jesus. I like Microsoft products as a whole but they do make some really shitty decisions. One that got us was their switching of PowerBI pro licences where you went one day from only needing a licence to write a report to needing a licence to view the report as well.
It comes back to bite them though as we are in the process of choosing a new ERP solution and Dynamics is probably off the table as the board keep raising the issue of trust in pricing…
It’s frustrating, I know. But, these functions were developed and added recently in Excel. IIRC, 2019 was already out by then. Newer functions are not backward compatible. Besides, the numbered versions of Microsoft Office are supposed to be a snapshot of a subset of the then Microsoft 365 featureset.
It’s not a defense by any means. Microsoft 365 is a well monetized product and probably a cash cow for them. Their money making practices are not all sound or even ethical all the time.
But for your own quality of use, if you’re going to share with other people, it’s always good to check out the support page of any newer functions to see which versions they’re available in. Here’s the one for SORT for example: https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c. It’s made clear that the function is not available in versions older than Excel 2021.
Many thanks for taking the time to respond and providing the links. To be honest this is the first time I have come across this issue. I knew there were differences between like likes of say excel 2013 vs 2016 vs 2019 but not home business vs office 365. Especially when we checked the excel version numbers and they were the same.
Lesson learnt. :-)
