Forum Discussion

ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Dec 24, 2022

Filtering Data

Hi I need to use the FILTER function to filter the range (C6:Q55) of the attached file to get the student name who got degree less the 50 in any defined subject (D4:Q4). The defined subjects should ...
Excel
office 365
  • Lorenzo's avatar
    Lorenzo
    Dec 24, 2022

    Hi ajl_ahmed

    Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed? => Why don't you try???

     

    A sligthly more concise one:

    =FILTER(C6:C55,
  MMULT(--(D6:Q55 < 50),SEQUENCE(COLUMNS(D4:Q5)))
)

     

MY's avatar
MY
Brass Contributor

PeterBartholomew1 

Great use of Lambda to solve the problem Peter - nice and easy to follow.

I wish I could learn and propose answers using Lambda but my version of Excel is on semi-annual enterprise and the company does not have access to Lambda functions; plus my co-workers would not have a clue as to how to review my work!

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 24, 2022

MY 

Your company may well need some more IT oriented staff to test the new functionality before it is released company-wide.  Your IT department / person could set one or two of you up with the insiders beta version.  I believe that to use the new functionality effectively requires 'retraining one's brain'.

Share