9 ms·

Also, the sort of formula shown in that article makes my skin crawl. Not because it's hard – it's mostly a set of sums based on conditional lookups with roundin

Also, the sort of formula shown in that article makes my skin crawl. Not because it's hard – it's mostly a set of sums based on conditional lookups with rounding – but because by the time you've written something like that, Excel is almost certainly the wrong paradigm for your data. It's a terrible IDE; making minor changes to that...thing will half as happily pop an error message and junk it as it will do the right thing. Similarly, you can't exactly git excel formulae alone, and the mixing of data and analysis inevitably causes chaos at some point – just look at all of the examples of financial disasters caused by it. I carte-blanch refuse to touch anybody's attempts at doing stats with it (in a medical science context), unless it also comes with R, sas or stata script.. What the article's really trying to say is "Being good at Excel implies you have a logical mind and the ability to see a way through problems. This is something that others lack".

2rsf 4y ago
I fully understand, but what would you recommended to an accountant? a business analyst without technical aptitude? a business owner, again with zero programming aptitude? Excel is intuitive to begin with, it gets ugly fast and almost impossible to debug but for small sets of data it is very easy to explain how to use it and you get results fast.
- golergka 4y ago
  They could just use Visual Basic right in Excel instead.
  - TheLoafOfBread 4y ago
    Or they could install Visual Studio and start writing .NET applications. Common, regular users often does not even know that Excel can do formulas and are filling single cells with calculator manually.
    - pjmlp 4y ago
      Actually when I went back to desktop Windows development for a while on life sciences, that is exactly what many researchers did after they outgrew VBA, asking IT to get VB.NET on their desktops. No R, no Python, VB.NET it was.
  - deleted 4y ago
    [deleted]
- constantcrying 4y ago
  Excel and spreadsheet software in general is great for certain things. It just scales horribly, it is inherently opaque and naturally hides the relations between its parts. People actually use it as a general programming language, which leads to inherently unmaintainable and hard to understand software.
- RalfWausE 4y ago
  Seriously: APL! Yeah, it takes a bit of time to become really comfortable, but our accountant was (after the initial take-it-away! phase) really glad i have her introduced to it
  - capableweb 4y ago
    I'd hesitate recommend a language whose symbols are not easily accessible or understandable. Try showing the following snippet to even a seasoned programmer and they'll struggle to understand what it does: Avg←{(+⌿⍵)÷≢⍵} Also, if you want a non-programmer to quickly be able to learn a language, the more learning resources they have available to them, the more probable it is that they'll be able to pick it up. APL probably don't have as many modern, free and online resources as other languages.
    - jxy 4y ago
      Yet you cherry picked an extension that's not in the ISO standard and only modern APL supports.
      - capableweb 4y ago
        Sorry about that, I don't APL well myself to know what parts belong to what. I simply searched for "APL hello world" and landed on https://tryapl.org/ https://tryapl.org/ which has that snippet on the left side. The "Examples" section on the Wikipedia page for APL also contains a bunch of symbols I would have no idea how to enter on a computer: https://en.wikipedia.org/wiki/APL_(programming_language)#Examples https://en.wikipedia.org/wiki/APL_(programming_language)#Exa...
        jxy 4y ago
        The whole point is not whether you, without any learning, would have any idea how to read, speak, think in any specific language. You likely have forgot how long you had to learn English. Do you know how many people in the world have no idea what you had written here, yet they are still very comfortable in using a computer? It's easy for an English speaking person to consider programming languages in English as the only kind that's usable. To be fair, English dominates in the tech world. But it would be narrow minded to dismiss a programming language solely based on its non-English symbols. Mathematicians have long been using symbols for efficiency in its expressiveness. Why not programming languages? I'd like to keep an open mind.
HyperSane 4y ago
Python + SQLite should replace a huge amount of Excel uses.
- lofatdairy 4y ago
  I personally vote R + Tidyverse only because I know a ton of older people in data/stats background who have done work in SAS and R, but struggle a bit with knowing what to do in Python. If all you're doing is tabular data all day, R handles that pretty well by default and RStudio is a much better IDE than even a Jupyter notebook for a more interactive paradigm.
  - oogway8020 4y ago
    Tidyverse is awesome, RStudio too
