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?

Thanks a lot Peter! I'm gonna use this to see if I can tackle my more complex AVERAGEIFS formula which looks like this:

=AVERAGEIFS(INDEX(Chemistry!$B$5:$AB$599,MATCH($A3,Chemistry!$A$5:$A$599,0),0),Chemistry!$B$4:$AB$4,">="&MIN((TODAY()-WEEKDAY(TODAY(),2)+5),((TODAY()-WEEKDAY(TODAY(),2)+5)-($A$2-1)*7)),Chemistry!$B$4:$AB$4,"<="&MAX((TODAY()-WEEKDAY(TODAY(),2)+5),((TODAY()-WEEKDAY(TODAY(),2)+5)-($A$2-1)*7)))

From the "main" sheet, this formula scans the Chemistry sheet in the B$5:$AB$599 range and limits the range to the current week and the number of weeks specified in $A$2. The week numbers are in the 4th row of Chemistry sheet. Here are the components of the above AVERAGEIFS formula:

average_range: INDEX(Chemistry!$B$5:$AB$599,MATCH($A3,Chemistry!$A$5:$A$599,0),0)
criteria_range1: Chemistry!$B$4:$AB$4
criteria1: ">="&MIN((TODAY()-WEEKDAY(TODAY(),2)+5),((TODAY()-WEEKDAY(TODAY(),2)+5)-($A$2-1)*7))
criteria_range2: Chemistry!$B$4:$AB$4
criteria2: "<="&MAX((TODAY()-WEEKDAY(TODAY(),2)+5),((TODAY()-WEEKDAY(TODAY(),2)+5)-($A$2-1)*7))


I'm scared to attempt it, but I'll see what I can do

 

[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 keep getting errors and not sure why. If you could provide any input, that would be super awesome In my new file, the "main" sheet reads from the "AllScores" sheet, and Chemistry scores are the third in the array in each cell as below:​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End of week:[/TD]
[TD]22[/TD]
[TD]21[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]week #:[/TD]
[TD]

10/19/2018​

<strike></strike>[/TD]
[TD]

10/12/2018​

<strike></strike>[/TD]
[TD]

10/05/2018​

<strike></strike>[/TD]
[TD]09/28/2018[/TD]
[TD]09/21/2018[/TD]
[TD]09/14/2018[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Student1[/TD]
[TD]98,96,85,71,100,79<strike></strike>[/TD]
[TD]<strike></strike>78,89,86,77,97,83<strike></strike>[/TD]
[TD]<strike></strike>91,80,96,79,84,88<strike></strike>[/TD]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Student2[/TD]
[TD]78,91,99,79,88,83<strike></strike>[/TD]
[TD]<strike></strike>89,84,93,90,88,94<strike></strike>[/TD]
[TD]<strike></strike>97,89,77,88,100,90<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And the goal is to get the average of chemistry scores in between the current week, e.g. 22, and the number of weeks before the current week, which is specified in $A$2 in main sheet, e.g. 2, which would mean week 20. (So if I put 3 in $A$2 of main sheet, then the formula would give me the average of chemistry scores spanning weeks 22-20.) I'm guessing that the cause of the errors is that I'm not properly modifying criteria1 and criteria2 so the formula can take the third numbers in the cells (but perhaps other components of the formula may need to be modified too?)

Thank you so much ​

 

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

I'm afraid that is getting too complex for me.

 

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

Ok, no problem, I'll try to get it to work, and will post if I succeed. Thank you!