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
Here you go, try this:
=FILTER(C6:C55,MMULT(IF(D6:Q55<50,1,0),SEQUENCE(COLUMNS(D4:Q5),1,1,0)))
ajl_ahmed's avatar
ajl_ahmed
Iron Contributor
Dec 24, 2022
Thx. for your reply
Does this function update dynamically? I mean that if I change the degree from <50 to >50 the elements will be changed?
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    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)))
)

     

Share