- eddieroger 4y ago
  That's really dismissive of the learning curve of learning two things versus one, as well as having the expertise to install Python and maybe SQLite versus Office being installed on most computers already. Python and SQLite also have no portability as compared to emailing someone an Excel sheet. I love writing SQL whenever I have the chance, but "should" is a really strong word here.
  - D13Fd 4y ago
    It's not just learning two things vs. one. It's learning a text-, command-, and script-based interface vs. a mostly visual one. You could likewise say "a huge amount of file operations should be performed with rsync" and just ignore the fact that it's much harder to learn than a drag-and-drop setup.
- CJefferson 4y ago
  I would love that to be true, but I'll be honest -- I consider myself a good programmer, but a couple of times I've tried Python + SQLite and ended up just giving up and using csv or JSON. Is there a nice library which makes it easy to use SQLite in Python even close to as easy as Excel? Or even as easy as Python lists? Anything which requires me to fill my Python code with SQL strings is (in my opinion) disqualified.
dd36 4y ago
It really needs a debug formula feature that allows you to look at a formula in a more readable format.
cultofmetatron 4y ago
prolly a bit outside the mainstream but -> https://gtoolkit.com/ https://gtoolkit.com/
- ricardonunez 4y ago
  I took a quick look and it reminds me Observable.
kqr 4y ago
Though I have to reach the heights of that formula, I keep coming back to Excel for the same reason that I come back to Perl. Not because it's always the best tool for the job, but because it's the lingua franca in many contexts. If I create the thing in Excel, then I know I can hand it off to someone else and they will both know how to run it, and, in a pinch, how to change it. With any other tool, there would eventually be an onboarding process not just for the thing I built, but also for the tool I built it in. Not with Excel – that will just run, whereever you go.
- Scarbutt 4y ago
  Where is Perl the lingua franca?
  - masklinn 4y ago
    The nightmare corpse-city of R'lyeh, deep under the waves, far from the glare of the yellow sun.
    - thesuperbigfrog 4y ago
      >> The nightmare corpse-city of R'lyeh, deep under the waves, far from the glare of the yellow sun. No. That is not Perl. That is C Recursion: https://www.bobhobbs.com/files/kr_lovecraft.html https://www.bobhobbs.com/files/kr_lovecraft.html
  - hollosi 4y ago
    Perl is preinstalled on pretty much every Linux and MacOS since time immemorial, and it's pretty much guaranteed to stay preinstalled for the foreseeable future. Python3 is up and coming, and eventually it will get there, but old machines must die before it can reach ubiquity.
    - masklinn 4y ago
      > Perl is preinstalled on pretty much every Linux and MacOS since time immemorial awk, bc, and sh are POSIX base utilities, they are literally preinstalled on every unix derivative. That doesn't make them linga franca, which is what Scarbutt was asking about. Hell, ed is a POSIX base utility. Does that make ed's command mode the linga franca of text manipulation? Of course not, the thought is preposterous.
giaour 4y ago
The way Excel lays out data and intermediate states visually is pretty intuitive (compared to, say, using a step-through debugger), but the formula editing pane has an atrocious UX. I would love to see Excel but with a scratch/snap-like interface for authoring formulas.
- nerdponx 4y ago
  The more I use Excel, the more I feel that its primary limitation is its UX for working with formulas.
  - willhslade 4y ago
    Check out the new LET function.
    - rbonvall 4y ago
      LET is a great syntactic improvement, but the UI is still the same. I'd love if the formula box supported the basic things one expects from a code editor, like syntax highlighting, formatting, indentation, comments, etc.
- criddell 4y ago
  The formula editor is pretty bad. If Microsoft fixed it and added GitHub integration easy enough for non-programmers, I think a lot of concerns about usage of Excel could be dealt with.
- Xeamek 4y ago
  This is actually great idea. If microsoft can add block-building programming for minecraft edu edition, why wont they do that to their flagship tool that would also help them in their competition against Google sheets. Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'. ...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff
sicp-enjoyer 4y ago
Everything else in their business ecosystem is in Excel, and that's where people are trained. It's maybe less convenient to make it work in that environment, but the fact that they can, despite those constraints is very valuable.
habibur 4y ago
We could write VisualBasic script for excel. Write this whole formula in a function, indenting and line breaking as is common, and then call that function with parameters from that excel sheet. Not sure if the current web/JS based excel still supports those things.
CorrectHorseBat 4y ago
If that makes your skin crawl, how would you feel like if I told you it's very likely that you a have a chip made with Excel in your pocket?
- thfuran 4y ago
  I would tell you that I don't even know what it means for a chip to be made with excel and ask you to leave before you ruin that.
  - dahart 4y ago
    Can confirm parent’s comment; some hardware design teams run on Excel, for a variety of core planning activities. They aren’t drawing traces in Excel, but they are designing pipeline scheduling and doing Tetris to fit all the reusable different compute units together, among other things.
