6 ms·

I think I want to do the opposite of reading my mind to be honest. The default cell type is generic, so just assume everything is a string and don’t do any pars

by ksala_ 4y ago

I think I want to do the opposite of reading my mind to be honest. The default cell type is generic, so just assume everything is a string and don’t do any parsing. Maybe show a tooltip to quickly change the cell type if you detect something that might be a number or a date. Probably it’s just different usage - when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, but simple to update and share with other people (even not technical folks) or to open a csv and quickly hide/filter some data.

Karellen 4y ago
> The default cell type is generic, so just assume everything is a string and don’t do any parsing. So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? That's... not why spreadsheets were invented. > when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, Then use a database program, not a spreadsheet! However, if there are no database programs that fit your needs, and you end up using a spreadsheet instead, don't complain that the spreadsheet acts like a spreadsheet instead of a database. It's not the tool's fault that you're the one using the wrong tool for the job.
- Dylan16807 4y ago
  > So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? If you didn't change anything else, just made A1 and B1 text cells, then adding them would still return 6912. > That's... not why spreadsheets were invented. It's decades later. Why they were invented is not the priority. > Then use a database program, not a spreadsheet! Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response.
  - zrobotics 4y ago
    Umm, if you are treating them as text cells, then adding them would product 12345678 since you are concatenating strings. I just don't think there is any good solution for this, since it's effectively impossible to automatically distinguish numbers that the user wants to treat as strings (like a zip code) and numbers that should be treated as floats or ints.
    - Dylan16807 4y ago
      + is not concatenation in any spreadsheet program I've seen, and it probably shouldn't be concatenation in anything. That's the solution, having a separate concatenation operator.
      - boston_clone 4y ago
        the '+' operator provides concatenation of strings in both python and C; in Excel, you use '=CONCAT()'... just syntactic differences, is all.
        zrobotics 4y ago
        Yeah, I spend more time doing data manipulation in python than in excel, so I thought that excel would do that as well.
        Dylan16807 4y ago
        It's not just a syntactic difference. It's a huge UX difference that one operator always does an addition on both numbers and numeric strings, and another operator always concatenates both numbers and numeric strings. The looser your typings the more it matters, and spreadsheets barely even have types.
        wizofaus 4y ago
        In C? How?
        boston_clone 4y ago
  - EvanAnderson 4y ago
    Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response. There’s nothing you can do in a spreadsheet that an RDBMS can’t do. Programmers often object to spreadsheets because they’re too “brittle”. I don’t think it’s an unreasonable objection. Most spreadsheet users don’t exhibit the rigor necessary to impose a schema on their spreadsheet-based data, nor to use features like named ranges to abstract away the physical location of data in the spreadsheet from its semantic meaning. Data validation is an afterthought, too. Spreadsheets invite silent errors when rows or columns are inserted or removed, data is copied/pasted, etc. It takes more effort, in my experience, to keep the formulae in a spreadsheet working in the face of modifications versus a database. A database explicitly separates the data from logic (at least, typically— getting into queries that act conditionally based kn queried data blurs that line a bit) and provides a strong schema and enforced validation at the time data is added or modified. You’re not going to get wrong answers from a database query because somebody added a row at the bottom of a range that isn’t covered by a SUM(), for example.
    - beenpola 4y ago
      ...I logged back into my lurker account just because of how annoying this was- I use Google Sheets all the time to do data visualization and analysis work because it just works, and because I can share my results with other people, and let them explore the data in a format that they understand. There's a reason lots of MBA types get issued high-performance, high RAM machines- because asking them to learn SQL or whatever AND asking them to learn how data structures and query syntax work instead of just doing things a little more slowly and throwing more performance at Excel makes a lot of sense. Not saying it's perfect, but please don't hate on Excel et al because they aren't the perfect world way of doing things.
      - Dylan16807 4y ago
        And even if you do teach them proper database queries, that doesn't give you a nice editable view of multiple interlinked queries and parameters, with output organized into charts and graphs. The best option down that road isn't switching to a database program, it's using a spreadsheet program that can do SQL.
