5 ms·

90% of the world’s software developers don’t realize that they are software developers and their language is Excel formulas.

by zweep 7y ago

samplatt 7y ago
While that's true, a signification fraction of the remaining 10% is software devs cleaning up after the damage done by people who have over-estimated their ability and created a bunch of business solutions in VBA...
- ViPrLa 7y ago
  Those same business solutions have generated more value than costs.
  - enraged_camel 7y ago
    You only say that because the value you mention is usually visible whereas the costs are usually hidden or not obvious. For example, in my experience Excel is really good at providing job security to those who develop complex spreadsheets. But the cost to the organization tends to be enormous as well, because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale.
    - kd5bjo 7y ago
      Any business that runs into that sort of scaling problem is already doing quite well for itself. How many of them would have gotten to that point if they needed to hire a software engineer to make every prototype that eventually led to the final working system?
      - giantDinosaur 7y ago
        I currently work for a business that has done quite well replacing Excel solutions with proper databases. We've literally worked through the collapse of one company (and its rebirth after it was merged with something else) partially due to the sheer impossibility of managing Excel solutions that had grown out of control. Yes, they mainly grew out of control due to management problems, but the way they did so was remarkable in its rapidity and absurdity. Users were being bought machines with 16gb RAM so they could actually track their sales.
        mywacaday 7y ago
        Do you have a workflow or methodology that you use or do you analyse and develop from scratch? What are you replacing the spreadsheets with, desktop to web apps?
        sweeneyrod 7y ago
        16GB of RAM costs about as much as employing a programmer for a day.
        jakehop 7y ago
        You’re missing the point. If your sales tracking alone takes up 16GB of RAM on the client, that isn’t scalable - and potentially a horrible experience to work with. Your point is correct in the single case, but I think we’re discussing beyond that.
        Robotbeat 7y ago
        Excel is a lot more scalable than doing it by hand. I think the point that Excel--even with its poorly scalable performance--has made programming ridiculously accessible in extremely valuable ways is spot on. In fact, it has creates unending market demand for software engineers--customers invent their own apps (in Excel), prove it out in the real world gaining revenue all the way until it slows due to scaling issues, and software engineers just need to rewrite it for them in a scalable environment. It seems pretty optimal to me.
        wangxiaofeng 7y ago
        agreed!
    - goto11 7y ago
      The people developing complex spreadsheets have job security because they are domain experts. They can't be replaced with a generic developer, even if the spreadsheet is converted into custom code.
      - chasd00 7y ago
        plus, when a generic developer writes the custom code it eventually evolves into an endless cycle of implementing Excel features the domain expert is use to having a click or two away.
        clausok 7y ago
        Plus, in my observation, there is often a dismissive attitude towards Excel among the generic developers which blinds them to the risk of this “re-implementing Excel’s features” cycle. I worked on a team that helped business users who had "outgrown excel", i.e., they had hung themselves with the rope Excel provides. Almost always their scaling problems were solved simply by better Excel practices: better management of the calculation mode; setting the RTD throttle interval to 1-2 seconds; replacing Bloomberg's streaming data function (BDP), with the native alternative {=RTD("BLOOMBERG.RTD...)}; optimizing the division of labor between what is done on the sheet with formulas versus with vba\xll code; meta programming, i.e., creating all or part of your calc sheets and formulas with code so that you get the understandability and observability of Excel formulas while avoiding the things that are hard to do with formulas, such as grouping, joining, filtering, looping; making 3rd-party add-ins workbook-specific such that they're not always on (many of these add-ins listen to application-level events like selection_change and on_calculation which can diminish the performance of all open Excel models, even the ones that don't use that add-in).
      - alach11 7y ago
        Yep. I've seen this pattern of "We need to convert this Excel-based process to an application". Then it takes the domain export + business analyst + developer to make any changes to the process. The final product is more polished, but way more expensive and slower to iterate.
    - cosmie 7y ago
      > because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale. Spreadsheets are entirely possible to scale, that just tends to be a skillset in and of itself, and domain experts organically creating a complex spreadsheet likely don't have the background in process engineering and software design principles to do so themselves. Generally speaking, you can usually refactor an unmaintainable and complex spreadsheet to mimic software engineering best practices, all without dropping down to VBA. Leveraging named ranges[1], locked cells[2] and formulas[3], data validation[4], and error handling[5]. Combined with some defensive validation checks, you can generally sort out the complexity issue nicely. Additional enhancements (based on Excel 2010+) can be made using tables and structured references[6], factoring out "data" worksheets into their own workbooks[7] and linking to them from the "user" workbook, adding relational integrity via a data model[8], or scaling data size via PowerQuery[9] (which stores data within the Excel file in a highly compressed, columnar format that transparently gets processed by a local instance of the same VertiPaq engine[10] that powers SQL Server Analysis Services) You can also drop down to VBA or Javascript[11] if you truly want/need to jump out of the rails of the built in options above. Or in more common cases (which leads to the hell-to-maintain spreadsheets that are more common), if you want to bypass all of the nifty built-in functionality above and do something quick-n-dirty. But if you leverage the above capabilities, you can mature a spreadsheet-based solution quite well and have a battle-tested, stable PoC that can be handed off to a software developer for migrating into a more permanent application. [1] https://trumpexcel.com/named-ranges-in-excel/ https://trumpexcel.com/named-ranges-in-excel/ [2] https://support.office.com/en-us/article/lock-cells-to-protect-them-cb7835f6-9c37-4161-bb53-d1c410acaf21 https://support.office.com/en-us/article/lock-cells-to-prote... [3] https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f https://support.office.com/en-us/article/display-or-hide-for... [4] https://support.office.com/en-us/article/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c https://support.office.com/en-us/article/more-on-data-valida... [5] https://www.exceltactics.com/definitive-guide-excel-error-types-error-handling/ https://www.exceltactics.com/definitive-guide-excel-error-ty... [6] https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-us/article/overview-of-excel-t... [7] https://www.microsoftpressstore.com/articles/article.aspx?p=2447199&seqNum=2 https://www.microsoftpressstore.com/articles/article.aspx?p=... [8] https://support.office.com/en-us/article/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b https://support.office.com/en-us/article/create-a-data-model... [9] https://en.wikipedia.org/wiki/Power_Pivot https://en.wikipedia.org/wiki/Power_Pivot [10] https://www.microsoftpressstore.com/articles/article.aspx?p=2449192 https://www.microsoftpressstore.com/articles/article.aspx?p=... [11] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-core-concepts https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
    - airbreather 7y ago
      I am continually telling my engineering manager that just because he can do somethign in Excel it in no way means that he should. He is a smart guy, but refuses to learn even basic Python or R, despite doing some very significant statistical work in an area related to preventing machines harming humans. I just wonder, even if initially perfect, how many spreadsheets have been unknowingly perverted by someone sitting on the mouse, or a pet cat treading the keyboard.
      - alok-g 7y ago
        Indeed, Excel is usually not the right tool. It is very powerful, but organization of the code/logic, correctness of the calculations, and readability/maintainability are all left on the developer. Mistakes are hard to spot. The levels of discipline and meticulousness needed to use the tool well are high. I have at times remarked that typical Microsoft Office installations should exclude Excel! :-) And I am saying all this about Excel even after being a power user myself and the primary author of the OP. :-) Mathcad, Mathematica, etc. could be good alternatives if not Python or R.

