Is it possible to store more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

[Rnkhch]

Hello,

I'm wondering if it is possible to store more than one number in an array. I'm hoping I can reduce the size of my large score sheets by storing the scores of each student in one cell where for example the first element would be math score, the second physics score, the third chemistry score, etc., like this:

A1={98,83,87,90,93}

If this is possible, how would I reference the nth element in the cell to use in subsequent calculations?

Thanks a lot for any input!

 

[Michael M]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

This

=SUM(Math!A2,Physics!A2,Chemistry!A2,Language!A2,Arts!A2)

but another way is to put a blank sheet at the beginning called Start and another at the end called End, then just use

=SUM(START:END!A2)

 

[Fluff]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

you might want to transfer the helper column further out say column XFD (the furthest column)

This is a VERY bad idea, it will cause an immense amount of bloat & is liable to slow everything down big time.

 

[jtakw]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

Wow, that's incredible! You're a genius jtakw

Now that this is taken care of, the next question is what would be the best way to put scores from multiple sheets into one cell? I tried something like this in "main" sheet and was hoping to drag the formula and easily compile the scores for all students in the main sheet, but it doesn't work:

A2=Math!A2,Physics!A2,Chemistry!A2,Language!A2,Arts!A2

Thanks a lot

Thanks, and you're welcome.

Try it like this:

=Math!A2&","&Physics!A2&","&Chemistry!A2&","&Language!A2&","&Arts!A2

 

[TotallyConfused]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

Hi,
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell
[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2
[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A2,",",REPT(" ",30)),C2*30-29,30)+0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello Jtakw

Your formula works perfectly. Nice job. I’m wondering if it can be adjusted so it would work on a series of letters instead of numbers. For example, if
A2 = AB,CD,E,FG,HIJ
C2 = 4
What would the formula in D2 be, so you would get ‘FG’ returned?

TotallyConfused

 

[jtakw]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

Hello Jtakw

Your formula works perfectly. Nice job. I’m wondering if it can be adjusted so it would work on a series of letters instead of numbers. For example, if
A2 = AB,CD,E,FG,HIJ
C2 = 4
What would the formula in D2 be, so you would get ‘FG’ returned?

TotallyConfused

How long might the Text strings in between commas be?
If they won't be longer than 15 characters or so, then just drop the +0 at the end of my formula:

Book1
	A	B	C	D
1			nth Element	Result
2	AB,CD,E,FG,HIJ		4	FG
Sheet298

Cell Formulas
Range	Formula
D2	=MID(SUBSTITUTE(A2,",",REPT(" ",30)),C2*30-29,30)

 

[TotallyConfused]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

How long might the Text strings in between commas be?
If they won't be longer than 15 characters or so, then just drop the +0 at the end of my formula:

	A	B	C	D
nth Element	Result
AB,CD,E,FG,HIJ	FG

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet298​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=MID(SUBSTITUTE(A2,",",REPT(" ",30)),C2*30-29,30)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello Jtakw

Thank you for your quick and complete response to my question. As far as answering your question about how long each text string would be between commas, I can’t answer that. I don’t have a specific problem I’m working on. I’ve been following this thread and after seeing your formula for selecting numbers, it occurred to me to wonder how to select text, which prompted my question.

I understand how the command of MID works, but I’m trying to figure out SUBSTITUTE and REPT, especially how they work together here. I played with your formula by changing the numbers, and found it still seemed to work as long as I kept the same relationship between the ‘30’ and ‘29’.

1 ) Is there any reason you picked ‘30’ rather than some other number?
2 ) If there were more than 15 characters, how would that change your formula? E.g. 20 or 30 characters.

Again, I want to THANK YOU for your help. This site has been a gold mine of help and information to beginners like me, and it is people like you that have made all this possible. In case you’re wondering about my name, yes, I’m still totally confused. It seems the more I learn about Excel and VBA, the more confused I become. Is there any hope for me?

TotallyConfused

 

[Michael M]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

@TOTALLY confused...

See my tagline

 

[TotallyConfused]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

@TOTALLY confused...

See my tagline

Hello Michael

Thanks for the offer. Normally, I’m not a drinker, but there have been times while working with Excel and VBA I’ve wondered whether I should start. I don’t think I’ll ever master all this, and now this past week I’ve heard that Microsoft has introduced seven new commands. Just what I need – more things to try to cram into my overworked brain.

TotallyConfused

 

[Rnkhch]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

Thanks!

And excellent discussion. I learned so much through this thread

 

[jolivanes]

Re: Is it possible to strore more than one number in a cell (i.e. an array in a cell), and how can the nth element be referenced in formulas?

@ TotallyConfused
If you're an Edmonton Eskimo fan you'll start drinking after today's game!