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).
rachel Thanks !
Here in YELLOW SHEETS, I want to FILTER DATAs from 'AVERAGE STUDENT 3e! (B:E) + 'DATA DETAILS 3e! (G:G;K:K;O:O;S:S) + 'AVERAGE STUDENT 3e! (F:G) for "_3eA"'s "1st Semester"
So that : It DEPENDS from (N1) and (K2) DROP DOWN LISTS
EXAMPLE :
=IF(AND((N1="1st Semester");(K2="_3eA"));IF(AND((N1="2nd Semester");(K2="_3eA"));FILTER(
=IF(AND((N1="1st Semester");(K2="_3eB"));IF(AND((N1="2nd Semester");(K2="_3eB"));FILTER(
=IF(AND((N1="1st Semester");(K2="_3eC"));IF(AND((N1="2nd Semester");(K2="_3eC"));FILTER(and REMOVE ERRORS and ZEROS
Image Illustration below
FILE Link below
rachel thanks !
1- Here I want SHARE/DIVIDE/SPLIT my student LIST in "DATA" sheet into 05 CLASSES that are in "SPLIT LIST" sheet
a- NUMBER of CLASSES = 05
b- NUMBER of STUDENT by CLASS =
in class 01 = 11
in class 02 = 15
in class 03 = 08
in class 04 = 12
in class 05 = 07 the LEFTED of STUDENTS
2- I want also make INVISIBLE when TABLES have NO STUDENT inside
in class 06 = empty and hiden
in class 07 = empty and hiden
I think you can use OFFSET to split student list:
I also added some conditional formatting to make the table highlighted in yellow if the number of students is zero:
(If you want to make the table invisible, I guess you can change the format. e.g, background colour = no fill, font colour = white).
spreadsheet attached.
rachel Thanks ! That works perfectly
1- Here I change it to 2 classes in ONE list in my "DATA" sheet ("DATA" sheet in COLUMN "I") that I want to "FILTER" before SHARE/DIVIDE/SPLIT when I CHOOSE a CLASS from my DROP DOWN LIST in CELL (E2)
I Don't UNDERSTAND why my FORMULA doesn't work correctly with "FILTER"
=IF(ISBLANK(OFFSET(FILTER(DATA!$B$19:$E$71;DATA!$H$19:$H$71='SPLIT LIST'!E2;"");SUM($C$26:$C$26);0;$C$27;4));"";OFFSET(FILTER(DATA!$B$19:$E$71;DATA!$H$19:$H$71='SPLIT LIST'!E2;"");SUM($C$26:$C$26);0;$C$27;4))
https://www.cjoint.com/c/MLot4UN52AE
FILTER() outputs a dynamic array, but OFFSET doesn't work on dynamic array, only on a range of cells.
To work around it, you will have to use CHOOSEROWS instead:
rachel Thanks !
Here I have my LIST with DUPLICATES NAME that I want to IDENTIFY
1- I want to FIND DUPLICATES in ROWS for "First NAME" and "Last NAME" …
2- … So that I can IDENTIFY them by NUMBERING them by OLDEST ("Birth" CRITERIA) COLUMN (D)
Example in IMAGE below:
File below
https://www.cjoint.com/c/MLqbbIaElge
I added a helper column in column H, DATA sheet to find duplicates.
Then I use FILTER and SORTBY to sort those duplicated rows by First Name, and then By "Birth Date".
rachel Thanks !
1- HOW to AUMATICALLY ADD this NUMBERING in DUPLICATES ???
2- … and MAINTAIN or SORT this WHOLE LIST
Illustration IMAGE below:
https://www.cjoint.com/c/MLqqqQeWoI3
I think you can adjust the helper column H:
If Name is duplicated, display N°<number>, if Name is not duplicated , display "".
Then you can use DATA_CM2A!B5:B77&DATA_CM2A!H5:H77 to add "NUMBERING" to those duplicated names:
Also I noticed you need to combine List in CM2A and List in CM2B, so I use VSTACK to do that:
rachel Thanks !
Is there a chance to GIVE same N° to the same DUPLICATES instead of CONTINUOUS NUMBERING
Example:
- JACK N°01 - JOHN N°01
- JACK N°02 - JOHN N°02
- JACK N°03
Like in the IMAGE below: