3 ms·

Excel has been an interesting tool that has been abused by many different people for many different subject areas, from petro-engineers to asset management to b

Excel has been an interesting tool that has been abused by many different people for many different subject areas, from petro-engineers to asset management to budget forecasting to drug sales forecasting to activity planning and on and on and on. With a judicious use of vba, many of the tasks above, especially those related to forecasting, planning and reporting can be automated down from hours to just seconds. I have seen inordinate amount of hours wasted by people using excel to do all sorts of tasks that were repetitive to the max. A thinking programmer can work with such people to change those tasks from being hugely manual to simply automatic. Mind you, the vba that has been written by non-programmers is usually so bad that you don't want to even try to attempt to change or fix it. It is far better to just start from scratch. It is a tool that can be used in quite novel ways, but it does have its gotchas that will cause interesting failures for businesses that rely on it. But when it is the only tool you have, then you use it any way you can.

sebazzz 8y ago
What we have often seen in the business, is that Excel gives a flexibility and freedom that purpose-built tools often don't have. Want a extra field? Done. Field need to have a date larger then xx-xx-xxxx? Done. Even if the purpose-built tool has some kind of custom field system that accomodates these wishes, there always will be some feature the developers need to build. And that costs more turnaround time (and money) then doing it yourself.
- oldandtired 8y ago
  That is certainly something that I have seen as well. The problem lies in validating the spreadsheets. Often, the amount of validation is minimal, whether that is the actual formulae used or the relationships that bare created or even the data that is used. I have seen some outstanding examples of well written spreadsheets but these tend to be the minority case. Depending on the organisation and the priority it puts on validation, the spreadsheets can range in quality from vry poor to mediocre. I have seen critical spreadsheets that were a disaster of coding. Many times, the IT costs for getting the IT developers to do the work for you is so far above what it costs for you to do it yourself or even to get a developer in specifically to work for you, outside of any controls that your IT group would exert. Plenty of the work I have done in the last 20 years was based on the end-user employing me to do the work taht was not cost effective or even allowed for by the company IT teams.
  - rightbyte 8y ago
    Is validation a problem? You see the intermediate steps of calculation in the columns. It's way more clear then black-box unit testing. It's like some nice live debugging with print statements everywhere.
    - oldandtired 8y ago
      Validation is always a problem. In one early example, I came across, the core data on which the model did not match what was really there. The data was assumed to be correct and, of course, nobody actually did the checking. The previous year's data was used for the following year's calculations. I knew the data for the spreadsheet was wrong only because I had been involved in looking at the actual data sources in weeks previously. But there can also be formulae errors. They seem to produce the correct information, but they will have subtle errors that miss necessary edge cases or use set values when they shouldn't.
rightbyte 8y ago
The problem is that when you involve a Real Programmer you ruin the flexibility of the sheet that made it so useful for the original user. It's better waste hours using Excel than waste even more hours trying to convince some developer to do a change and wait for the product - which might never come.
- oldandtired 8y ago
  I have been the "Real Programmer" that was closely involved with building the sheet models. The flexibility is in being able to build lots of different models for investigation. It is oft found that the model builder misses some of the subtleties of using spreadsheets and their models don't quite go the way they expect. In my case, I was on call to discuss what was to be achieved and to help them build what they needed. The turn around times were short. My function as the "Real Programmer" was to get them into a position of solving any relevant problems and making sure that they were able to progress with their work. That is the beauty of working directly for the end-user instead of being part of the central IT team.

sebazzz 8y ago

What we have often seen in the business, is that Excel gives a flexibility and freedom that purpose-built tools often don't have. Want a extra field? Done. Field need to have a date larger then xx-xx-xxxx? Done. Even if the purpose-built tool has some kind of custom field system that accomodates these wishes, there always will be some feature the developers need to build. And that costs more turnaround time (and money) then doing it yourself.

oldandtired 8y ago
That is certainly something that I have seen as well. The problem lies in validating the spreadsheets. Often, the amount of validation is minimal, whether that is the actual formulae used or the relationships that bare created or even the data that is used. I have seen some outstanding examples of well written spreadsheets but these tend to be the minority case. Depending on the organisation and the priority it puts on validation, the spreadsheets can range in quality from vry poor to mediocre. I have seen critical spreadsheets that were a disaster of coding. Many times, the IT costs for getting the IT developers to do the work for you is so far above what it costs for you to do it yourself or even to get a developer in specifically to work for you, outside of any controls that your IT group would exert. Plenty of the work I have done in the last 20 years was based on the end-user employing me to do the work taht was not cost effective or even allowed for by the company IT teams.
- rightbyte 8y ago
  Is validation a problem? You see the intermediate steps of calculation in the columns. It's way more clear then black-box unit testing. It's like some nice live debugging with print statements everywhere.
  - oldandtired 8y ago
    Validation is always a problem. In one early example, I came across, the core data on which the model did not match what was really there. The data was assumed to be correct and, of course, nobody actually did the checking. The previous year's data was used for the following year's calculations. I knew the data for the spreadsheet was wrong only because I had been involved in looking at the actual data sources in weeks previously. But there can also be formulae errors. They seem to produce the correct information, but they will have subtle errors that miss necessary edge cases or use set values when they shouldn't.

rightbyte 8y ago

The problem is that when you involve a Real Programmer you ruin the flexibility of the sheet that made it so useful for the original user. It's better waste hours using Excel than waste even more hours trying to convince some developer to do a change and wait for the product - which might never come.

oldandtired 8y ago
I have been the "Real Programmer" that was closely involved with building the sheet models. The flexibility is in being able to build lots of different models for investigation. It is oft found that the model builder misses some of the subtleties of using spreadsheets and their models don't quite go the way they expect. In my case, I was on call to discuss what was to be achieved and to help them build what they needed. The turn around times were short. My function as the "Real Programmer" was to get them into a position of solving any relevant problems and making sure that they were able to progress with their work. That is the beauty of working directly for the end-user instead of being part of the central IT team.