5 ms·

> 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` sho

> 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
        sorry, meant cpp! C (apparently) uses a function that more closely resembles Excel
        wizofaus 4y ago
        C has strcat but that only does the easy part - allocating and freeing the memory is still the caller's b job.
    - wizofaus 4y ago
      Tend to agree that choice of + for string concatenation was a mistake - anyone know the history/first widely used example? Did the original BASIC have it? (I think QuickBasic did, but that's unlikely to be the origin). Edit: found https://retrocomputing.stackexchange.com/questions/20460/what-was-the-first-programming-language-to-use-for-string-concatenation https://retrocomputing.stackexchange.com/questions/20460/wha..., suggests it was ALGOL-68.
  - deleted 4y ago
    [deleted]
  - ineedasername 4y ago
    + is not a cancatenation operator in excel. It is numeric addition. It is added the numeric values of which the letter characters are merely a representation then that answer would be the same as if excel treated them as numeric data types. Alternatively, if you don’t want excel to perform addition on underlying numeric values but instead only on defined data types then the formula could result in NaN which would signal to the user that the explicit data type was not compatible with addition. I know, complaining like this probably doesn’t make sense. It’s a minor issue to begin with and I’m probably an edge case as far as excel users go. It doesn’t make sense for MS to tailor its product for my type of user.
- 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?

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
        sorry, meant cpp! C (apparently) uses a function that more closely resembles Excel
        wizofaus 4y ago
        C has strcat but that only does the easy part - allocating and freeing the memory is still the caller's b job.
  - wizofaus 4y ago
    Tend to agree that choice of + for string concatenation was a mistake - anyone know the history/first widely used example? Did the original BASIC have it? (I think QuickBasic did, but that's unlikely to be the origin). Edit: found https://retrocomputing.stackexchange.com/questions/20460/what-was-the-first-programming-language-to-use-for-string-concatenation https://retrocomputing.stackexchange.com/questions/20460/wha..., suggests it was ALGOL-68.
- deleted 4y ago
  [deleted]
- ineedasername 4y ago
  + is not a cancatenation operator in excel. It is numeric addition. It is added the numeric values of which the letter characters are merely a representation then that answer would be the same as if excel treated them as numeric data types. Alternatively, if you don’t want excel to perform addition on underlying numeric values but instead only on defined data types then the formula could result in NaN which would signal to the user that the explicit data type was not compatible with addition. I know, complaining like this probably doesn’t make sense. It’s a minor issue to begin with and I’m probably an edge case as far as excel users go. It doesn’t make sense for MS to tailor its product for my type of user.
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?