spywaregorilla 4y ago
At a glance it just looks amatuerish too. You can see they're constantly checking the cell D2 and switching on whether it falls under a value 1-5 or so. This should be 5 cells calculating an output uniquely for each possible value of D2. Then picking the appropriate one later on.
BitwiseFool 4y ago
Excel is not an IDE, but I find it to be the most accessible thing for the average office worker who needs to manipulate data. Not optimal, mind you, but in terms of someone being able to jump in and start working with data it is unmatched.
- driscoll42 4y ago
  Don't forget just about everyone has it installed. Want to send a report to your boss? Email it. Build out something in an ID using .NET or something else? Well if they want to make changes hope they have it installed and are familiar with the syntx.
4oo4 4y ago
This 100%. My hell for a couple years was being an Excel forms "developer" as part of my job duties, and maintaining an extremely complex Excel spreadsheet (that should have been a web application) that I inherited from my predecessor. I wish I had taken screenshots but I think some of the most complex formulas in it had about a dozen levels of branching in them, and to make it worse it was used for a business critical process related to documentation requirements for consumer mortgage loans. The formula shown in the screenshot is representative, though to make it more manageable you can hide and label intermediate calculations in hidden columns and sheets. It looks like they may have done some of that based on the cell references, but they could still offload a lot more of that to different places. There were days I spent several hours clicking "Evaluate Formula" button close to 100 times in a row to debug things like this. The syntax colorization definitely helps, but I would have killed for just the ability to pretty print a formula or set a breakpoint. I'm always shocked it doesn't have features like this that programmers depend on, but I wonder if part of that is because the people that would write something this complex in Excel have never experienced all the tools you get with "real" programming languages. At home I was getting deeper into Linux and bash/python, and that made me resent Excel even more. I finally moved into the IT department and got really into PowerShell and training myself as a sysadmin, but I would still get requests about that damn spreadsheet for at least a year or two until they finally stopped asking. I used to enjoy problem solving challenges with Excel for a while, but in the end it became a parasitic relationship because of the time needed to fix issues and the complete lack of ownership and knowledge within the department that supposedly owned the form and process. Critical decisions are made based on Excel formulas like these, (people getting fired, financial decisions, etc.), maintained by people that barely understand programming. Yes, it's nice that it's helped democratize automation and coding for non-technical people, but Excel just wasn't designed for things this complex. When your Excel formulas and macros start to look like this, you need better tools to manage the complexity and trust the maintenance to tech people instead of business people that don't fully grasp the monsters they're creating when they just keep using the only tool they know.
- HPsquared 4y ago
  There are some tools available to pretty print Excel formulas, e.g. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/
la64710 4y ago
Microsoft excels in encouraging their users to be dumb and dumber. Just out of the kindness of their hearts.
- la64710 4y ago
  Not sure why it got downvoted. It’s not necessarily a negative comment. Their business runs on their customers being dumber than yesterday.
counttheforks 4y ago
I feel like Excel is almost there. If only you could save a sheet as a folder, where each formula was its own file and Excel would hot reload the changes made to those files. Then you might even be able to track the formulas in git.
- pbhjpbhj 4y ago
  I'd rather a "dual view" sheet, where each cell may shows the formula in the top half and the result in the bottom half. Save the CSV of the formulae & data view to your versioning system. I guess you could have a third sub-cell for style?
- HPsquared 4y ago
  Microsoft actually does offer a diff tool in some versions of Office, "Spreadsheet Compare", you can filter to look at e.g. formula changes (or any other area of differences between two Excel files). Annoying it's not offered in most versions.
HPsquared 4y ago
A bit of indentation would help a lot. I tend to do this by hand, but there are automatic tools also, e.g. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/
Tagbert 4y ago
Yes, that formula did give me the heebie-geebies. When I need to do a complex calculation like that in Excel, I break it up into multiple cells each doing a subset of the calculation, then I have a result cell that brings the results together at the end. that way I can work on manageable formulas and can check the results of each. It’s probably less efficient from a processing and memory standpoint but I rarely work on large data sets and the gains in reliability are well worth it.
joeman1000 4y ago
The biggest issue is just how badly you can fuck something up by propagating a bad formula, then changing it somewhere and not updating what was propagated. It obscures the most important thing: the logic behind the calculations. Who in gods name wants to see just columns and columns of data? For what reason? I really want excel/spreadsheets to die a horrible death. They’ve ruined engineering as far as I’m concerned. Try to do an engineering degree without 12 different people forcing you to wade through their hare-brained idiosyncratic fuckfest of an excel file when what they want is probably two lines in python. I want to smack people who can’t just into dataframes. For gods sake.