90% of the world’s software developers don’t realize that they are software developers and their language is Excel formulas.

by zweep 7y ago

90% of the world’s software developers don’t realize that they are software developers and their language is Excel formulas.

samplatt 7y ago

While that's true, a signification fraction of the remaining 10% is software devs cleaning up after the damage done by people who have over-estimated their ability and created a bunch of business solutions in VBA...

ViPrLa 7y ago
Those same business solutions have generated more value than costs.
- enraged_camel 7y ago
  You only say that because the value you mention is usually visible whereas the costs are usually hidden or not obvious. For example, in my experience Excel is really good at providing job security to those who develop complex spreadsheets. But the cost to the organization tends to be enormous as well, because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale.
  - kd5bjo 7y ago
    Any business that runs into that sort of scaling problem is already doing quite well for itself. How many of them would have gotten to that point if they needed to hire a software engineer to make every prototype that eventually led to the final working system?
    - giantDinosaur 7y ago
      I currently work for a business that has done quite well replacing Excel solutions with proper databases. We've literally worked through the collapse of one company (and its rebirth after it was merged with something else) partially due to the sheer impossibility of managing Excel solutions that had grown out of control. Yes, they mainly grew out of control due to management problems, but the way they did so was remarkable in its rapidity and absurdity. Users were being bought machines with 16gb RAM so they could actually track their sales.
      - mywacaday 7y ago
        Do you have a workflow or methodology that you use or do you analyse and develop from scratch? What are you replacing the spreadsheets with, desktop to web apps?
      - sweeneyrod 7y ago
        16GB of RAM costs about as much as employing a programmer for a day.
        jakehop 7y ago
        You’re missing the point. If your sales tracking alone takes up 16GB of RAM on the client, that isn’t scalable - and potentially a horrible experience to work with. Your point is correct in the single case, but I think we’re discussing beyond that.
        Robotbeat 7y ago
        Excel is a lot more scalable than doing it by hand. I think the point that Excel--even with its poorly scalable performance--has made programming ridiculously accessible in extremely valuable ways is spot on. In fact, it has creates unending market demand for software engineers--customers invent their own apps (in Excel), prove it out in the real world gaining revenue all the way until it slows due to scaling issues, and software engineers just need to rewrite it for them in a scalable environment. It seems pretty optimal to me.
        wangxiaofeng 7y ago
        agreed!
  - goto11 7y ago
    The people developing complex spreadsheets have job security because they are domain experts. They can't be replaced with a generic developer, even if the spreadsheet is converted into custom code.
    - chasd00 7y ago
      plus, when a generic developer writes the custom code it eventually evolves into an endless cycle of implementing Excel features the domain expert is use to having a click or two away.
      - clausok 7y ago
        Plus, in my observation, there is often a dismissive attitude towards Excel among the generic developers which blinds them to the risk of this “re-implementing Excel’s features” cycle. I worked on a team that helped business users who had "outgrown excel", i.e., they had hung themselves with the rope Excel provides. Almost always their scaling problems were solved simply by better Excel practices: better management of the calculation mode; setting the RTD throttle interval to 1-2 seconds; replacing Bloomberg's streaming data function (BDP), with the native alternative {=RTD("BLOOMBERG.RTD...)}; optimizing the division of labor between what is done on the sheet with formulas versus with vba\xll code; meta programming, i.e., creating all or part of your calc sheets and formulas with code so that you get the understandability and observability of Excel formulas while avoiding the things that are hard to do with formulas, such as grouping, joining, filtering, looping; making 3rd-party add-ins workbook-specific such that they're not always on (many of these add-ins listen to application-level events like selection_change and on_calculation which can diminish the performance of all open Excel models, even the ones that don't use that add-in).
    - alach11 7y ago
      Yep. I've seen this pattern of "We need to convert this Excel-based process to an application". Then it takes the domain export + business analyst + developer to make any changes to the process. The final product is more polished, but way more expensive and slower to iterate.
  - cosmie 7y ago
    > because spreadsheets are impossible to scale, which in turn makes the processes that rely on them impossible to scale. Spreadsheets are entirely possible to scale, that just tends to be a skillset in and of itself, and domain experts organically creating a complex spreadsheet likely don't have the background in process engineering and software design principles to do so themselves. Generally speaking, you can usually refactor an unmaintainable and complex spreadsheet to mimic software engineering best practices, all without dropping down to VBA. Leveraging named ranges[1], locked cells[2] and formulas[3], data validation[4], and error handling[5]. Combined with some defensive validation checks, you can generally sort out the complexity issue nicely. Additional enhancements (based on Excel 2010+) can be made using tables and structured references[6], factoring out "data" worksheets into their own workbooks[7] and linking to them from the "user" workbook, adding relational integrity via a data model[8], or scaling data size via PowerQuery[9] (which stores data within the Excel file in a highly compressed, columnar format that transparently gets processed by a local instance of the same VertiPaq engine[10] that powers SQL Server Analysis Services) You can also drop down to VBA or Javascript[11] if you truly want/need to jump out of the rails of the built in options above. Or in more common cases (which leads to the hell-to-maintain spreadsheets that are more common), if you want to bypass all of the nifty built-in functionality above and do something quick-n-dirty. But if you leverage the above capabilities, you can mature a spreadsheet-based solution quite well and have a battle-tested, stable PoC that can be handed off to a software developer for migrating into a more permanent application. [1] https://trumpexcel.com/named-ranges-in-excel/ https://trumpexcel.com/named-ranges-in-excel/ [2] https://support.office.com/en-us/article/lock-cells-to-protect-them-cb7835f6-9c37-4161-bb53-d1c410acaf21 https://support.office.com/en-us/article/lock-cells-to-prote... [3] https://support.office.com/en-us/article/display-or-hide-formulas-f7f5ab4e-bf24-4efc-8fc9-0c1b77a5356f https://support.office.com/en-us/article/display-or-hide-for... [4] https://support.office.com/en-us/article/more-on-data-validation-f38dee73-9900-4ca6-9301-8a5f6e1f0c4c https://support.office.com/en-us/article/more-on-data-valida... [5] https://www.exceltactics.com/definitive-guide-excel-error-types-error-handling/ https://www.exceltactics.com/definitive-guide-excel-error-ty... [6] https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-us/article/overview-of-excel-t... [7] https://www.microsoftpressstore.com/articles/article.aspx?p=2447199&seqNum=2 https://www.microsoftpressstore.com/articles/article.aspx?p=... [8] https://support.office.com/en-us/article/create-a-data-model-in-excel-87e7a54c-87dc-488e-9410-5c75dbcb0f7b https://support.office.com/en-us/article/create-a-data-model... [9] https://en.wikipedia.org/wiki/Power_Pivot https://en.wikipedia.org/wiki/Power_Pivot [10] https://www.microsoftpressstore.com/articles/article.aspx?p=2449192 https://www.microsoftpressstore.com/articles/article.aspx?p=... [11] https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-core-concepts https://docs.microsoft.com/en-us/office/dev/add-ins/excel/ex...
  - airbreather 7y ago
    I am continually telling my engineering manager that just because he can do somethign in Excel it in no way means that he should. He is a smart guy, but refuses to learn even basic Python or R, despite doing some very significant statistical work in an area related to preventing machines harming humans. I just wonder, even if initially perfect, how many spreadsheets have been unknowingly perverted by someone sitting on the mouse, or a pet cat treading the keyboard.
    - alok-g 7y ago
      Indeed, Excel is usually not the right tool. It is very powerful, but organization of the code/logic, correctness of the calculations, and readability/maintainability are all left on the developer. Mistakes are hard to spot. The levels of discipline and meticulousness needed to use the tool well are high. I have at times remarked that typical Microsoft Office installations should exclude Excel! :-) And I am saying all this about Excel even after being a power user myself and the primary author of the OP. :-) Mathcad, Mathematica, etc. could be good alternatives if not Python or R.