A series of excel errors, including failure to copy down a formula to the whole column, led many governments to adopt a policy of economic austerity. The spreadsheet had demonstrated that, historically, countries that adopted austerity came out of recession faster. Once the errors in the spread where fixed, it actually proved the opposite. But by then the damage was done.
Edit: It was UMASS grad students that spotted the spreadsheet errors by these Harvard/IMF heavy weights:)
Reinhart and Rogoff kindly provided us with the working spreadsheet from the RR analysis. With the working spreadsheet, we were able to approximate closely the published RR results. While using RR’s working spreadsheet, we identified coding errors, selective exclusion of available data, and unconventional weighting of summary statistics.
In addition to their excel errors, their analysis required excluding the Australia, Canada, and New Zealand. Once these countries were included in their analysis, their argument falls apart. To me, it's clear that this was a case of very selective researcher degrees of freedom to support austerity. Why anyone would take Reinhart or Rogoff seriously after this farce is beyond my comprehension.
> their analysis required excluding the Australia, Canada, and New Zealand. Once these countries were included in their analysis, their argument falls apart
Unsure on Oz and Canada. But New Zealand was excluded due to gaps in R&R's data at time of their first paper's publication. They "fully integrated the New Zealand data back to the early 1800s" as well as for "every major high debt episode for advanced countries since 1800" for their 2012 paper [1].
The effect size that Herndon et al found is "growth at high debt levels" being "a little more than half of the growth rate at the lowest levels." R&R's 2012 paper finds an even more-muted result: "2.4% for high debt versus 3.5% for below 90%."
In case it's useful to anyone: google sheets' lambda[1] and map[2] functions have prevented a ton of fill-down issues for me. Plus the ability to use a whole column without specifying number of rows (e.g. "A1:B" instead of "A1:B1000")
Those functions + a little bit of custom app script have helped me (not very technical) get pretty far in building maintainable data pipelines, reporting, etc. in gsheets.
Two of those 3 sound intentional: selective exclusion of data and unconventional weighting. The "coding errors" may also have been intentional. I would suggest more scrutiny of the authors and their motives before dismissing as "Excel errors, whoops"
I think it's more likely that if that paper had never been published the government would have advertised a different pretext for the decision they wished to make anyway.
True, but the paper reduced opposition. I was surprised by the paper at the time, but given the authors considered it a useful analysis. I was quite relieved when the error was discovered
> The authors show our accidental omission has a fairly marginal effect on the 0-90% buckets in figure 2. However, it leads to a notable change in the average growth rate for the over 90% debt group.
That was for parties to save face after investing in Greece. They just wanted their money back without having to recognize the investments were not good quality, as stated. Luckily, all the stereotypes about the lazy south were handy.
> Germany was and is still fooled by it. That is why Greece was treated the way it was after the Lehman crash in 2008.
With or without that study, Greece would have been treated this way. Saving money is burned so much into German culture that none of the recent German ministers of finance can forget their microeconomic views and adopt macroeconomic views.
This goes way back to the end of the 1920s when the German government decided to save money during the great depression.
Also, in Berlin there was a museum about saving money [1].
I have done excellent work in O365 Excel that is later muddied by concurrent/other editors.
Half the time I'm correcting cells where a user navigated to the wrong one and replaced the formula. What's more frustrating is when I develop a BETTER formula to use for the entire column, but there's no easy way to replace it all and I actually want to preserve previously calculated values. (I preserve values for historical accuracy, even if the formula then was not-great.) Makes me think Excel needs git-like version control for rows/records, and the ability to query things like last-modified of a cell, etc.
I have copied everything in the column out to a temporary spreadsheet then paste-by-value to put it back, once the correct formula is set throughout. That's tedious and error-prone and sometimes I lose the dumb historic coloring that previous editors wanted (I preserve what I can) and log why this changed as the maintainer changed. Am I doing this wrong?
There's no easy way to spot holes/changes in formulas as you scan down the column: You can use the Review tab option to show Formulas, but if the formulas are largely the same (start the same but are very long), you're unlikely to spot the difference. This could be thousands of rows to scroll through, or the last hundred you're concerned with. Seems like there should be a better way.
I want an easy table protection option to require that 'this formula will be the only formula in this column'. Table protection is so lacking. You can't protect a column to say "only computed values exist here". You protect the column, and it prevents users from entering a new row/record, making a mistake, and deleting the row to try again. We train folks: If you mess up the next row, just delete the entire row and attempt to add it back. The computed columns/values will be there for you. Protecting a column makes this impossible.
Online Excel is advancing.. but I want too much. I feel like there's been low-hanging fruit for years and it's no wonder all these alternatives are good enough to replace Office.
IMHO instead of ramming a square peg into a round hole one may think of switching from Excel to i.e. Jupyter notebook(s).
Keep the raw data separate from the calculations/visualizations and get a more trackable environment.
You may also pinch out bunch of functions as separate scripts in a git repo and share these between various notebooks/people.
Calculate md5 checksums of raw data files. If values in rows can be modified, get the hash sums (xxhash?) per row. That way even with the lowest common denominator (text CSVs) you know if the inputs changed and where.
For larger data consider storing it in parquet format and using duckdb.
Assuming that you must have Excel output calculate what's needed using raw data, notebooks/scripts then output Excel using openpyxl
> A series of excel errors, including failure to copy down a formula to the whole column, led many governments to adopt a policy of economic austerity.
I can't help thinking that if a single published paper can lead to a national policy of austerity then the problem is not with the paper ...
Sounds more like an excuse to never reform the country to me. By that logic in bad times you shouldn't do the necessary reforms. And since they aren't done in good times either.
The wikipedia link you cite disagrees that it “proved the opposite”
> Further papers … which were not found to contain similar errors, reached conclusions similar to the initial paper, though with much lower impact on GDP growth.
De facto, austerity tends to be used to argue for cuts to social programs and reduced investment in common goods or to argue for privatization of common goods.
This tends to benefit the top 0.1%: The living situation of the the majority is worsened and their bargaining position for employment is weakened. Privatization leads to private profits that overwhelmingly go to the top 0.1%, and of course there's a direct power aspect to it as well. If you literally own the regional electricity provider, for example, that gives you direct political power that goes beyond the mere profit you can make from this ownership.
Technically, it doesn't have to be that way. For example, austerity could theoretically be used to argue for much higher top-end marginal income taxes and capital gains taxes as well as wealth taxes. But political constellations being what they are, that's usually not how it plays out.
They discovered this during a recount that only happened because someone noticed a different calculation error in the official result [1] ("Hans Peter Doskozil" + "Andreas Babler" should add up to "Gültige Stimmen" (valid votes) but didn't).
The current goverment is very unpopular, but the SPÖ in the opposition has failed to capitalize on that and is currently polling below the governing ÖVP [2]. This incident will not help.
> The current goverment is very unpopular, but the SPÖ in the opposition has failed to capitalize on that and is currently polling below the governing ÖVP [2]. This incident will not help.
That's because the SPÖ has made the same mistake that the German SPD or French and Italian PS did: they went away from their worker roots and aligned themselves with corporatist ideals (i.e. neoliberalism). No surprise that their electorate eroded together with the wealth and life quality of said electorate thanks to wage stagnation.
Babler is an actual social democrat in name and politics. I am pretty sure he will manage to turn around the party's fate - and hopefully, also be an example towards our social democrats, I'm sick of Olaf Scholz and his cuddling with the FDP.
I would argue this is the inevitable result of all political entities. They get captured by the richest parts of the system they're supposed to rule over. This happens to left and right leaning organizations, the only difference is what kinds of undesirable policies result.
This is why a political system that lets parties be born and die naturally is preferable to the US two-party system. We're stuck with a choice between two vampires.
Moreover, the wife of a person near to Doskozil, Michaela Grubesa, was the leader of the election commission, which has its own very strange taste.
Now, never credit maliciousness to what can be credited to incompetence, but you know, it's a lot of small details that make the whole story more bizarre.
Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should.
For example: you can have an Excel file with 100k lines and have a formula accidentally replaced by a static number on line 87456. Nobody will ever find it.
> Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should.
I make this point when chatting with new team members that say "let's just use X," without really understanding how X works, and I use Excel as the analogy. Most people know how to use an equation field. Most people don't consider writing a spreadsheet programming, but it is.
And just like other types of programs, you can have really insidious bugs, and you need to consider how you'll address those, or address changes in the underlying or internal components of the system.
A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example.
Starting in 2003 I spent several years as a principal developer developing mobile office software.
When we launched our excel compatible spreadsheet we diligently formalized every function and wrote tests to verify behavior. It was beautiful.
Then we started receiving lots of bug reports from users because our calculations often didn’t match what they were seeing in Excel. Older Excel versions had lots of bugs and Microsoft had been carrying them forward because at that point it was “better” to carry the errors forward and display results people expected rather than fixing them and having everyone confused by the changed results.
So we copied their behavior as best we could. I wish I had the list still but it was long and full of weird edge cases which caused formulas to be incorrect.
>A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example.
Is this specific to Excel? Floating point math can do that regardless of the platform. Addition on floats isn't associative!
I've built a unit testing framework for Excel using Python and the openpyxl library. I work in a bank and using Excel was unavoidable in some cases. You'd basically build a test suite for a spreadsheet in Python and run it like a regular test suite. It helped a lot in catching issues. Obviously it would be nicer if unit testing was built into Excel, but it is possible to build side tools around it.
Regarding your example, you can protect worksheets and have only some cells unlocked for modification [1].
You can add conditional formatting, VBA procedures, additional formulas etc. to do whatever validation you like. However, you need a software engineer (or someone who thinks like one) to properly implement that, and it’s not exactly fun.
Do people working in finance / government / other places where Excel heavy fields really not have any tooling for "linting"/"typing" (formula replaced by static number in 1 line, integer displayed as date etc) or "unit testing"?
This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
I used to work for a UK government ministry that has a budget in the tens of billions of pounds per year. I can tell you with confidence: no, there really is no linting, typing, automated testing, version control, nor any other tooling that you would consider "table stakes" for software development, when it comes to spreadsheets. Every single policy mechanism of that ministry relies, at some point in the pipeline, on at least one untested spreadsheet full of copypasted formulae, and inscrutable VBA macros passed down through generations. There are quality control reviews, of course, but they're manual. They don't scale. And these spreadsheets are used to make decisions that decide the fates of nearly 70 million people. And that ministry is not exceptional wrt other ministries, i.e. they're all as bad as this.
There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. Nobody had even heard of git. So nothing changed. It was the dark ages, and a major factor in why I quit.
My SO works in accounting, and they really do run without guardrails. And yes there are a ton of different software packages that people can buy for various uses (and do).
The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else.
Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.
> This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
There are ways to deal with this: array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection).
You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming.
Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to.
Other comments are amateur hour.
There are lots of bad programs too, but programming is not so democratic.
Also it is very convenient to blame a mistake on Excel.
Have two different people do the same work, see if they get the same answer. Ideally the two people use different tools, so "likely to make same mistake" is minimized.
Yes, it's possible to do a VBA macro or an external program to validate a sheet.
But there is no built-in tool you can use to say to excel "this column must always be a number, don't ever allow it to be autodetected as a date" or "this column must always have a formula, if the formula doesn't exist or is a static number, alert the user".
You can lock cells, but who bothers doing that in the real world.
If one has to write a program to check if a user did not screw up cut and paste from one sheet/column/whatever it would be IMHO easier to just skip Excel, user and just write a script automating error prone C&P in Excel.
If some user wants to view it, then the script can create a valid Excel file.
Well it depends on your data. I'm an accountant and it is very normal for accountants spreadsheets to be full of 'check digits'. Checks and balances are well ingrained into accounts!
In its basic form you perform the same calculation on the totals as you did on the roots and subtract it from the result. The answer must be zero. I know it is harder on some kinds of data, but it can prevent a huge amount of every day errors.
Makes perfect sense. The testing being discussed is to create platform where generic tests can be coded and rerun. So tests that aren't specific to totals and subtotals
I've had columns filled with a formula and intentionally replaced one cell with a static value. You immediately get a warning on that cell that can be ignored/cleared but takes a handful of clicks to do so. Granted, there's no overall place to view the warnings (that I am aware of) so if you have 100k lines and one cell replaced you can't easily see it without inspecting everything. Is this no longer the case?
There are certainly a lot of good practices such as ensuring any formulas are compatible with "fill down". It'd make for a nice standard, like MISRA C.
If you wanted automatic formula auditing, I'd recommend using R1C1 reference style so formulas using relative references are independent of the cell's location, then use FORMULATEXT function, and an array formula applied over the whole range you want to check.
Will you be the one to teach boomer-age payroll accountants to create SQL tables for their calculations? I can give you a phone number to offer your consulting services :)
Of course a custom website backed by a properly schema'd SQL database would be better, but that takes money, time and someone needs to keep it updated.
People use Excel because it's on everyone's computer by default, they have full permissions to do anything with it without needing IT approval and it's surprisingly powerful when you get into it.
And if a client happens to have a really weird non-standard requirement for how their payroll is done, any accountant can easily create a custom Excel sheet just for them - instead of waiting 6 months for an external consultant company to provide them with a custom tool that does 80% what they need.
These aren’t “technical errors”. These are user errors, made by people who know just enough about Excel to use it, but not to include anything that validates the results - or constraints that prevent intentional or unintentional manipulation of sheets, once considered “done”.
Unfortunately, this is not a problem with the tool, Excel does what it’s supposed to. Any tool in the hands of someone not properly trained to use it, or without the discipline to use it correctly, adds a risk of incorrect output.
I would say that the Citibank loan payment fiasco was mostly due to bad software design - which yes is mostly due to bad management priorities and validation. I don’t think most people look at that and say “that was pure user error, the software is fine”
On the other side of the spectrum there’s pure user error aka pebkac.
Where does this instance sit on the spectrum, I.e. even although it is possible to do a lot of things in excel, it’s often not a good idea to do many of things in it for a variety of deficiencies.
The problem is fundamental to the spreadsheet data model. Spreadsheets use the "big bag of cells" data model, where anything can go anywhere. The people who enjoy spreadsheets like this data model, it is very quick to get something up and running. Those of us who don't like spreadsheets hate it. it encourages the mixing of data and code. it promotes coding via copy/paste and all the opportunities for code/data addressing mismatches that go with it. But for me the thing that drives spreadsheets from mildly annoying to outright dislike is that there is very little done to keep data rows intact, it is very easy for items in a row of data to end up where they shouldn't be. All I really want is row security... So I ended up replacing my spreadsheets with relational databases. the UI sucks in comparison. but at least my rows stay intact.
Even with plain text CSV files one can detect that a given cell was changed, have md5 checksums of files, etc. In a day to day use where users often share their edits using emails just figuring out which is the correct and most recent copy is a fools errand.
In some settings the sheets are just a giant white boards: different parts of the same sheet can contain partially replicated and not cross-checked data.
Samples in columns, so each row is a different type. "Free flow of thought" or annotations of say numerical cell with text in the same cell.
All this steams from the fact that users do not see the data as something to be queried and compared but as a board one can just look at.
Exactly the same line of argumentation people bring forward in defense of C. If only it was used correctly, it would work! But it isn’t and it won’t, and modern alternatives are popular for a reason. It’s a technology problem.
Austrian politicians on all levels rarely admit their mistakes, the same yesterday where the blame was put on technical errors and the lack of a second count (preferably on paper, it's only 600 votes after all) was barely mentioned. Procedures were sloppy and hastened, hence the error wasn't spotted on Saturday.
This isn't the first time spreadsheet errors have messed with election results.
It was a column error in tabulated election results that cause all the conflict in 2020 in Antrim county, Michigan. Any computer folks who took the time to read the report by an University of Michigan computer scientist hired by the Republican chaired bipartisan committee that the Michigan state senate would have seen that there was no vote fraud, but user error that got caught almost immediately in the accountability systems that were in place to catch any errors like that.
If we stopped trying to report election returns in real time like sporting events, and could be patient a few hours, we could avoid a lot of these highly divisive controversies.
> If we stopped trying to report election returns in real time ... we could avoid a lot of these highly divisive controversies.
At least in the United States, election results were reported the same night for decades and there were very few questions surrounding the truth, even in 2000 people agreed on the need for a recount, even if they disagreed on the methodology or the ultimate result.
Only since 2020, all of a sudden results take multiple days and the electorate is supposed to just understand "this is how it is now" and not question anything, despite witnessing major swings in individual states or districts occurring overnight.
> Only since 2020, all of a sudden results take multiple days and the electorate is supposed to just understand "this is how it is now" and not question anything, despite witnessing major swings in individual states or districts occurring overnight.
The main reason the counting took much longer was three-fold: many more votes were cast by mail than was typical, several states were quite close in their vote count (three states within 1%), and a few states were prohibited from taking any preparatory steps to counting mail ballots prior to election day. And this was known--and heavily reported on!--well before the election.
Counting mail-in ballots is intrinsically harder than in-person ballots. Indeed, very rarely is it ever actually completed on election night (not least of which is that in many states, the votes need not be received by election day to be counted). However, usually mail-in ballots aren't enough to decide the results of an election. But when 2/3 of the ballots are via mail, and especially when there was an expected partisan difference between in-person and mail-in ballots, it takes a lot longer to develop a good consensus as to when one of the candidates is highly likely to have won.
Good grief. That's just simply not correct. What happened is that in 2016 and 2020 specifically, the presidential election turned on a handful of states that were (by definition, really) very close. Close results take longer, because if it's not close you can call an election without finishing the count. If the close states are not critical to the result, as is more common, no one cares.
> major swings in individual states or districts occurring overnight
Again, no. This is not a new effect, nor is the fact that different groups of ballots skew differently and are counted at different times.
I know it was rediculous! Biden was leading Texas and then all of the sudden we're supposed to expect a surge of red votes and now Trump carried the state?
But in all seriousness.
1) "Decades" is 5 elections which is a sample size of garbage.
2) The election results haven't been actually reported on the same day for a long time. Absentee ballots aren't countable until after Election day in many states making it trivial to demonstrate that they haven't been counted until the next day meaning you can't have reported the exact results on the first night.
Now, news media have been making predictions on who is going to win the election and they did refrain from doing that. But one big thing to remember is that states where Republicans determined the election process such as Arizona did not even count 100% of their ballots by Nov 12 (9 days after the 3rd, aka Election Day).
I agree things have become far more contentious since 2020. Trump's campaign used that controversy to fund-raise for "legal defense" but the small print in his campaign emails I received also said the funds could be used to pay down his campaign debts.
If you haven't read the Michigan Senate report (result of an 8-month bipartisan investigation chaired by a Trump Republican), please do. It cleared the air and answered tons of questions for me, seeing the evidence they found and realizing that there are simple, understandable reasons explaining what happened and also explaining the ruckus that resulted from so many people crying fraud but being unwilling to take time themselves to investigate the facts of the matter.
The difference, I'd argue, is that generally the loser of an election makes a public statement conceding that they lost. 2000 definitely was an outlier in this regard as well, but I think it still differed in that the disagreement was about counting the votes, not about the integrity of the election itself. Once you have a major candidate who's willing to make a baseless claim of fraud and a "stolen" election, suddenly the process that used to happen behind the scenes quietly in the past to count and verify the result that was announced earlier is put under a microscope and analyzed by people who have no idea how that process actually works, which is...well, almost everyone.
Mind that in this case the vote was not a large nation wide vote like a U.S. primary, but an election at a party delegate convention. Thus 400 or so voters. That should be doable in acceptable time.
My wife is a school administrator and every year I run a little self-written pipeline for her to munge the local authority's XML admissions data into an Xlsx that the staff can use for planning home visits, class allocation etc. Excel (well strictly speaking it's loaded into Google sheets) really is the only game in town for this kind of thing, but even at this very low level there are many fraught footguns necessitating extremely careful handling. Anyone running an election or an economy off this thing needs their head examined.
The R community is fantastic and very helpful for getting started. There is so much peace of mind to be gained by doing everything in R and then using R’s write.xlsx to output the excel sheet for your users.
Python+pandas is an ok alternative. The ergonomics are weird, but if you’re familiar with Python, it works enough.
The tragedy about it is, that they only had to count 600 votes and had a committee of 20 people for doing that. All 20 people could’ve just counted all the votes, without any „technology“ involved.
All the experts said, that usually in such an election the ballots are sorted by candidate, put on stacks of 10s, then combined to stacks of 100s. If they followed this best practice, they would’ve had around 10 stacks, and the result would‘ve been obvious to everyone.
(Not so) funny twist: after the election the ballots were put into unsealed bags and were stored in an office where many people had access. So all recounts that are done now, are completely worthless.
Readit News
Edit: It was UMASS grad students that spotted the spreadsheet errors by these Harvard/IMF heavy weights:)
https://peri.umass.edu/fileadmin/pdf/working_papers/working_...https://en.wikipedia.org/wiki/Growth_in_a_Time_of_Debt
Unsure on Oz and Canada. But New Zealand was excluded due to gaps in R&R's data at time of their first paper's publication. They "fully integrated the New Zealand data back to the early 1800s" as well as for "every major high debt episode for advanced countries since 1800" for their 2012 paper [1].
The effect size that Herndon et al found is "growth at high debt levels" being "a little more than half of the growth rate at the lowest levels." R&R's 2012 paper finds an even more-muted result: "2.4% for high debt versus 3.5% for below 90%."
[1] https://archive.nytimes.com/www.nytimes.com/interactive/2013...
Those functions + a little bit of custom app script have helped me (not very technical) get pretty far in building maintainable data pipelines, reporting, etc. in gsheets.
[1] https://support.google.com/docs/answer/12508718 [2] https://support.google.com/docs/answer/12568985
[0]: https://archive.nytimes.com/www.nytimes.com/interactive/2013...
No, it did not. It simply moderated the effect [1].
[1] https://archive.nytimes.com/www.nytimes.com/interactive/2013...
https://www.bloomberg.com/news/articles/2013-04-18/faq-reinh...
and from the rebuttal:
> The authors show our accidental omission has a fairly marginal effect on the 0-90% buckets in figure 2. However, it leads to a notable change in the average growth rate for the over 90% debt group.
https://www.bloomberg.com/news/articles/2013-04-18/faq-reinh...
Germany was and is still fooled by it. That is why Greece was treated the way it was after the Lehman crash in 2008.
With or without that study, Greece would have been treated this way. Saving money is burned so much into German culture that none of the recent German ministers of finance can forget their microeconomic views and adopt macroeconomic views.
This goes way back to the end of the 1920s when the German government decided to save money during the great depression.
Also, in Berlin there was a museum about saving money [1].
[1]: https://www.dhm.de/en/exhibitions/archive/2018/saving/
Half the time I'm correcting cells where a user navigated to the wrong one and replaced the formula. What's more frustrating is when I develop a BETTER formula to use for the entire column, but there's no easy way to replace it all and I actually want to preserve previously calculated values. (I preserve values for historical accuracy, even if the formula then was not-great.) Makes me think Excel needs git-like version control for rows/records, and the ability to query things like last-modified of a cell, etc.
I have copied everything in the column out to a temporary spreadsheet then paste-by-value to put it back, once the correct formula is set throughout. That's tedious and error-prone and sometimes I lose the dumb historic coloring that previous editors wanted (I preserve what I can) and log why this changed as the maintainer changed. Am I doing this wrong?
There's no easy way to spot holes/changes in formulas as you scan down the column: You can use the Review tab option to show Formulas, but if the formulas are largely the same (start the same but are very long), you're unlikely to spot the difference. This could be thousands of rows to scroll through, or the last hundred you're concerned with. Seems like there should be a better way.
I want an easy table protection option to require that 'this formula will be the only formula in this column'. Table protection is so lacking. You can't protect a column to say "only computed values exist here". You protect the column, and it prevents users from entering a new row/record, making a mistake, and deleting the row to try again. We train folks: If you mess up the next row, just delete the entire row and attempt to add it back. The computed columns/values will be there for you. Protecting a column makes this impossible.
Online Excel is advancing.. but I want too much. I feel like there's been low-hanging fruit for years and it's no wonder all these alternatives are good enough to replace Office.
You may also pinch out bunch of functions as separate scripts in a git repo and share these between various notebooks/people.
Calculate md5 checksums of raw data files. If values in rows can be modified, get the hash sums (xxhash?) per row. That way even with the lowest common denominator (text CSVs) you know if the inputs changed and where.
For larger data consider storing it in parquet format and using duckdb.
Assuming that you must have Excel output calculate what's needed using raw data, notebooks/scripts then output Excel using openpyxl
I can't help thinking that if a single published paper can lead to a national policy of austerity then the problem is not with the paper ...
> Further papers … which were not found to contain similar errors, reached conclusions similar to the initial paper, though with much lower impact on GDP growth.
This tends to benefit the top 0.1%: The living situation of the the majority is worsened and their bargaining position for employment is weakened. Privatization leads to private profits that overwhelmingly go to the top 0.1%, and of course there's a direct power aspect to it as well. If you literally own the regional electricity provider, for example, that gives you direct political power that goes beyond the mere profit you can make from this ownership.
Technically, it doesn't have to be that way. For example, austerity could theoretically be used to argue for much higher top-end marginal income taxes and capital gains taxes as well as wealth taxes. But political constellations being what they are, that's usually not how it plays out.
Dead Comment
The current goverment is very unpopular, but the SPÖ in the opposition has failed to capitalize on that and is currently polling below the governing ÖVP [2]. This incident will not help.
[1] https://twitter.com/MartinThuer/status/1664992876231639042
[2] https://apa.at/produkt/apa-wahltrend/
That's because the SPÖ has made the same mistake that the German SPD or French and Italian PS did: they went away from their worker roots and aligned themselves with corporatist ideals (i.e. neoliberalism). No surprise that their electorate eroded together with the wealth and life quality of said electorate thanks to wage stagnation.
Babler is an actual social democrat in name and politics. I am pretty sure he will manage to turn around the party's fate - and hopefully, also be an example towards our social democrats, I'm sick of Olaf Scholz and his cuddling with the FDP.
This is why a political system that lets parties be born and die naturally is preferable to the US two-party system. We're stuck with a choice between two vampires.
Now, never credit maliciousness to what can be credited to incompetence, but you know, it's a lot of small details that make the whole story more bizarre.
For example: you can have an Excel file with 100k lines and have a formula accidentally replaced by a static number on line 87456. Nobody will ever find it.
I make this point when chatting with new team members that say "let's just use X," without really understanding how X works, and I use Excel as the analogy. Most people know how to use an equation field. Most people don't consider writing a spreadsheet programming, but it is.
And just like other types of programs, you can have really insidious bugs, and you need to consider how you'll address those, or address changes in the underlying or internal components of the system.
A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example.
When we launched our excel compatible spreadsheet we diligently formalized every function and wrote tests to verify behavior. It was beautiful.
Then we started receiving lots of bug reports from users because our calculations often didn’t match what they were seeing in Excel. Older Excel versions had lots of bugs and Microsoft had been carrying them forward because at that point it was “better” to carry the errors forward and display results people expected rather than fixing them and having everyone confused by the changed results.
So we copied their behavior as best we could. I wish I had the list still but it was long and full of weird edge cases which caused formulas to be incorrect.
Is this specific to Excel? Floating point math can do that regardless of the platform. Addition on floats isn't associative!
IIRC, he was pretty proud of being able not only to have a regression test suite, but also being able to do TDD-excel :)
You can add conditional formatting, VBA procedures, additional formulas etc. to do whatever validation you like. However, you need a software engineer (or someone who thinks like one) to properly implement that, and it’s not exactly fun.
[1] https://support.microsoft.com/en-us/office/lock-or-unlock-sp...
There are workarounds using VBA, but not via Typescipt - the Excel JS API knows no clipboard.
This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either.
The employees use Excel sheets handed over from "someone" and just plug in the numbers.
In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...
There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. Nobody had even heard of git. So nothing changed. It was the dark ages, and a major factor in why I quit.
The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else.
Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.
> This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
no they don't. Not even for billion dollar scale accounting
> because it sounds like an easy product to sell for lots of money.
Is it Excel though? Because if not, then you're going to have the mother of all uphill battles trying to get it sold.
It would be akin to trying to convince a giant company with hundreds of millions of lines of Java code to rewrite into golang.
You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them.
The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming.
Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to.
Other comments are amateur hour.
There are lots of bad programs too, but programming is not so democratic.
Also it is very convenient to blame a mistake on Excel.
If these tools need any type of user interaction they won't use it.
How many people still right align text per spacebar or tab if they are pros?
> and have a formula accidentally replaced by a static number on line 87456
Surely this specific case could be validated externally with a script too.
But there is no built-in tool you can use to say to excel "this column must always be a number, don't ever allow it to be autodetected as a date" or "this column must always have a formula, if the formula doesn't exist or is a static number, alert the user".
You can lock cells, but who bothers doing that in the real world.
If some user wants to view it, then the script can create a valid Excel file.
In its basic form you perform the same calculation on the totals as you did on the roots and subtract it from the result. The answer must be zero. I know it is harder on some kinds of data, but it can prevent a huge amount of every day errors.
How do you manage to require "a handful of clicks" to do it?
If you wanted automatic formula auditing, I'd recommend using R1C1 reference style so formulas using relative references are independent of the cell's location, then use FORMULATEXT function, and an array formula applied over the whole range you want to check.
Then you could be sure.
That's indeed one of the basic suggestions in Spolsky's "You Suck At Excel":
https://www.youtube.com/watch?v=0nbkaYsR94c
Of course a custom website backed by a properly schema'd SQL database would be better, but that takes money, time and someone needs to keep it updated.
People use Excel because it's on everyone's computer by default, they have full permissions to do anything with it without needing IT approval and it's surprisingly powerful when you get into it.
And if a client happens to have a really weird non-standard requirement for how their payroll is done, any accountant can easily create a custom Excel sheet just for them - instead of waiting 6 months for an external consultant company to provide them with a custom tool that does 80% what they need.
Unfortunately, this is not a problem with the tool, Excel does what it’s supposed to. Any tool in the hands of someone not properly trained to use it, or without the discipline to use it correctly, adds a risk of incorrect output.
On the other side of the spectrum there’s pure user error aka pebkac.
Where does this instance sit on the spectrum, I.e. even although it is possible to do a lot of things in excel, it’s often not a good idea to do many of things in it for a variety of deficiencies.
In some settings the sheets are just a giant white boards: different parts of the same sheet can contain partially replicated and not cross-checked data. Samples in columns, so each row is a different type. "Free flow of thought" or annotations of say numerical cell with text in the same cell.
All this steams from the fact that users do not see the data as something to be queried and compared but as a board one can just look at.
It was a column error in tabulated election results that cause all the conflict in 2020 in Antrim county, Michigan. Any computer folks who took the time to read the report by an University of Michigan computer scientist hired by the Republican chaired bipartisan committee that the Michigan state senate would have seen that there was no vote fraud, but user error that got caught almost immediately in the accountability systems that were in place to catch any errors like that.
If we stopped trying to report election returns in real time like sporting events, and could be patient a few hours, we could avoid a lot of these highly divisive controversies.
At least in the United States, election results were reported the same night for decades and there were very few questions surrounding the truth, even in 2000 people agreed on the need for a recount, even if they disagreed on the methodology or the ultimate result.
Only since 2020, all of a sudden results take multiple days and the electorate is supposed to just understand "this is how it is now" and not question anything, despite witnessing major swings in individual states or districts occurring overnight.
The main reason the counting took much longer was three-fold: many more votes were cast by mail than was typical, several states were quite close in their vote count (three states within 1%), and a few states were prohibited from taking any preparatory steps to counting mail ballots prior to election day. And this was known--and heavily reported on!--well before the election.
Counting mail-in ballots is intrinsically harder than in-person ballots. Indeed, very rarely is it ever actually completed on election night (not least of which is that in many states, the votes need not be received by election day to be counted). However, usually mail-in ballots aren't enough to decide the results of an election. But when 2/3 of the ballots are via mail, and especially when there was an expected partisan difference between in-person and mail-in ballots, it takes a lot longer to develop a good consensus as to when one of the candidates is highly likely to have won.
Good grief. That's just simply not correct. What happened is that in 2016 and 2020 specifically, the presidential election turned on a handful of states that were (by definition, really) very close. Close results take longer, because if it's not close you can call an election without finishing the count. If the close states are not critical to the result, as is more common, no one cares.
> major swings in individual states or districts occurring overnight
Again, no. This is not a new effect, nor is the fact that different groups of ballots skew differently and are counted at different times.
But in all seriousness.
1) "Decades" is 5 elections which is a sample size of garbage.
2) The election results haven't been actually reported on the same day for a long time. Absentee ballots aren't countable until after Election day in many states making it trivial to demonstrate that they haven't been counted until the next day meaning you can't have reported the exact results on the first night.
Now, news media have been making predictions on who is going to win the election and they did refrain from doing that. But one big thing to remember is that states where Republicans determined the election process such as Arizona did not even count 100% of their ballots by Nov 12 (9 days after the 3rd, aka Election Day).
If you haven't read the Michigan Senate report (result of an 8-month bipartisan investigation chaired by a Trump Republican), please do. It cleared the air and answered tons of questions for me, seeing the evidence they found and realizing that there are simple, understandable reasons explaining what happened and also explaining the ruckus that resulted from so many people crying fraud but being unwilling to take time themselves to investigate the facts of the matter.
Python+pandas is an ok alternative. The ergonomics are weird, but if you’re familiar with Python, it works enough.
(Not so) funny twist: after the election the ballots were put into unsealed bags and were stored in an office where many people had access. So all recounts that are done now, are completely worthless.