4 ms·

Does your '=vlookup(x, ...)' have tests surrounding it? How about code review? How about source control?

pointytrees 9y ago
I think you missed the point. The comment you replied to is stating that excel does not have these things. Proper software engineering will have these. Excel is fast, but also easy to repeat a mistake. With unit tests, software is much less likely to repeat a mistake. Once the mistake is made, found, corrected, unit tested, making the mistake again is likely to be caught by the unit test.
twic 9y ago
In my experience, the fact there is a human being staring at the output all day goes a long way towards mitigating the lack of automatic tests. As programmers, we write tests because we can't see what's happening inside the program while it's at work, so we have to give it a thorough exam before letting it do that. Spreadsheet jockeys can take a radically different approach to validation.
- bonesss 9y ago
  There's also a bevy of issues that impact programmed systems that (in theory!), don't apply to Excel. I can't guarantee the a programs output will be the same after every VM patch, component update, re-deployment, or even just re-compile. Generally we have multiple devs touching the same system, or same code. Generally we want that code to be robust in the face of reuse in other components and contexts. Generally we have no ability to see production errors unless they are properly logged and monitored, etc. Worst of all I, a subject expert in nerdy things, am implementing a model in a domain I am an amateur in and have no fundamental context for assessing. A spreadsheet jockey inverts all those problems. They know what they want, and are intimately connected to its operation.
agar 9y ago
Actually, in my experience "sanity checks" were very often built into financial models. Does your balance sheet balance? That's a pretty basic "unit test". Do your calculated values match the financial statements values? No? Throw an error. Divide by zero? Reference a blank cell (similar to an unset variable)? Using a value as a number? Referencing outside of a range? All immediately throw an error that will flow through your model. And code review often does take place - in banking at least, models written by analysts are checked by associates and then VPs. People who understand what the numbers should look like can spot mistakes pretty quickly using a TI calculator. Source control is a problem, but many models are one-offs for specific projects - no company (or deal) is structured identically. You're identifying problems that have, for the most part, already been solved. Not perfectly, but in ways that work well within the structures that have been built around the use of Excel as a core tool.

I think you missed the point. The comment you replied to is stating that excel does not have these things. Proper software engineering will have these. Excel is fast, but also easy to repeat a mistake. With unit tests, software is much less likely to repeat a mistake. Once the mistake is made, found, corrected, unit tested, making the mistake again is likely to be caught by the unit test.

twic 9y ago

In my experience, the fact there is a human being staring at the output all day goes a long way towards mitigating the lack of automatic tests. As programmers, we write tests because we can't see what's happening inside the program while it's at work, so we have to give it a thorough exam before letting it do that. Spreadsheet jockeys can take a radically different approach to validation.

bonesss 9y ago
There's also a bevy of issues that impact programmed systems that (in theory!), don't apply to Excel. I can't guarantee the a programs output will be the same after every VM patch, component update, re-deployment, or even just re-compile. Generally we have multiple devs touching the same system, or same code. Generally we want that code to be robust in the face of reuse in other components and contexts. Generally we have no ability to see production errors unless they are properly logged and monitored, etc. Worst of all I, a subject expert in nerdy things, am implementing a model in a domain I am an amateur in and have no fundamental context for assessing. A spreadsheet jockey inverts all those problems. They know what they want, and are intimately connected to its operation.

agar 9y ago

Actually, in my experience "sanity checks" were very often built into financial models. Does your balance sheet balance? That's a pretty basic "unit test". Do your calculated values match the financial statements values? No? Throw an error. Divide by zero? Reference a blank cell (similar to an unset variable)? Using a value as a number? Referencing outside of a range? All immediately throw an error that will flow through your model. And code review often does take place - in banking at least, models written by analysts are checked by associates and then VPs. People who understand what the numbers should look like can spot mistakes pretty quickly using a TI calculator. Source control is a problem, but many models are one-offs for specific projects - no company (or deal) is structured identically. You're identifying problems that have, for the most part, already been solved. Not perfectly, but in ways that work well within the structures that have been built around the use of Excel as a core tool.