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?

Maybe this

Excel 2007
	A	B	C
1	98,83,87,90,93	98
2		83
3		87
4		90
5		93
6
7
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,2)
B2	=MID(A1,4,2)
B3	=MID(A1,7,2)
B4	=MID(A1,10,2)
B5	=RIGHT(A1,2)

 

[LFKim2018]

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?

The suggestion of Mr. Michael M is good. He used a helper column.
in case such helper column is hampering any data around it - you might want to transfer the helper column further out say column XFD (the furthest column) - then you can make your calculations - say, functions max, min, aver etc addressing the XFD cells without using any "complicated" workarounds. HTH

 

[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?

No....I didn't use it for a helper column.
I simply used a variety of formula to address each set of numbers.
The OP can then apply whichever one they require at any location they need...

 

[LFKim2018]

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..

 

[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?

Hi,

Don't think that'll work, what if a student is really bad at math, and got a failing score of 9, but really good at language and got a perfect score of 100...

Here's one way:

Book1
	A	B	C	D	E	F	G	H	I	J
1			nth Element	Result		Math	Physics	Chemistry	Language	Arts
2	98,83,87,90,93		4	90		1	2	3	4	5
3	6,7,8,4,0		2	7
4	9,100,64,7,100		5	100
Sheet298

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

F1:J2 is Not needed, I'm showing them for reference purposes Only, but you'll probably need something like that to refer to anyway.
Just enter the nth element you want in C, the formula will extract the correct element.

 

[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?

....glad I'm not student No 2....

 

[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?

....glad I'm not student No 2....

Don't think he/she ever showed up for class.

 

[LFKim2018]

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?

neat!

 

[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?

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