AVERAGE and GET RID Off ZEROs

Boulakgnobot 

See attached.

HELP-FILTER-FILL-01.xlsx

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

Boulakgnobot 

Updated file.

HELP-24-FILTER-DIFFERENT-CLASSES-DIFFERENT-DISCIPLINES---RACHEL---HELP-03-suite02---GET-RID-OFF-ZEROs-.xlsx

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

Boulakgnobot 

Can you try this OneDrive link:

HELP-24-FILTER-DIFFERENT-CLASSES-DIFFERENT-DISCIPLINES-22-Juin-2024---RACHEL---HELP-03-suite01---GET-RID-OFF-ZEROs.xlsx

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 file

Boulakgnobot 

Adding more filtering for "IEN" should fix it:

See attached.

rachel  Thanks !

Now HOW can I get rid off the zeros and the extras ?

IMAGE below :

FILE link below :

https://www.cjoint.com/c/NGlkZa7ttBp

Boulakgnobot 

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

Boulakgnobot 

I see. then we need to add an IF inside SORT:

rachel  Thanks !

Here, there's some mistake with SORTING

It's OK for (Q1) "2nd Semester" because I wanted to SORT it by (L) "ANNUAL"

BUT there's a PROBLEM when it comes for (Q1) "1st Semester" because I want it to be SORT also BUT by (J) "S01"

FILE link below :

https://www.cjoint.com/c/NGdbfEgmBie

Boulakgnobot 

On 1). I think it is better to separate Average and Rank:

On 2), I added helper columns for sorting:

See attached.

rachel  Thanks !

1- NOW, I want my "1st Semester" RANKs to be in (M) same COLUMN as for "2nd Semester"

EXAMPLE :
IF = "1st Semester", Average STILL in (J) COLUMN but Rank GOES to (M) COLUMN Like for "2nd Semester"

Here, is it possible to Separate 2 Adjacents Columns at once ?

PICTURE below :

FILE link below :

https://www.cjoint.com/c/NGbp35tps6W

2- HOW can I, at the same time, SORT this LIST depending on (L);(D) then (C) Columns that mean the "ANNUAL" Average first then Last Name and First Name ?

Boulakgnobot 

I think you need to put CHOOSECOLS outside INDIRECT (see attached):

rachel  Thanks !

NOW, I want to change my "2nd Semester" to be FILTERED from 'AVERAGE STUDENT 3e! (O:T) with NON ADJACENTS by CHOOSING (O;Q;S;T) COLUMNS

EXAMPLE :
IF = "2nd Semester" so (J5:O85) = 'AVERAGE STUDENT 3e! (O3:T85) but WITHOUT (P) and ®
Maybe by USING CHOOSECOLS()

FILE link below :

https://www.cjoint.com/c/NGbb0QHdkPT

Boulakgnobot 

Yes, I believe you can. (Attached the excel).

I just convert G2:J7 as a table and names it "Classes". and I put =INDIRECT("Classes["&B2&"]") as data validation list:

rachel  Thanks !

Can I use the same principle to fill my drop down ?

Here I WANT "BLOC" (B3) DROP-DOWN LIST to DEPEND from "LEVEL" (B2) DROP-DOWN LIST

EXAMPLE :
- IF "LEVEL" = "Class_3"
       "BLOC" = "3B";"3B";"3C";"3D";"3E";"3F"
- IF "LEVEL" = "Class_4"
       "BLOC" = "4B";"4B";"4C";"4D";"4E";"4F"
- IF "LEVEL" = "Class_5"
       "BLOC" = "5B";"5B";"5C";"5D";"5E";"5F"
- IF "LEVEL" = "Class_6"
       "BLOC" = "6B";"6B";"6C";"6D";"6E";"6F"

FILE below :

https://www.cjoint.com/c/NFDdpxdfw7u

Boulakgnobot 

See attached.

I put the address of relevant ranges for 1st Semester, 2nd Semester in below table:

Then I use INDIRECT to get data from those ranges.