SEQUENCE formula

GMC The Macro Man

GMC The Macro Man

Board Regular
Joined
Mar 23, 2023
Messages
100
Office Version
  1. 2021
Platform
  1. Windows
Hi everyone,
I'm looking for some help with this file which reports on Students and their pass marks on the subjects they sat
The attached file has 3 sheets. The 1st, "Student Data", displays the student name details and then a list of 4 subjects that they could potentially sit along with the scoring
The 2nd sheet, "Subject Scores", FILTERs out the relevant data using a SEQUENCE formula, to show the students with their "Predicted scores".
***** A big shout out here to "Fluff" - Mr Excel MVP, who helped me get this far *******

The request I have been asked on the "Subject Scores" sheet is, could it be possible to insert a column after each subject "predicted" column, which would allow the teacher to input a FFT comment, (I have a done a MOCK UP of how I would like it to look on the "Preferred Output" sheet.

This is the current formula that I am using but how could i change it to accommodate the above?

=LET(r,SEQUENCE(ROWS(Student_Data[[Biology Nat Level]:[Maths Predicted]])),d,INDEX(Student_Data[[Biology Nat Level]:[Maths Predicted]],r,SEQUENCE(,3,8,8)),c,COLUMNS(d),s,SEQUENCE(,c+3),x,IF(s<=3,Student_Data[[SCN]:[Reg Class]],INDEX(d,r,s-3)),f,FILTER(x,INDEX(x,,1)<>0),IF(f=0,"",f))

Some info on the Student Data page that may help you understand it
Columns 1 to 4 are Student info,
Columns 5 to 28 are 3 SUBJECTS
Each subject has 8 columns
Column 1 is the Nat Level (National Level) is the level of difficulty a student will complete dependent on their age and ability
Column 2 is the Subject teacher
Column 3 is the Target Grade which the student could achieve and is set at the beginning of the TERM
Column 4 to 7 are the 4 periods through the term when a student sits an internal exam to assess if they are on Target
Column 8 is the "Predicted" score based on the Students last exam taken.

Thanks for looking

Sequence Table.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1SCNFull NameYear GroupReg ClassBiology Nat LevelBiology TeacherBiology Target GradeBiology OctBiology DecBiology JanBiology MarBiology PredictedEnglish Nat LevelEnglish TeacherEnglish Target GradeEnglish OctEnglish DecEnglish JanEnglish MarEnglish PredictedMathematics Nat LevelMathematics TeacherMaths Target GradeMaths OctMaths DecMaths JanMaths MarMaths Predicted
2P1234591Zaid HawkinsS66A Nat 7Teacher 1516222
3P1234592Neveah ArmstrongS66A Nat 5Teacher 1669933
4P1234593Cedric SteeleS66ANat 7Teacher 256677 Nat 7Teacher 1566888
5P1234594Jennifer VelazquezS66ANat 6Teacher 836499Nat 5Teacher 1411222
6P1234595Elsa MoralesS66ANat 7Teacher 823333Nat 6Teacher 1616222
7P1234596Jean AshleyS66A Nat 7Teacher 1566888
8P1234663Angeline CooleyS55BNat 6Teacher 925466Nat 5Teacher 1427333
9P1234664Drew RichardS55BNat 6Teacher 1067666Nat 4Teacher 1766644
10P1234665Larissa MeyerS55BNat 6Teacher 1023322Nat 6Teacher 1668888
11P1234666Gina GibbsS55BNat 6Teacher 1068855Nat 4Teacher 1766644
12P1234667Jazmyn CruzS55BNat 6Teacher 1024422Nat 6Teacher 1552666
13P1234779Haley CarterS44ANat 5Teacher 856677Nat 5Teacher 1557666
14P1234780Michelle SpenceS44ANat 5Teacher 937688Nat 5Teacher 1666888
15P1234781Rodolfo WardS44ANat 4Teacher 1156799Nat 6Teacher 1766666
16P1234782Andre FrederickS44ANat 5Teacher 846677Nat 4Teacher 1667888
17P1234783Barrett JenkinsS44ANat 553344Nat 535533Nat 5Teacher 1460077
18P1234784Paula MiddletonS44ANat 5Teacher 935799Nat 5Teacher 1668888
19P1234785Renee CalhounS44ANat 5Teacher 223333Nat 5Teacher 1156522Nat 5Teacher 1411111
20P1234786Cheyenne WilkinsonS44ANat 4Teacher 267888Nat 5Teacher 936477Nat 5Teacher 1467888
21P1234787Calvin WagnerS44ANat 5Teacher 269900Nat 4Teacher 1166655Nat 4Teacher 1569499
22P1234788Pedro MullenS44ANat 5Teacher 1157888Nat 5Teacher 1636944
23P1234789Christina SantanaS44ANat 5Teacher 355688Nat 4Teacher 867777Nat 5Teacher 1556666
24P1234790Simon EricksonS44ANat 5Teacher 369988Nat 5Teacher 1056677Nat 4Teacher 1567777
Student Data
Cell Formulas
RangeFormula
L4,L23:L24,L19:L21,L17L4=IFNA(LOOKUP(2,1/(Student_Data[@[Biology Oct]:[Biology Mar]]<>""),Student_Data[@[Biology Oct]:[Biology Mar]]),"")
T2:T8,T10:T24T2=IFNA(LOOKUP(2,1/(Student_Data[@[English Oct]:[English Mar]]<>""),Student_Data[@[English Oct]:[English Mar]]),"")
AB2:AB24AB2=IFNA(LOOKUP(2,1/(Student_Data[@[Maths Oct]:[Maths Mar]]<>""),Student_Data[@[Maths Oct]:[Maths Mar]]),"")


Sequence Table.xlsx
ABCDEFG
1SCNFull NameYear GroupReg ClassBiology PredictedEnglish PredictedMaths Predicted
2P1234591Zaid HawkinsS66A2
3P1234592Neveah ArmstrongS66A3
4P1234593Cedric SteeleS66A78
5P1234594Jennifer VelazquezS66A92
6P1234595Elsa MoralesS66A32
7P1234596Jean AshleyS66A8
8P1234663Angeline CooleyS55B63
9P1234664Drew RichardS55B64
10P1234665Larissa MeyerS55B28
11P1234666Gina GibbsS55B54
12P1234667Jazmyn CruzS55B26
13P1234779Haley CarterS44A76
14P1234780Michelle SpenceS44A88
15P1234781Rodolfo WardS44A96
16P1234782Andre FrederickS44A78
17P1234783Barrett JenkinsS44A437
18P1234784Paula MiddletonS44A98
19P1234785Renee CalhounS44A321
20P1234786Cheyenne WilkinsonS44A878
21P1234787Calvin WagnerS44A59
22P1234788Pedro MullenS44A84
23P1234789Christina SantanaS44A876
24P1234790Simon EricksonS44A877
Subject Scores
Cell Formulas
RangeFormula
A2:G24A2=LET(r,SEQUENCE(ROWS(Student_Data[[Biology Nat Level]:[Maths Predicted]])),d,INDEX(Student_Data[[Biology Nat Level]:[Maths Predicted]],r,SEQUENCE(,3,8,8)),c,COLUMNS(d),s,SEQUENCE(,c+4),x,IF(s<=4,Student_Data[[SCN]:[Reg Class]],INDEX(d,r,s-4)),f,FILTER(x,INDEX(x,,1)<>0),IF(f=0,"",f))
Dynamic array formulas.


Sequence Table.xlsx
ABCDEFGHIJ
1SCNFull NameYear GroupReg ClassBiology PredictedCommentEnglish PredictedCommentMaths PredictedComment
2P1234596Jean AshleyS66A
3P1234663Angeline CooleyS55B68HT Input
4P1234664Drew RichardS55B568Extra work req
5P1234665Larissa MeyerS55B2Doing well
6P1234666Gina GibbsS55B558
7P1234667Jazmyn CruzS55B25
8P1234779Haley CarterS44A7
9P1234780Michelle SpenceS44A8Speak to PT8Extra work req
10P1234781Rodolfo WardS44A59Code 9898
11P1234782Andre FrederickS44A2Doing Well7
12P1234783Barrett JenkinsS44A37
13P1234784Paula MiddletonS44A9Code 989
Preferred Output
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Question Question
Count with Multiple Criteria - Follow up
Replies
2
Views
178
myall_blues
myall_blues
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
315
Asbestos_Jen
Asbestos_Jen
G
  • Question Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
558
Greenbehindthecells
G
JenniferMurphy
  • Solved
Is there a way to sort a table without screwing up a scatter chart based on that table?
Replies
9
Views
1K
JenniferMurphy
JenniferMurphy
GMC The Macro Man
  • Solved
How to count with Multiple Criteria
Replies
6
Views
221
dreid1011
dreid1011

Forum statistics

Threads
1,222,630
Messages
6,167,191
Members
452,104
Latest member
jadethejade

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top