Quite OT: storing multivariate data in MS Excel/ Google Sheets as coordinates

[echappist]

Does anyone know if this is possible?

So that two (or more) attributes may be stored in a single cell as coordinates.

The format would be something like (a, b, c), and I would like to track a series of such coordinates for a series of events and a few different entities. Storing each of the constituent coordinate as separate cells would make this rather cumbersome.

For examples, I would like to tally up attributes a, b, and c for entities 1, 2, and 3 over three events. I'd like to store the values as follows, with the stored attributes manipulable for later use. Storing each attribute as a separate cell would probably require three different sheets, whereas the method I'm inquiring would accomplish this in one sheet.

------------Event 1-----------Event 2-------------Event 3
Entity 1---(a, b, c)-----------(a, b, c)------------(a, b, c)
Entity 2---(a, b, c)-----------(a, b, c)------------(a, b, c)
Entity 3---(a, b, c)-----------(a, b, c)------------(a, b, c)

[Louis]

If it's anything remotely complicated forget Excel.

Use MATLAB, or a real programming language.

[Louis]

To answer your question more directly, yes, it's possible to store three sets of numbers in a given cell, but however you choose to do it, it's going to be a pita to deal with. One way would be to convert the numbers into a long text string and put that in a cell, then to use them convert the text string back into numbers. (each cell can store over 32,000 characters)

However, I'd much rather use three different sheets than have to mess with that sort of trick. Why do you want to use only one sheet? The memory of your machine is the only limit on the number of sheets you can use, so going from one to three is no big deal at all.

Having said that, as I said above, if it's anything that's sort of complicated, don't use Excel.

Good Luck

[OtayBW]

You may be able to 'fool it' by formatting the data the way you want in a text file, and then importing that file into Excel with selection of appropriate column width designations. Otherwise, maybe you could just set separate columns to sort (or, or sum, or do whatever statistics you choose) for each event, and then do your work on the calculated columns...

[Louis]

I'm assuming (perhaps incorrectly) that if the OP uses three columns for each group of number he'll have more than 256 (the limit to the number of columns one can have) and therefore need to use more than one sheet.

Of course the number of rows is much more (over 65,000 on the machine I'm using right now) so that could help too. (using three rows instead of three columns for each set)

[echappist]

To answer your question more directly, yes, it's possible to store three sets of numbers in a given cell, but however you choose to do it, it's going to be a pita to deal with. One way would be to convert the numbers into a long text string and put that in a cell, then to use them convert the text string back into numbers. (each cell can store over 32,000 characters)

However, I'd much rather use three different sheets than have to mess with that sort of trick. Why do you want to use only one sheet? The memory of your machine is the only limit on the number of sheets you can use, so going from one to three is no big deal at all.

Having said that, as I said above, if it's anything that's sort of complicated, don't use Excel.

Good Luck

Thanks for the explanation.

Quick clarification to the suggestion you mentioned in your first paragraph, that would require special parsing, correct? Something like, take 1st to 19th character as proxy for 1st attribute and 21st to 39th character as proxy for 2nd attribute, with a special character of some sort (the 20th character) denoting a delimiter?

The only reason to do it is for presentation purposes, so all of the data could be viewed by all the entities concerned with ease. I could do a graph, but that gets unwieldy very quickly. Ditto with usage of multiple sheets. A table displaying coordinates would appear a more elegant way of doing this.

I guess I could still consolidate data from various sheets into one sheet used purely for displaying the attributes, but then that sheet itself isn't manipulable.

[Louis]

Something like, take 1st to 19th character as proxy for 1st attribute and 21st to 39th character as proxy for 2nd attribute, with a special character of some sort (the 20th character) denoting a delimiter?

Yes, something like that, but I don't think it will solve your fundamental problem (which based on your explanation, I now assume is the width of the display). If using three adjacent columns doesn't work then cramming three numbers into a single cell won't work either.

I think the simplest thing is just to use three columns for each set, and make them as narrow as possible. If you can't fit it all on one screen you'll just have to use the slider to go back and forth from left to right.

If you have two displays (either on the wall or on a desk) you can stretch the sheet and use the left one to show the left side of the sheet and the right one to show the right side.

[weiwentg]

in a database behind the scenes, it’s more likely that each variable would be stored as a separate variable, i.e. different columns. Each entity would probably get its own ID variable. So the raw data might look like:

ID x y z
1, 40, 30, 20
1, 41, 31, 21
1, 42, 31, 22
...
2, 40, 32, 20
2, 40, 33, 21
2, 43, 34, 22

Then you can add a column for event as well. Storing a whole bunch of variables in one column is asking for trouble when you try to convert the thing to another format or you try to extract individual variables from the super-variable.

[echappist]

Understood; thank you all for the explanation. And yes, the purpose is for a very trivial purpose of fitting the data in a display, more than anything else. I guess there really is wisdom to storing each attribute separately.

Maybe one of these days I'll enroll at the "local college" and learn Matlab

[Louis]

Maybe one of these days I'll enroll at the "local college" and learn Matlab

It will help with number manipulation stuff (which is what I originally thought you were trying to do) but isn't any better than Excel when it comes to displaying the data.

[thegunner]

jim stop using a spreadsheet as a DB.

[unterhausen]

try Julia. The free version is plenty good