61–70 of 78 posts
Earlier quoted context omitted.
I work for NASA JPL... I think most people would be amazed if they knew how much “rocket science” was done in Excel.
Given the problems with statistical functions in Excel, perhaps we should be a tad worried?
Excel w/o vba is the ultimate in functional programming.
If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure formulas. Define a function in a cell like `=($s) => [expr]` (or something idc). Call it with `=A1("string arg", B2:B100)` or by name if you name it via named ranges. Functions are obviously a new data type, so you can pass them as parameters to each other or…
https://support.office.com/en-us/article/create-power-query-...
https://msdn.microsoft.com/en-us/query-bi/m/understanding-po...
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-re...
Earlier quoted context omitted.
I’ve been solving this problem with PowerShell for the last year or so. It’s usually available on locked-down corporate Windows boxes, and you can use the entire .NET Framework, as well as third-party .NET libraries, without admin rights. I’m using it with System.Data.SQLite to build a WPF application at the moment, which will hopefully replace a whole bunch of messy and hard-to-maintain Excel sheets and Access datab…
I love PowerShell for this reason. I had a 100 CSV files, and I needed the first two rows removed to put them into a signal viewing app, and it took only a few lines of PowerShell to do this on my corporate machine. I would normally do this in another PL, but strangely PowerShell's solution further pushed me to look into it for other uses on that machine. I wish there were a good book for this, since I actually prefe…
Earlier quoted context omitted.
If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure formulas. Define a function in a cell like `=($s) => [expr]` (or something idc). Call it with `=A1("string arg", B2:B100)` or by name if you name it via named ranges. Functions are obviously a new data type, so you can pass them as parameters to each other or…
I think I disagree entirely. The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. Functions add in questions of scoping. H…
You should look into array formulas (or block formulas). They break out of the one-at-a-time mold and could be much more powerful.
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 hav…
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 mone…
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.
Earlier quoted context omitted.
Could you explain what the dangerous features are?
Dangerous is mostly the spaghetti business logic that results when excel spreadsheets get scaled up into systems . “Dangerous” in lighter ways: https://m.youtube.com/watch?v=-gYb5GUs0dM Nostalgia requires this... https://www.smore.com/clippy-js
From that link: “Spreadsheet errors are costing businesses billions of pounds, according to a financial modeling company, which is calling for the introduction of industry-wide standards to reduce the risk of mistakes.
F1F9 estimated that 88 percent of all spreadsheets have errors in them, while 50 percent of spreadsheets used by large companies have material defects. The company said the mistakes are not just costly in terms of time and money - but also lead to damaged reputations, lost jobs and disrupted careers.”
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 hav…
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.
Earlier quoted context omitted.
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 mone…
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…
Earlier quoted context omitted.
If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure formulas. Define a function in a cell like `=($s) => [expr]` (or something idc). Call it with `=A1("string arg", B2:B100)` or by name if you name it via named ranges. Functions are obviously a new data type, so you can pass them as parameters to each other or…
I think I disagree entirely. The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. Functions add in questions of scoping. H…