2rsf 4y ago

I fully understand, but what would you recommended to an accountant? a business analyst without technical aptitude? a business owner, again with zero programming aptitude? Excel is intuitive to begin with, it gets ugly fast and almost impossible to debug but for small sets of data it is very easy to explain how to use it and you get results fast.

golergka 4y ago
They could just use Visual Basic right in Excel instead.
- TheLoafOfBread 4y ago
  Or they could install Visual Studio and start writing .NET applications. Common, regular users often does not even know that Excel can do formulas and are filling single cells with calculator manually.
  - pjmlp 4y ago
    Actually when I went back to desktop Windows development for a while on life sciences, that is exactly what many researchers did after they outgrew VBA, asking IT to get VB.NET on their desktops. No R, no Python, VB.NET it was.
- deleted 4y ago
  [deleted]
constantcrying 4y ago
Excel and spreadsheet software in general is great for certain things. It just scales horribly, it is inherently opaque and naturally hides the relations between its parts. People actually use it as a general programming language, which leads to inherently unmaintainable and hard to understand software.
RalfWausE 4y ago
Seriously: APL! Yeah, it takes a bit of time to become really comfortable, but our accountant was (after the initial take-it-away! phase) really glad i have her introduced to it
- capableweb 4y ago
  I'd hesitate recommend a language whose symbols are not easily accessible or understandable. Try showing the following snippet to even a seasoned programmer and they'll struggle to understand what it does: Avg←{(+⌿⍵)÷≢⍵} Also, if you want a non-programmer to quickly be able to learn a language, the more learning resources they have available to them, the more probable it is that they'll be able to pick it up. APL probably don't have as many modern, free and online resources as other languages.
  - jxy 4y ago
    Yet you cherry picked an extension that's not in the ISO standard and only modern APL supports.
    - capableweb 4y ago
      Sorry about that, I don't APL well myself to know what parts belong to what. I simply searched for "APL hello world" and landed on https://tryapl.org/ https://tryapl.org/ which has that snippet on the left side. The "Examples" section on the Wikipedia page for APL also contains a bunch of symbols I would have no idea how to enter on a computer: https://en.wikipedia.org/wiki/APL_(programming_language)#Examples https://en.wikipedia.org/wiki/APL_(programming_language)#Exa...
      - jxy 4y ago
        The whole point is not whether you, without any learning, would have any idea how to read, speak, think in any specific language. You likely have forgot how long you had to learn English. Do you know how many people in the world have no idea what you had written here, yet they are still very comfortable in using a computer? It's easy for an English speaking person to consider programming languages in English as the only kind that's usable. To be fair, English dominates in the tech world. But it would be narrow minded to dismiss a programming language solely based on its non-English symbols. Mathematicians have long been using symbols for efficiency in its expressiveness. Why not programming languages? I'd like to keep an open mind.

HyperSane 4y ago

Python + SQLite should replace a huge amount of Excel uses.

lofatdairy 4y ago
I personally vote R + Tidyverse only because I know a ton of older people in data/stats background who have done work in SAS and R, but struggle a bit with knowing what to do in Python. If all you're doing is tabular data all day, R handles that pretty well by default and RStudio is a much better IDE than even a Jupyter notebook for a more interactive paradigm.
- oogway8020 4y ago
  Tidyverse is awesome, RStudio too
eddieroger 4y ago
That's really dismissive of the learning curve of learning two things versus one, as well as having the expertise to install Python and maybe SQLite versus Office being installed on most computers already. Python and SQLite also have no portability as compared to emailing someone an Excel sheet. I love writing SQL whenever I have the chance, but "should" is a really strong word here.
- D13Fd 4y ago
  It's not just learning two things vs. one. It's learning a text-, command-, and script-based interface vs. a mostly visual one. You could likewise say "a huge amount of file operations should be performed with rsync" and just ignore the fact that it's much harder to learn than a drag-and-drop setup.
