[HN Gopher] Excel will allow certain auto data conversions to be...
___________________________________________________________________
Excel will allow certain auto data conversions to be turned off
Author : xnhbx
Score : 40 points
Date : 2023-10-23 05:51 UTC (3 hours ago)
(HTM) web link (insider.microsoft365.com)
(TXT) w3m dump (insider.microsoft365.com)
| turtleyacht wrote:
| 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."
| atoav wrote:
| I 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 wrote:
| Then color code the row based on that output
| KronisLV wrote:
| > Then color code the row based on that output
|
| Pretty useful! https://help.libreoffice.org/latest/en-
| US/text/scalc/01/0512...
| atoav wrote:
| Exactly
| wjnc wrote:
| I 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 wrote:
| I 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 wrote:
| I 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 wrote:
| The elephant in the room, Microsoft PowerApps:
| 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.
| spacemanspiffii wrote:
| Yes 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. I think
| one challenge is that the variable names in Excel (B3, B2-10
| for a list) are not easily converted to descriptive names.
| extr wrote:
| This is great. It's actually surreal to see such a longstanding
| annoyance finally addressed.
| TMWNN wrote:
| Indeed. Every time I opened a .csv and had Excel courteously
| convert all UPCs into scientific notation, I wondered "Who
| would ever want that to happen?" Finally there is a way to
| avoid this.
| hypercube33 wrote:
| I just want it to auto size cols when I paste data instead of
| rows
| stirlo wrote:
| > Convert a continuous string of letters and numbers to a date.
|
| This has been a massive bugbear of mine. Particularly when it
| inexplicably chooses USA date formats even when faced with a
| column containing values like 15-07-75. It would frequently
| convert half the values into US date format where possible and
| leave others like above unconverted.
| rukuu001 wrote:
| Not just you. Perhaps no other software feature has caused more
| hours of lost productivity than Excel auto-formatting
| _whatever_ to a date.
|
| Edit: I wonder, does '1-1' count as a 'continuous string of
| letters and numbers'? I still don't want '1-1' to be converted
| to a date.
| paule89 wrote:
| Still remember that dna data was saved and edited inside of excel
| and somehow those huge strings of data were randomly false in
| part, because of automatic data conversion to arbitrary data
| formats.
| calrain wrote:
| I just want them to support Ctrl+Backspace to wipe the contents
| of a cell...
| curiousgal wrote:
| Or Ctrk-A when you're inside a text box ffs
| mhuffman wrote:
| I just want paste to work as a human might expect. Normal paste
| in excel ... brings color and font information in, ctrl+shift+v
| which should just be plain ... does something else? Idk wtf it
| does, but it paste strings from further back in the history. At
| least for me on mac
| ulucs wrote:
| Normal paste (paste values) exists but is a bit convoluted:
| ctrl+v -> ctrl -> v
| Neil44 wrote:
| That leading zeros thing is such a pain in the ass. Silly default
| IMO.
| cm2187 wrote:
| And to all developers: don't use leading zero in a numeric id
| stored as text unless you are vicious!
| noobermin wrote:
| So not that libreoffice calc is as feature-ful as excel but AFAIK
| it gives you options on how to autoconvert data every time you
| import or copy and paste table like data into it including not
| converting.
| aquir wrote:
| Great! I am saying in the last 10 years: all I need is a button
| in Excel called "I am a power user, don't do anything unless
| told" We are getting there :)
___________________________________________________________________
(page generated 2023-10-23 09:00 UTC)