- Kamq 4y ago
  > So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? If they were text, I'd expect it to end up as `12345678`, personally. + being reasonably commonly overloaded to mean string concatenation.
  - fragmede 4y ago
    Wat.
  - oarsinsync 4y ago
    Excel uses & for string concatenation. Excel considers + to be an arithmetic operation. Excel is a spreadsheet.
  - replygirl 4y ago
    you are so not the target user my dude
    - Kamq 4y ago
      Maybe, but it seems weird for it to be completely unreasonable for operator overloading to be a thing, but also expect the user to have a working knowledge of basic data types.
- eastbound 4y ago
  Everyone uses Excel as a database, and Excel should absolutely tackle this. It’s like “Everyone is using Toyota to carry children to schools in underdeveloped countries” so let’s put seatbelts, you know, even if it’s not the intended usage. The French government believed that Covid cases had stopped increasing, because their federated Excel spreadsheet was silently truncating data at 1 million rows. Accidents like this happen all the time. Access should be based on Excel, only adding sheet types: single-record forms, SQL reports, pivot tables. In fact, pivot tables are the proof that Excel is a database tool.
  - Karellen 4y ago
    > It’s like “Everyone is using Toyota to carry children to schools in underdeveloped countries” so let’s put seatbelts, you know, even if it’s not the intended usage. WTF? Are you saying Toyota don't normally put seatbelts in their cars? Like... what???
    - rawling 4y ago
      In the bed of pickups, maybe?
mmis1000 4y ago
I think the usage to store other type of type is getting popular very late after excel has being invented. It was created to count money. So it would make sense to defaults field to number unless it obviously isn't. People are just using a tool that isn't designed for your current purpose. I think probably the best change it can do is don't alter the data at input. Instead, alter it at display and compute. Then it won't actually corrupt your data anymore. Then for a wrong field type, you just need to change the type, and the correct data will be displayed as expected.

by ksala_ 4y ago

Karellen 4y ago

> The default cell type is generic, so just assume everything is a string and don’t do any parsing. So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? That's... not why spreadsheets were invented. > when I use excel is mostly to store a table of raw data where I want to store the actual data, not what excel thing the data is. I’m using it as the equivalent of a database, Then use a database program, not a spreadsheet! However, if there are no database programs that fit your needs, and you end up using a spreadsheet instead, don't complain that the spreadsheet acts like a spreadsheet instead of a database. It's not the tool's fault that you're the one using the wrong tool for the job.

Dylan16807 4y ago
> So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? If you didn't change anything else, just made A1 and B1 text cells, then adding them would still return 6912. > That's... not why spreadsheets were invented. It's decades later. Why they were invented is not the priority. > Then use a database program, not a spreadsheet! Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response.
- zrobotics 4y ago
  Umm, if you are treating them as text cells, then adding them would product 12345678 since you are concatenating strings. I just don't think there is any good solution for this, since it's effectively impossible to automatically distinguish numbers that the user wants to treat as strings (like a zip code) and numbers that should be treated as floats or ints.
  - Dylan16807 4y ago
    + is not concatenation in any spreadsheet program I've seen, and it probably shouldn't be concatenation in anything. That's the solution, having a separate concatenation operator.
    - boston_clone 4y ago
      the '+' operator provides concatenation of strings in both python and C; in Excel, you use '=CONCAT()'... just syntactic differences, is all.
      - zrobotics 4y ago
        Yeah, I spend more time doing data manipulation in python than in excel, so I thought that excel would do that as well.
      - Dylan16807 4y ago
        It's not just a syntactic difference. It's a huge UX difference that one operator always does an addition on both numbers and numeric strings, and another operator always concatenates both numbers and numeric strings. The looser your typings the more it matters, and spreadsheets barely even have types.
      - wizofaus 4y ago
        In C? How?
        boston_clone 4y ago