CJefferson 4y ago
I would love that to be true, but I'll be honest -- I consider myself a good programmer, but a couple of times I've tried Python + SQLite and ended up just giving up and using csv or JSON. Is there a nice library which makes it easy to use SQLite in Python even close to as easy as Excel? Or even as easy as Python lists? Anything which requires me to fill my Python code with SQL strings is (in my opinion) disqualified.

dd36 4y ago

It really needs a debug formula feature that allows you to look at a formula in a more readable format.

cultofmetatron 4y ago

prolly a bit outside the mainstream but -> https://gtoolkit.com/ https://gtoolkit.com/

ricardonunez 4y ago
I took a quick look and it reminds me Observable.

kqr 4y ago

Though I have to reach the heights of that formula, I keep coming back to Excel for the same reason that I come back to Perl. Not because it's always the best tool for the job, but because it's the lingua franca in many contexts. If I create the thing in Excel, then I know I can hand it off to someone else and they will both know how to run it, and, in a pinch, how to change it. With any other tool, there would eventually be an onboarding process not just for the thing I built, but also for the tool I built it in. Not with Excel – that will just run, whereever you go.

Scarbutt 4y ago
Where is Perl the lingua franca?
- masklinn 4y ago
  The nightmare corpse-city of R'lyeh, deep under the waves, far from the glare of the yellow sun.
  - thesuperbigfrog 4y ago
    >> The nightmare corpse-city of R'lyeh, deep under the waves, far from the glare of the yellow sun. No. That is not Perl. That is C Recursion: https://www.bobhobbs.com/files/kr_lovecraft.html https://www.bobhobbs.com/files/kr_lovecraft.html
- hollosi 4y ago
  Perl is preinstalled on pretty much every Linux and MacOS since time immemorial, and it's pretty much guaranteed to stay preinstalled for the foreseeable future. Python3 is up and coming, and eventually it will get there, but old machines must die before it can reach ubiquity.
  - masklinn 4y ago
    > Perl is preinstalled on pretty much every Linux and MacOS since time immemorial awk, bc, and sh are POSIX base utilities, they are literally preinstalled on every unix derivative. That doesn't make them linga franca, which is what Scarbutt was asking about. Hell, ed is a POSIX base utility. Does that make ed's command mode the linga franca of text manipulation? Of course not, the thought is preposterous.

giaour 4y ago

The way Excel lays out data and intermediate states visually is pretty intuitive (compared to, say, using a step-through debugger), but the formula editing pane has an atrocious UX. I would love to see Excel but with a scratch/snap-like interface for authoring formulas.

nerdponx 4y ago
The more I use Excel, the more I feel that its primary limitation is its UX for working with formulas.
- willhslade 4y ago
  Check out the new LET function.
  - rbonvall 4y ago
    LET is a great syntactic improvement, but the UI is still the same. I'd love if the formula box supported the basic things one expects from a code editor, like syntax highlighting, formatting, indentation, comments, etc.
criddell 4y ago
The formula editor is pretty bad. If Microsoft fixed it and added GitHub integration easy enough for non-programmers, I think a lot of concerns about usage of Excel could be dealt with.
Xeamek 4y ago
This is actually great idea. If microsoft can add block-building programming for minecraft edu edition, why wont they do that to their flagship tool that would also help them in their competition against Google sheets. Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'. ...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff

sicp-enjoyer 4y ago

Everything else in their business ecosystem is in Excel, and that's where people are trained. It's maybe less convenient to make it work in that environment, but the fact that they can, despite those constraints is very valuable.

habibur 4y ago

We could write VisualBasic script for excel. Write this whole formula in a function, indenting and line breaking as is common, and then call that function with parameters from that excel sheet. Not sure if the current web/JS based excel still supports those things.

CorrectHorseBat 4y ago

If that makes your skin crawl, how would you feel like if I told you it's very likely that you a have a chip made with Excel in your pocket?

thfuran 4y ago
I would tell you that I don't even know what it means for a chip to be made with excel and ask you to leave before you ruin that.
- dahart 4y ago
  Can confirm parent’s comment; some hardware design teams run on Excel, for a variety of core planning activities. They aren’t drawing traces in Excel, but they are designing pipeline scheduling and doing Tetris to fit all the reusable different compute units together, among other things.

spywaregorilla 4y ago

At a glance it just looks amatuerish too. You can see they're constantly checking the cell D2 and switching on whether it falls under a value 1-5 or so. This should be 5 cells calculating an output uniquely for each possible value of D2. Then picking the appropriate one later on.

