Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Cursed Excel: "1/2"+1=45660

quadratichq.com

Post reply on HN
Print view View original

81–90 of 144 posts

Re: Cursed Excel: "1/2"+1=45660

#81

Post by hnlmorg »

dmurray wrote: post #71
hnlmorg wrote: post #61

Earlier quoted context omitted.

“1/2” is a string. So “1/2”+1 is either an error because of datetype mismatch (which is terrible UX for a spreadsheet or going to mean one of the following to scenarios: Date plus a day “1/2” concatenates with “1” The latter is wrong, the former, while unexpected, does kind of make the most sense here.

Why would you favour "date plus a day" rather than "number plus a number"? I agree Excel has to guess, and in isolation guessing that "1/2" should be parsed a date is not a terrible choice, and that parsing the individual components separately is simpler and more predictable than using the full context that it's about to be added to a number. But evaluating to 1.5 would raise few eyebrows.

> Why would you favour "date plus a day" rather than "number plus a number"?

Date is a number though. It’s only when we print them in a human readable way that they become anything else.

Whereas 1/2 is an expression.

Re: Cursed Excel: "1/2"+1=45660

#82

Post by hnlmorg »

SAI_Peregrinus wrote: post #70
hnlmorg wrote: post #61

Earlier quoted context omitted.

“1/2” is a string. So “1/2”+1 is either an error because of datetype mismatch (which is terrible UX for a spreadsheet or going to mean one of the following to scenarios: Date plus a day “1/2” concatenates with “1” The latter is wrong, the former, while unexpected, does kind of make the most sense here.

Excel allows bare strings, so `"1/2" + 1` is a string with embedded quotation marks. So that's a third option for what to do.

True. And that would probably make the most sense too.

A very good point you’ve made there :)

Re: Cursed Excel: "1/2"+1=45660

#83

Post by [deleted] »

moring wrote: post #32
deepsun wrote: post #23

Earlier quoted context omitted.

The others may have their own preferences to edit documents. It's like you edited one code file in a project, and you want everyone to switch to night IDE theme when they open that particular file.

The meaning of a value (data type in programming lingo) is not a preference because it is objective, not subjective. It depends on the cell being displayed, not on the viewer in front of the screen.

[deleted]

Re: Cursed Excel: "1/2"+1=45660

#84

Post by lawn »

post-it wrote: post #78
thesuitonym wrote: post #65

Earlier quoted context omitted.

If I type in 1/2, that means 1 divided by 2, or 0.5. If I then type +1, that means add 1. 1/2 I should never mean any kind of date, unless I'm entering it into a field that has already been declared a date field, or I have written that, then declared the field to be a date field.

I think most people that enter `1/2` in a spreadsheet do indeed mean `January 2nd` and not `0.5`. In the wider world of people using spreadsheets, dates are certainly more common than fractions.

In Sweden we don't use that numbering scheme and instead use Day/Month Year (which makes more sense as it goes from smaller to larger).

Re: Cursed Excel: "1/2"+1=45660

#85

Post by mulmen »

matsemann wrote: post #17
nabilhat wrote: post #14

Earlier quoted context omitted.

This is supported in Excel. Select options > Data > Automatic Data Conversions > untick the boxes.

How does it then work if I send the file to others. Is it saved in the file or will it just crash there?

Not sure about an Excel workbook file like xlsx but for something like a CSV there is no way to attach that preference to the file so Excel will continue to mangle data as it always has unless everyone who touches it updates their settings.

Re: Cursed Excel: "1/2"+1=45660

#86

Post by mulmen »

deepsun wrote: post #23
matsemann wrote: post #17

Earlier quoted context omitted.

How does it then work if I send the file to others. Is it saved in the file or will it just crash there?

The others may have their own preferences to edit documents. It's like you edited one code file in a project, and you want everyone to switch to night IDE theme when they open that particular file.

Well, no, it isn't because Excel actually changes the underlying data too. It's more like changing the formatting of all the files in the project and deleting all the characters after the 80th column.

Re: Cursed Excel: "1/2"+1=45660

#87

Post by brassattax »

lawn wrote: post #84
post-it wrote: post #78

Earlier quoted context omitted.

I think most people that enter `1/2` in a spreadsheet do indeed mean `January 2nd` and not `0.5`. In the wider world of people using spreadsheets, dates are certainly more common than fractions.

In Sweden we don't use that numbering scheme and instead use Day/Month Year (which makes more sense as it goes from smaller to larger).

It would be interesting to know if your Excel correctly interprets 1/2 as 1st February based on your international settings.

Re: Cursed Excel: "1/2"+1=45660

#88

Post by rickdeckard »

jstanley wrote: post #77
criddell wrote: post #7

I've never understood why they don't let you turn off automatic date parsing. That one feature has caused me more grief than anything else in Excel.

Meanwhile every time I import a CSV into LibreOffice I can't work out how to make it interpret my dates as actual dates.

If it provides any comfort, Excel in turn is unable to properly open/save a CSV with the separator being a comma (!) unless the regional settings of Windows are not defining it as such.

On german systems it's for example a semicolon, so a CSV is basically a "semicolon separated value" file, and there is no working solution around that...

Re: Cursed Excel: "1/2"+1=45660

#90

Post by tetha »

ogogmad wrote: post #76

How do people feel about array languages (like J, APL, K, BQN, Uiua) versus spreadsheets?

In my experience, a big reason why people reach to excel is the simple visualization you can get once the data is in there, more or less validly. This would make either Matlab, or Jupyter Notebooks the bigger competitor.

Except another reason to use Excel is the fairly low amount of programming knowledge you need. You can solve a lot of business requirements with a few point + click sums and averages, knowing how to fix parts of an equation while dragging and maybe some VLOOKUP as a stretch goal.

That is something excel does very well for many low-technical people.

Personally, I've found importing CSV and JSON files into postgres and working with views to export data tailor-made for excel visualizations to be a terrifying sweet spot of unholy and nasty power.

Post reply on HN