How to create an excel formula using LET and other dynamic array functions which gives an calculated output of each column?

Question

I have a table like this which stores marks of students in different subjects

Name	Class	ID	Math	English	Hindi
Tony	5	1	39	30	30
Andrew	5	2	40	20	20
Mark	5	3	40	10	30

and a tabel like this which sets the maximum marks of the subject

Subject	Max Marks
Math	40
English	30
Hindi	30

I want to create a excel formula that will return this by calculating the percentage of marks obtained in each subject and grade it accordingly and arranges the data to this

and could generate a excel function using AI which is =LET( Data, Table1, Headers, {"Name","Class","ID","Math","Math Grade","English","English Grade","Hindi","Hindi Grade"}, GradeThresholds, Table2, GetMaxMarks, LAMBDA(Subject, IFERROR(INDEX(Table2[Max Marks], MATCH(Subject, Table2[Subject], 0)), "Subject Not Found") ), Grades, LAMBDA(Score,MaxMarks, IFS( Score / MaxMarks >= 0.9, "A", Score / MaxMarks >= 0.75, "B", Score / MaxMarks >= 0.6, "C", Score / MaxMarks >= 0.5, "D", TRUE, "F" ) ), MathMaxMarks, GetMaxMarks("Math"), EnglishMaxMarks, GetMaxMarks("English"), HindiMaxMarks, GetMaxMarks("Hindi"), MathGrades, Grades(INDEX(Data,,MATCH("Math",Table1[#Headers],0)), MathMaxMarks), EnglishGrades, Grades(INDEX(Data,,MATCH("English",Table1[#Headers],0)), EnglishMaxMarks), HindiGrades, Grades(INDEX(Data,,MATCH("Hindi",Table1[#Headers],0)), HindiMaxMarks), ExpandedTable, HSTACK( INDEX(Data,,MATCH("Name",Table1[#Headers],0)), INDEX(Data,,MATCH("Class",Table1[#Headers],0)), INDEX(Data,,MATCH("ID",Table1[#Headers],0)), INDEX(Data,,MATCH("Math",Table1[#Headers],0)), MathGrades, INDEX(Data,,MATCH("English",Table1[#Headers],0)), EnglishGrades, INDEX(Data,,MATCH("Hindi",Table1[#Headers],0)), HindiGrades ), VSTACK(Headers, ExpandedTable) )

which works perfectly, but i want the formula to be dynamic and adjust itself as to i will be adding new subjects to the tables and don't want to hardcode the subject names into the formula each time I add a new subject.

Without taking this apart, I suggest you make another table that contains the dynamic formulas for grade cut-off scores by subject, then the students' grades are a simple lookup by subject and range VLookup by score. — Chris Maurer
– Chris Maurer, Commented Dec 24, 2024 at 15:50
I don't know who all downvoted the question, but I found it a nice challenge and it is well asked. — P.b
– P.b, Commented Dec 24, 2024 at 20:02

rotabor · Accepted Answer · 2024-12-25 14:36:57Z

4

I created this solution for you:

[A14]=LET(
  marks,LAMBDA(sc,subj,
    LET(r,sc/XLOOKUP(subj,Table2[Subject],Table2[Max Marks]),
      IF(r<0.5,"F",CHAR(71-INT(20*r/3)))
    )
  ),
  REDUCE(
    Table1[[#All],[Name]:[ID]],
    SEQUENCE(1,COLUMNS(Table1)-3,4),
    LAMBDA(a,v,
      HSTACK(a,
        CHOOSECOLS(Table1[#All],v),
        VSTACK(INDEX(Table1[#All],1,v)&" Grade",
          marks(CHOOSECOLS(Table1,v),INDEX(Table1[#All],1,v))
        )
      )
    )
  )
)

Where Table1 is the first table, Table2 is the second table. You can easily add new rows and columns in Table1 and new rows in Table2 without formula modification.

edited Dec 25, 2024 at 14:36

answered Dec 24, 2024 at 16:17

rotabor

5,6112 gold badges5 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Dec 24, 2024 at 17:14

what a blessing! thanks for the solution it works perfectly you saved the day. I am so so thankful...

P.b · Accepted Answer · 2024-12-24 19:46:57Z

3

Another option skipping the max score table:

=REDUCE(A1:C4,{1,2,3},
 LAMBDA(x,y,
        LET(i,CHOOSECOLS(D1:F4,y),
            n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1))))))

Table references:

=REDUCE(Table1[[#All],[Name]:[ID]],{1,2,3},
 LAMBDA(x,y,
        LET(i,CHOOSECOLS(Table1[[#All],[Math]:[Hindi]],y),
        n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1))))))

Fully dynamic:

=LET(table,Table1[#All],
     subjects,DROP(table,,3),
REDUCE(TAKE(table,,3),SEQUENCE(,COLUMNS(subjects)),
LAMBDA(x,y,
   LET(i,CHOOSECOLS(subjects,y),
       n,DROP(i,1),
HSTACK(x,
       i,
       VSTACK(TAKE(i,1)&" Grade",
              XLOOKUP(n/MAX(n),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1)))))))

edited Dec 24, 2024 at 19:46

answered Dec 24, 2024 at 19:21

P.b

12k2 gold badges15 silver badges34 bronze badges

10 Comments

rotabor Dec 24, 2024 at 20:23

Well refactored, but it can be a case when nobody has got the max score.

P.b Dec 24, 2024 at 21:05

@rotabor I understood the question as having a max mark for each subject and if a subject is added, also the max for that gets added (or gets calculated without adding in this case).

Daniel Dec 25, 2024 at 10:43

@P.b thanks a lot for your answer it's very interesting how the function uses the max mark scored by a student to calculate and assign the grades respectively, I will definitely use this in other projects, but for this question I have to set a standard max mark in another table, since sometimes not even 1 student might score a max mark.

DjC Dec 25, 2024 at 12:20

You should be able to use the same concept without REDUCE if you replace MAX with XLOOKUP (or even TOROW(Table2[Max Marks]) if the subjects are always in the same order in both tables). For example:

=LET(n,3,subj,DROP(Table1[#Headers],,n),vals,XLOOKUP(DROP(Table1,,n)/XLOOKUP(subj,Table2[Subject],Table2[Max Marks]),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1,-2),cols,SEQUENCE(,COLUMNS(Table1)),SORTBY(HSTACK(Table1[#All],VSTACK(subj&" Grade",vals)),HSTACK(cols,DROP(cols,,n))))

Cheers! ;)

P.b Dec 25, 2024 at 13:45

@rotabor

=LET(table,Table1[#All],subjects,DROP(table,,3),REDUCE(TAKE(table,,3),SEQUENCE(,COLUMNS(subjects)),LAMBDA(x,y,LET(i,CHOOSECOLS(subjects,y),n,DROP(i,1),HSTACK(x,i,VSTACK(@i&" Grade",XLOOKUP(n/VLOOKUP(@i,Table2,2,),{0.9,0.75,0.6,0.5},{"A","B","C","D"},"F",-1)))))))

including table2

|

Collectives™ on Stack Overflow

How to create an excel formula using LET and other dynamic array functions which gives an calculated output of each column?

2 Answers 2

1 Comment

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related