5 ms·
It would be nice if Excel had a builtin "double bookkeeping" feature: given a formula, ensure results are also calculated or accounted for in another place. It
by turtleyacht 3y ago
It would be nice if Excel had a builtin "double bookkeeping" feature: given a formula, ensure results are also calculated or accounted for in another place.
It would be cumbersome to always have it enabled, so it could certainly be disabled by default. But Microsoft could market the feature with suggestive tooltips.
One example is if I have the fornula `=A1+B1` in cell C1, I can go to a separate worksheet and generate a constraint like `=MUST(W1!C:C<1337)`; then Excel would flag any rows where the calculation is false (≥1,337).
Of course, this kind of goes into treating derived cells as constrained types, but it seems sanity is achieved with the easier checks.
Constraints or properties are nice in that they are not unit tests; they could be added at the "moment of instantiation" like an object constructor--but in this case, the violation occurs as a post hoc check. It has to happen first.
You might say, "I always triple-check my models and ensure worksheets are equal in multiple ways." Maybe it's possible to do it already. Sometimes, quality is about introducing frictive utility with minimal overhead.
The problems solved are usually not handled with only with an integer primitive, but hand in hand with a domain component that makes us pause and go, "Okay, I guess a person's age won't be MAX_VALUE or negative."
- deleted 3y ago[deleted]
- atoav 3y agoI am no excel wizard (have been using Libreoffice for a decade now) but I guess you can do that already (in it's own row) =IF(EXACT(ValueA;ValueB); ValueA; "Mismatch!") So you basically just compare two other rows (which you can hide if you like) and if they are exactly the same you display the value, otherwise you display an error.
- pests 3y agoThen color code the row based on that output
- turtleyacht 3y agoThank-you. Yes, it's already there in Excel. The next step may be Don't Repeat Yourself (DRY), so even N nearly duplicate formulae for N rows is N-1 more times than needed. It's not so bad with one column, but after rearranging a dozen columns and copy-pasting some corrections, the question becomes, "Is everything still working okay, or do I need to skip lunch?" There's ways around, like a `CURRENT_ROW()` function. That makes it generic. Understandably, it can be a hassle to type extra functions all the time. Boilerplate for one-liners isn't fun; the whole point is rapid iteration and prototyping. Just saying, if a model is important enough to keep around and maintain, put in some pragmatic checks--just like your example.
- badwolf 3y agoIf I'm doing a lot of moving around of things, and wanting to keep more complicated formulas working, I'll usually just use named ranges for my own sanity (and as a convenience for whoever may need to take over a workbook after me) - eg, ```=SUMIF(TransactionProductSold,"Product1",TransactionAmount)```
- wjnc 3y agoI wish someone built a tool to extract any model from Excel and help annotation and conversion to code with clean separation of input, logic, output. The amount of creepy legacy Excel is killing my organization.
- DaiPlusPlus 3y agoI know it's a fad, but isn't that the exact kind of thing that AI-enthusiasts claim ChatGPT/LlaMa/CoPilot will be capable of doing within a year or two?
- spacemanspiffii 3y agoI mean, some are claiming that AI will be capable of anything. But I don't think extracting Excel formula's is currently a focus of LLM applications? Do you know of startups or other attempts at exploring this?
- jve 3y agoThe elephant in the room, Microsoft PowerApps: https://powerusers.microsoft.com/t5/Webinars-and-Video-Gallery/Create-Power-Apps-from-Excel-with-data-Microsoft-AI-Copilot/td-p/2177009 https://powerusers.microsoft.com/t5/Webinars-and-Video-Galle... Power Apps are pushing into AI direction. And it does use AI to parse excel file. Moreover Power Apps on itself has PowerFx engine that uses Excel formulas for app + more.
- HPsquared 3y agoYou think Excel is bad, just think of the legacy those will leave behind in 20 years... Anything cloud or SaaS-related, good luck trying to dust that off down the line!
- spacemanspiffii 3y agoYes I recognize this problem in my organization as well. I think this is feasible, and tools in this direction exist already, like https://formulas.readthedocs.io/en/stable/doc.html https://formulas.readthedocs.io/en/stable/doc.html. I think one challenge is that the variable names in Excel (B3, B2-10 for a list) are not easily converted to descriptive names.
- robertlagrant 3y agoI like the idea of suggestive tooltips.
- turtleyacht 3y agoYes, it could just be "Hi there, you're using a formula depending on another formula. Do you want to add a check on these rows?" If the user elects to add a check, then expand on constraints and such. If the user selects no, remind the next user that "inadvertent modifications could result in indeterminate results." Eventually, someone receiving the attachment enables it, and a discussion starts for the group as a whole. One camp may deride the change: it will never be useful, and data is always in bounds. The other may point out some assumptions that were unclear, and now a check exists. Adding it cost nearly nothing, but coverage reduces chances of regression.
- badwolf 3y agoYou could even have a helpful office themed avatar that can provide those tooltips. Make it a fun paperclip!
- RecycledEle 3y ago> generate a constraint like `=MUST(W1!C:C<1337)`; then Excel would flag any rows where the calculation is false (≥1,337). I do exactly that double-checking in Excel with conditional formatting. If I enter a blood pressure reading that is over 250 or under 35, the cell turns bright red.