3 ms·

the secret to using Excel well in large complex tables/spreadsheets is the same secret as coding with large complex programs. You use structure, design away th

the secret to using Excel well in large complex tables/spreadsheets is the same secret as coding with large complex programs. You use structure, design away the complexity, implement constraints, error checking and tests. Note it's true that an inexperienced person is liable to make spaghetti code either way, but there's nothing fundamental about a spreadsheet format that makes it inherently unusable for a lot of small/medium problems. Indeed it even has benefits in terms of interactivity/ turn around/accessibility. Of course, there's also a lot of problems with Excel and reasons not to use it like a database or anything which fundamentally relies on maintaining data integrity, and it's liable to be the first tool reached for by the non-experienced, who will generally make a mess of things large and complex as a rule.

bradford 6y ago
> You use structure, design away the complexity, implement constraints, error checking and tests. I've never thought of Excel as an ideal tool for any of these things. I'm struggling to think of how it would have change-verifications/tests in the same way that software projects do. (I'm certainly open to the possibility that I'm ignorant/unaware on this subject)
- ACow_Adonis 6y ago
  depending on your point of view, you don't do them in the "same" way (I.e with separate unit tests or compiler enforced safety), and explicit difs are hard/impossible. And ideal is dependent on the task (it's ideal for me to get a working interactive graphic + visualisation to a user behind a corporate firewall via email in the same afternoon I get the request, I wouldn't actually choose to do 'programming' in Excel, but I consider a budget small and simple and programming is probably overkill) what you do is much closer to old-school low level programming. define the relationships between your tables and variables well, set up explicit corresponding arrays of 1s and 0s that are themselves error checks on the underlying structure/ contents of your tables, calculate things in two different spots/ways and verify equivalence holds, and use simple red/green conditional formatting to draw attention to when things fall outside of expected state, etc.

bradford 6y ago

> You use structure, design away the complexity, implement constraints, error checking and tests. I've never thought of Excel as an ideal tool for any of these things. I'm struggling to think of how it would have change-verifications/tests in the same way that software projects do. (I'm certainly open to the possibility that I'm ignorant/unaware on this subject)

ACow_Adonis 6y ago
depending on your point of view, you don't do them in the "same" way (I.e with separate unit tests or compiler enforced safety), and explicit difs are hard/impossible. And ideal is dependent on the task (it's ideal for me to get a working interactive graphic + visualisation to a user behind a corporate firewall via email in the same afternoon I get the request, I wouldn't actually choose to do 'programming' in Excel, but I consider a budget small and simple and programming is probably overkill) what you do is much closer to old-school low level programming. define the relationships between your tables and variables well, set up explicit corresponding arrays of 1s and 0s that are themselves error checks on the underlying structure/ contents of your tables, calculate things in two different spots/ways and verify equivalence holds, and use simple red/green conditional formatting to draw attention to when things fall outside of expected state, etc.