- EvanAnderson 4y ago
  Databases only have a fraction of the capabilities of a spreadsheet. That's not a reasonable response. There’s nothing you can do in a spreadsheet that an RDBMS can’t do. Programmers often object to spreadsheets because they’re too “brittle”. I don’t think it’s an unreasonable objection. Most spreadsheet users don’t exhibit the rigor necessary to impose a schema on their spreadsheet-based data, nor to use features like named ranges to abstract away the physical location of data in the spreadsheet from its semantic meaning. Data validation is an afterthought, too. Spreadsheets invite silent errors when rows or columns are inserted or removed, data is copied/pasted, etc. It takes more effort, in my experience, to keep the formulae in a spreadsheet working in the face of modifications versus a database. A database explicitly separates the data from logic (at least, typically— getting into queries that act conditionally based kn queried data blurs that line a bit) and provides a strong schema and enforced validation at the time data is added or modified. You’re not going to get wrong answers from a database query because somebody added a row at the bottom of a range that isn’t covered by a SUM(), for example.
  - beenpola 4y ago
    ...I logged back into my lurker account just because of how annoying this was- I use Google Sheets all the time to do data visualization and analysis work because it just works, and because I can share my results with other people, and let them explore the data in a format that they understand. There's a reason lots of MBA types get issued high-performance, high RAM machines- because asking them to learn SQL or whatever AND asking them to learn how data structures and query syntax work instead of just doing things a little more slowly and throwing more performance at Excel makes a lot of sense. Not saying it's perfect, but please don't hate on Excel et al because they aren't the perfect world way of doing things.
    - Dylan16807 4y ago
      And even if you do teach them proper database queries, that doesn't give you a nice editable view of multiple interlinked queries and parameters, with output organized into charts and graphs. The best option down that road isn't switching to a database program, it's using a spreadsheet program that can do SQL.
Kamq 4y ago
> So, if you have `1234` in A1 and `5678` in B1, `=A1+B1` should by default produce `VALUE ERROR` rather than `6912`? If they were text, I'd expect it to end up as `12345678`, personally. + being reasonably commonly overloaded to mean string concatenation.
- fragmede 4y ago
  Wat.
- oarsinsync 4y ago
  Excel uses & for string concatenation. Excel considers + to be an arithmetic operation. Excel is a spreadsheet.
- replygirl 4y ago
  you are so not the target user my dude
  - Kamq 4y ago
    Maybe, but it seems weird for it to be completely unreasonable for operator overloading to be a thing, but also expect the user to have a working knowledge of basic data types.
eastbound 4y ago
Everyone uses Excel as a database, and Excel should absolutely tackle this. It’s like “Everyone is using Toyota to carry children to schools in underdeveloped countries” so let’s put seatbelts, you know, even if it’s not the intended usage. The French government believed that Covid cases had stopped increasing, because their federated Excel spreadsheet was silently truncating data at 1 million rows. Accidents like this happen all the time. Access should be based on Excel, only adding sheet types: single-record forms, SQL reports, pivot tables. In fact, pivot tables are the proof that Excel is a database tool.
- Karellen 4y ago
  > It’s like “Everyone is using Toyota to carry children to schools in underdeveloped countries” so let’s put seatbelts, you know, even if it’s not the intended usage. WTF? Are you saying Toyota don't normally put seatbelts in their cars? Like... what???
  - rawling 4y ago
    In the bed of pickups, maybe?

mmis1000 4y ago

I think the usage to store other type of type is getting popular very late after excel has being invented. It was created to count money. So it would make sense to defaults field to number unless it obviously isn't. People are just using a tool that isn't designed for your current purpose. I think probably the best change it can do is don't alter the data at input. Instead, alter it at display and compute. Then it won't actually corrupt your data anymore. Then for a wrong field type, you just need to change the type, and the correct data will be displayed as expected.