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!

 

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

P.S. I noticed that the combination of colon/parentheses in the formulas are automatically converted to smileys, and I cannot fix that even with editing. Can one of the forum moderators fix that for me please? Thank you!

 

[Peter_SSs]

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?

P.S. I noticed that the combination of colon/parentheses in the formulas are automatically converted to smileys, and I cannot fix that even with editing. Can one of the forum moderators fix that for me please? Thank you!

Done. In future you could
- put a space between those characters : (
or
- Click Go Advanced at the bottom of the Reply window then look down to the Additional options, one of which is "Disable smilies in text"
or
- use noparse tags (used like code tags as described in my signature block below)


What exactly are you trying to do related to post 30?
Is it to average, say, the Physics scores? If so, couldn't you just reference the Physics scores directly? Something like
=AVERAGE(Physics!A2:A31)

 

[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 Peter for the fix!

Yes, I could reference the scores directly and get the average, but the point was that if I were to use the array format extensively and convert all my sheets to the array format, then how would I reference ranges? (I'm trying to see if it's worthwhile to use the array format because if it becomes cumbersome/impossible to reference ranges, then I might as well stick to my regular sheets, i.e. one number per cell.)

 

[Peter_SSs]

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?

Well, my overall comment is that combining different values in a single cell and then trying to split that up again to perform calculations is not a good or efficient way to go. Excel works best where numbers are in individual cells.

However, if you want to persist can you tell me, perhaps with reference to the sample data in post 6, exactly what you are trying to average?

 

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

You're right. I'm just giving it a shot to see how it will work

In the post 6 example, we could for instance average the math scores of the three students, i.e. 98, 6, 9 (which in reality would be many more than just three) to see what the average of the class is for math, which would need the formula I wrote in post 30 (I only changed the reference to the nth element column (C2) in post 6 formula and substituted it with "1":

A5=

((MID(SUBSTITUTE($A2,",",REPT(" ",30)),1*30-29,30)+0),(MID(SUBSTITUTE($A3,",",REPT(" ",30)),1*30-29,30)+0),(MID(SUBSTITUTE($A4,",",REPT(" ",30)),1*30-29,30)+0))​

And I'm hoping I could eliminate the need to reference each of the three scores and instead have a range like usual where you would include only the beginning and ending elements of the range:

A5=((MID(SUBSTITUTE($A2,",",REPT(" ",30)),1*30-29,30)+0) : (MID(SUBSTITUTE($A4,",",REPT(" ",30)),1*30-29,30)+0))​

 

[Peter_SSs]

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?

In the post 6 example, we could for instance average the math scores of the three students, ...

So, using a similar formula structure to jtakw's from that post, try this copied across.

Excel Workbook
	A	E	F	G	H	I	J
1			Math	Physics	Chemistry	Language	Arts
2			1	2	3	4	5
3	98,83,87,90,93	Average	37.66666667	63.33333333	53	33.66666667	64.33333333
4	6,7,8,4,0
5	9,100,64,7,100
Scores

 

[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, this is unbelievably amazing! You are the absolute number one genius on the planet

I'm gonna apply this and see how my sheets behave.

Now I'm tempted to really convert everything into arrays If I need to reference ranges in other contexts (formulas) I'm gonna count on you

Thanks a lot Peter!!!

 

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

Just an update on the use of cell arrays. I converted one of my biggest files to the array format, and the size of the file decreased from 130MB to 70MB, and it opens faster too. I used the 'nth element' formula from post 6 and the range formula from post 36 in several scenarios, including my favorite INDEX(MATCH(),MATCH()), and so far everything works beautifully

This seems to be an amazing way of condensing huge Excel files

 

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

Hi Peter,

I got stuck at AVERAGEIFS and I hope you can help Any chance you could please demonstrate how to use AVERAGEIFS in your post 36 for a hypothetical criteria of the numbers being greater than 5, for example. I want to see which components of the AVERAGEIFS formula should be modified and how.

Thanks a lot!

 

[Peter_SSs]

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?

Any chance you could please demonstrate how to use AVERAGEIFS in your post 36 for a hypothetical criteria of the numbers being greater than 5,

Try

=SUMPRODUCT(--(MID(SUBSTITUTE($A$3:$A$5,",",REPT(" ",30)),30*(F2-1)+1,30)-5>0),--MID(SUBSTITUTE($A$3:$A$5,",",REPT(" ",30)),30*(F2-1)+1,30))/
        SUMPRODUCT(--(MID(SUBSTITUTE($A$3:$A$5,",",REPT(" ",30)),30*(F2-1)+1,30)-5>0))