Data types 2

I’m surprised that I seem to be the only one who often knows the data type I expect. Maybe I should do less ‘poor mans ETL’ type work?

Would it be useful then if cells could have a fixed data type? one that didn’t get overwritten by pasting (unlike the rather flimsy data validation)? one that was at the cell level, not in the cell entry event (unlike the flimsy data validation*)

*What I mean here is is if you have a formula in a cell (with data validation) that results in a valid value, everything is fine. If you then change one of the precedents of that formula to give a invalid result, the data validation does not trigger. – I’m not saying thats bad or wrong, what I am saying is something that was more robust be useful?

Also what do you think of being able to give cells a unit (like miles, km, hours or something) that could then be used to check you are not doing something dumb. (I’ve lost count of the number of times I’ve added pounds, dollars, yen, and euros or whatever – luckily having the yen in there make that blunder obvious). I’ve heard this units suggestion a few times, I’m not sure how much use I would make of it, what about you?



4 Responses to “Data types 2”

  1. Jayson Says:

    Whenever I use data validation I try to keep it out of formula cells. Now that I’ve said that, it’s also not realistic to think that I try to guess what every user is going to do with the file and where they might be placing their formulas.

  2. Charlie Hall Says:

    I like the concept of units – and would use them if they were available – I guess it would be good for a user to override them in some way (possibly with a visible indicator) if they really needed to get around a limitation induced by the units. It would also make spreadsheets stronger from a audit/review perspective, which would be important for critical spreadsheets.


  3. Al Gill Says:

    Actually, the way I read the responses was that most of us know what type we expect – but we also know that users often produce the unexpected so have another line of defence in VBA (or C# or whatever).

    Agree with trying to keep validation out of formula cells. Ambivalent about units.


    PS I’m also a big fan of reading in data from wksheets and faking arrays for speed purposes – clients frequently insist on data being held in XL rather than a dB so you’re stuck trying to make it as fast as possible with what you’ve got.

  4. Adam Vero Says:

    The idea of stopping you from adding sterling to dollars, or pints to metres is great, but for more complex formulae it would break down.

    There’s absolutely nothing wrong with multiplying kg by m and dividing by squared seconds to give a force, and various other concepts such as number of customer complaints (unitless) per million dollars of product sold.

    I guess that would just mean you could not use them in all cases.

    It could be implemented so you could define the units for a cell and if that cell had a formula it would only accept precedent cells of the same unit [or none?] Cells with values in would not be affected, and you could create formulae in cells with no units applied which used values from cells with and without units applied.
    I suppose as long as this was a warning and did not stop you mixing then it would be OK. (using the same interface as the current warnings for things like numbers in text format or formula different from row above)

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: