Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Jul 16, 2022
Solved

Need formula to filter student information based on a condition of getting subject mark less than 50

Hi I have two ranges of data, Ranges 1 and 2, which represent the student marks in the first and second semesters respectively.  I want to collect or filter the students of ranges 1 and 2 based on ...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jul 16, 2022

ajl_ahmed 

This is an Excel Dynamic Array formula, but it will probably look as alien as the Power Query solution!

WorksheetFormula
= VSTACK(
      StudentFilterλ(Range1),
      StudentFilterλ(Range2)
  ):


StudentFilterλ
=LAMBDA(dataRange,
    LET(
        markRange, DROP(dataRange, 2, 1),
        marks,     TOCOL(markRange),
        student,   TOCOL(IF(markRange > 0, DROP(TAKE(dataRange, , 1), 2))),
        subject,   TOCOL(IF(markRange > 0, DROP(TAKE(dataRange, 2), 1, 1))),
        stage,     TOCOL(IF(markRange > 0, INDEX(dataRange, 1, 4))),
        semester,  TOCOL(IF(markRange > 0, INDEX(dataRange, 1, 2))),
        table,     HSTACK(student, stage, semester, subject),
        FILTER(table, marks < 50)
    )
)

The major catch is that you need to set your Excel 365 to the Insider beta channel to have access to the array shaping functions.

 

Resources