Forum Discussion
AVERAGE and GET RID Off ZEROs
I updated column Y for average. (spreadsheet attached).
You can use below formula to filter out a table.
First row is student scores.
Second row is Maxi scores.
Blank cells are excluded.
=FILTER(VSTACK(HSTACK(I18,L18,O18,R18,S18,T18,U18,V18),HSTACK($I$16,$L$16,$O$16,$R$16,$S$16,$T$16,$U$16,$V$16)),HSTACK(I18,L18,O18,R18,S18,T18,U18,V18)<>"")
Then I SUM the second row of the table. then I divide "Total" in column X by this SUM.
I don't speak French so I will need further clarification on RANK, please correct if wrong:
If only one M ranks first: 1er
if only one F ranks first: 1ère
if more than one persons rank first: 1eEx ?
rachelThanks
CORRECT
If only one M (male) ranks first: 1er
if only one F (female) ranks first: 1ère
if more than one persons rank first: 1eEx or 1èreEx
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