BitwiseFool 4y ago

Excel is not an IDE, but I find it to be the most accessible thing for the average office worker who needs to manipulate data. Not optimal, mind you, but in terms of someone being able to jump in and start working with data it is unmatched.

driscoll42 4y ago
Don't forget just about everyone has it installed. Want to send a report to your boss? Email it. Build out something in an ID using .NET or something else? Well if they want to make changes hope they have it installed and are familiar with the syntx.

4oo4 4y ago

This 100%. My hell for a couple years was being an Excel forms "developer" as part of my job duties, and maintaining an extremely complex Excel spreadsheet (that should have been a web application) that I inherited from my predecessor. I wish I had taken screenshots but I think some of the most complex formulas in it had about a dozen levels of branching in them, and to make it worse it was used for a business critical process related to documentation requirements for consumer mortgage loans. The formula shown in the screenshot is representative, though to make it more manageable you can hide and label intermediate calculations in hidden columns and sheets. It looks like they may have done some of that based on the cell references, but they could still offload a lot more of that to different places. There were days I spent several hours clicking "Evaluate Formula" button close to 100 times in a row to debug things like this. The syntax colorization definitely helps, but I would have killed for just the ability to pretty print a formula or set a breakpoint. I'm always shocked it doesn't have features like this that programmers depend on, but I wonder if part of that is because the people that would write something this complex in Excel have never experienced all the tools you get with "real" programming languages. At home I was getting deeper into Linux and bash/python, and that made me resent Excel even more. I finally moved into the IT department and got really into PowerShell and training myself as a sysadmin, but I would still get requests about that damn spreadsheet for at least a year or two until they finally stopped asking. I used to enjoy problem solving challenges with Excel for a while, but in the end it became a parasitic relationship because of the time needed to fix issues and the complete lack of ownership and knowledge within the department that supposedly owned the form and process. Critical decisions are made based on Excel formulas like these, (people getting fired, financial decisions, etc.), maintained by people that barely understand programming. Yes, it's nice that it's helped democratize automation and coding for non-technical people, but Excel just wasn't designed for things this complex. When your Excel formulas and macros start to look like this, you need better tools to manage the complexity and trust the maintenance to tech people instead of business people that don't fully grasp the monsters they're creating when they just keep using the only tool they know.

HPsquared 4y ago
There are some tools available to pretty print Excel formulas, e.g. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/

la64710 4y ago

Microsoft excels in encouraging their users to be dumb and dumber. Just out of the kindness of their hearts.

la64710 4y ago
Not sure why it got downvoted. It’s not necessarily a negative comment. Their business runs on their customers being dumber than yesterday.

counttheforks 4y ago

I feel like Excel is almost there. If only you could save a sheet as a folder, where each formula was its own file and Excel would hot reload the changes made to those files. Then you might even be able to track the formulas in git.

pbhjpbhj 4y ago
I'd rather a "dual view" sheet, where each cell may shows the formula in the top half and the result in the bottom half. Save the CSV of the formulae & data view to your versioning system. I guess you could have a third sub-cell for style?
HPsquared 4y ago
Microsoft actually does offer a diff tool in some versions of Office, "Spreadsheet Compare", you can filter to look at e.g. formula changes (or any other area of differences between two Excel files). Annoying it's not offered in most versions.

HPsquared 4y ago

A bit of indentation would help a lot. I tend to do this by hand, but there are automatic tools also, e.g. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/

Tagbert 4y ago

Yes, that formula did give me the heebie-geebies. When I need to do a complex calculation like that in Excel, I break it up into multiple cells each doing a subset of the calculation, then I have a result cell that brings the results together at the end. that way I can work on manageable formulas and can check the results of each. It’s probably less efficient from a processing and memory standpoint but I rarely work on large data sets and the gains in reliability are well worth it.

joeman1000 4y ago

The biggest issue is just how badly you can fuck something up by propagating a bad formula, then changing it somewhere and not updating what was propagated. It obscures the most important thing: the logic behind the calculations. Who in gods name wants to see just columns and columns of data? For what reason? I really want excel/spreadsheets to die a horrible death. They’ve ruined engineering as far as I’m concerned. Try to do an engineering degree without 12 different people forcing you to wade through their hare-brained idiosyncratic fuckfest of an excel file when what they want is probably two lines in python. I want to smack people who can’t just into dataframes. For gods sake.