Forum Discussion
AVERAGE and GET RID Off ZEROs
rachel Thanks
Is there any FORMULA for sheet "RESULTS" to lookup/fill at once (B19:C117) for "Prénoms" and "Noms" ? and then "D19:F117) for "Appel", "Genre" and "Classe" ?
I can do it with:
=INDEX(DATA!B19:$B$118;MATCH($A18;DATA!$A19:$A118;0)) here my problem is I can do it but for COLUMN by COLUMN
or
FILTER(DATA!$A$19:$C$118;Results!A18;"") => here also I can do it but this will break once I add new COLUMN between them
XLOOKUP can return multiple columns in one go.
Below formula in B18 works:
=XLOOKUP($A18,DATA!$A$19:$A$118,HSTACK(DATA!$B$19:$B$118,DATA!$C$19:$C$118,DATA!$G$19:$G$118,DATA!$H$19:$H$118,DATA!$I$19:$I$118),"",0,1)
I updated column G and column H too. (updated spreadsheet attached).
See attached.
I get lost in your nested IFs too.
So I added a table, and use XLOOKUP in columnD.
rachelThanks !
Here I want to FILL (D:D) with COLUMNS (A:A) and (C:C)
IF (A:A="Move up to a higher class" + C:C="Successful" OR "Fail")) OR (A:A="Repeats in case of failure" + C:C="Successful") = "PassB25"
IF ("Repeats in case of failure" + "Fail") OR (Exceptional repetition" + ("Successful" OR "Fail")) = "Repeat25"
IF ("Excluded in case of failure" + ("Successful" OR "Fail")) = "Exclude24"
IF ("Abandon" + "Abandon" = "Exclude24"
IF ("Move" + "Move" = "Move24"
I TRY with this below FORMULA but I still don't understand WHY it doesn't WORK ?
=IF(B3="";"";IF(OR(IF(AND(B3="3A";A3="Move up to a higher class";IF(OR(C3="Successful";C3="Fail")));IF(AND(B3="3A";A3="Repeats in case of failure";C3="Successful")));"PassB";"")
IMAGE below :
FILE link below :
https://www.cjoint.com/c/NGvtWN3UC1G
Updated file.
I added IF() around XLOOKUP to replace "0" with "".
rachel Thanks !
Here, I have 3 dependent Drop-Downs (Q1), (Q2) and (M2)
The PROBLEM is that when it comes for "LEVEL {"Class_5e";"Class_6e"} (Q2) there is NO "ITV" Notes for those 2 LEVELS in 'DATA DETAILS'! Sheet so it gives me back ZEROs
SOLUTION : I want to GET RID Off z ZEROs in (I) Column
IMAGE below :
LINK below :
https://www.cjoint.com/c/NGobFKw7MnC
Can you try this OneDrive link:
I think something is wrong with uploading file in this site.
I tried to download some of the files I uploaded last week, didn't work either.
Thanks !
I have problem to DOWNLOAD this FILE. It gives me html filerachel Thanks !
Now HOW can I get rid off the zeros and the extras ?
IMAGE below :
FILE link below :
https://www.cjoint.com/c/NGlkZa7ttBp
Yes. Helpers are required for sorting.
I noticed you already have "RANK" in AVERAGE STUDENT 3e. So for 5 best students, I filter out students with RANK <=5; for 10 best students, I filter out students with RANK <= 10. (see attached).
rachel Thanks !
Here, I want to FILTER depending on (L1) Criteria, the NUMBER of BEST STUDENTS (2;5 or 10 highest AVERAGE) from each class in 'AVERAGE STUDENT 3e'! sheet
(F); (0); (X) depending on (I1)
Maybe with "Helpers"
FILE link below :
https://www.cjoint.com/c/NGea4Uc8z7G
