AVERAGE and GET RID Off ZEROs

Brass Contributor

Hello everyone !

 

I need help with my worksheet. I have inside 3 sheets = "DATA", "RESULTS" and "COMPOSITION"

 

1- for "COMPOSITION" sheet: I need to calculate "AVER." (D32) with some specific criteria

   - IF all "Activities" (C18:C29) HAVE "Notes" (D18:D29) so AVERAGE= SUM(Average)/Number of activities (those 12 activities)

  - IF LESS of all "Activities" (C18:C29) HAVE "Notes" (D18:D29) so AVERAGE= SUM(Average)/Number of activities (those DONE activities)

 

2- for "RESULTS" sheet: I want to GET RID of ZEROs and POTENTIAL ERRORS

 

File below

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

 

Thanks for your help !

61 Replies

@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:

 

CLASS HELP_HELP04- FIND DUPLICATES on a ROW.jpg

 

File below

 

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

 

@Boulakgnobot 

I added a helper column in column H, DATA sheet to find duplicates.

Screenshot 2023-12-16 at 3.55.33 PM.png

 

Then I use FILTER and SORTBY to sort those duplicated rows by First Name, and then By "Birth Date".

 

Screenshot 2023-12-16 at 3.56.48 PM.png

@rachel  Thanks !

 

1- HOW to AUMATICALLY ADD this NUMBERING in DUPLICATES ???

 

2- … and MAINTAIN or SORT this WHOLE LIST

Illustration IMAGE below:

 

CLASS HELP_HELP04- FIND DUPLICATES on a ROW.jpg

 

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

 

 

 

@Boulakgnobot 

 

I think you can adjust the helper column H:

If Name is duplicated, display N°<number>, if Name is not duplicated , display "".

Screenshot 2023-12-17 at 2.50.18 PM.png

 

Then you can use DATA_CM2A!B5:B77&DATA_CM2A!H5:H77 to add "NUMBERING" to those duplicated names:

Screenshot 2023-12-17 at 2.52.28 PM.png

 

Also I noticed you need to combine List in CM2A and List in CM2B, so I use VSTACK to do that:

 

Screenshot 2023-12-17 at 2.54.25 PM.png

 

@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:

 

CLASS HELP_HELP05- FIND DUPLICATES on a ROW in FULL LIST.jpg

 

@Boulakgnobot 

 

I added another helper column I to record running count of First Name + Last Name to achieve this:

Screenshot 2023-12-18 at 4.42.54 PM.png

@rachelThanks for your help !

 

I have problem when I try to "Custom Sort" "DATA_CM2A" or "DATA_CM2B"

CLASS HELP_HELP06- GET RID OFF TABLE ARRAY 01.jpg

I get this MESSAGE: << You can't change part of an array. >>

CLASS HELP_HELP06- GET RID OFF TABLE ARRAY 02.jpg

 

Even so I DELETE ALL z "Table names" from "Name Manager"

CLASS HELP_HELP06- GET RID OFF TABLE ARRAY 03.jpg

 

HOW can I GET RID Off this INVISIBLE "ARRAY" so that I can SORT it ???

 

 

@Boulakgnobot 

 

I think the "INVISIBLE" array is "=SEQUENCE(COUNTA(B5:$B$77))" you use in column A.

I change it to ROW()-4. and sorting works:

Screenshot 2023-12-21 at 9.27.36 PM.png

@rachel  Thanks !

 

Here, I want to LOOKUP Vertically the Horizontal students MARKS from (J4:K4;M4:N4;P4:Q4;S4:V4)

BUT I want the (HSTACK) to be VERTICALLY (like shown in column (D)) so that it fills once for every student (62 students) REPORT CARD instead of "INDEX" & "MATCH" which can I use But with One by One RESULT

 

Like in IMAGE below:

CLASS HELP_HELP07- TRANSPOSE z HORIZONTAL HSTACK to VERTICALLY.jpg

 

File link below:

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

 

@Boulakgnobot 

I think you can use TRANSPOSE and XLOOKUP to do this:

Screenshot 2023-12-22 at 9.57.21 PM.png

@rachel  Thanks again and again !

 

1- I create FIX and UNIQUE "REGISTRATION CODE"

 

CRITERIAS: ("School" acronym &"_") & ("First Name" acronym) & ("Birthday" without SLASH "/") & "/" & ("Gender")

 

"PC3_JA-240808/M"

 

Like in the IMAGES below:

CLASS HELP_HELP08- STUDENT REGISTRATION CODE 01.jpg

CLASS HELP_HELP08- STUDENT REGISTRATION CODE 02.jpg

 

File below:

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

 

 

@Boulakgnobot 

 

I think below should work:

Screenshot 2023-12-23 at 10.14.40 AM.png

@rachel  Thanks !

 

Do you have any idea of what to ADD to get different (true UNIQUE) "REGISTRATION CODE"
in the case of 2 students with all same (1st name, last name, birthday, place, gender, class)
like for (B5) and (B6) or (B12) and (B13) or (B53) and (B54) ??? 

 

CLASS HELP_HELP08- STUDENT REGISTRATION CODE ANSWER 01.jpg

 

File link below:

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

@Boulakgnobot 

I added a helper column J to get students' initials, column K to record a running count of students with same initials, birth date and gender. then column L adds a suffix to those students with same initials, birth date and gender.

Screenshot 2023-12-24 at 4.57.03 PM.png

@rachel  Thanks !

 

Here, for the student report cards, I want to choose which "COMPOSTITION" NOTES to DISPLAY by choosing from (F1) Drop down list:

 

=IF($F$1="1st Test" then display
"=TRANSPOSE(XLOOKUP($J$5;RESULTS!$A$5:$A$104;HSTACK(RESULTS!$H$5:$I$104;RESULTS!$K$5:$L$104;RESULTS!$N$5:$O$104;RESULTS!$Q$5:$R$104;RESULTS!$T$5:$W$104);"";0;1))"

 

=IF($F$1="2nd Test" then display
"=TRANSPOSE(XLOOKUP($J$5;RESULTS!$A$5:$A$104;HSTACK(RESULTS!$AF$5:$AG$104;RESULTS!$AI$5:$AJ$104;RESULTS!$AL$5:$AM$104;RESULTS!$AO$5:$AP$104;RESULTS!$AR$5:$AU$104);"";0;1))"

 

=IF($F$1="3rd Test" then display
"=TRANSPOSE(XLOOKUP($J$5;RESULTS!$A$5:$A$104;HSTACK(RESULTS!$BD$5:$BE$104;RESULTS!$BG$5:$BH$104;RESULTS!$BJ$5:$BK$104;RESULTS!$BM$5:$BN$104;RESULTS!$BP$5:$BS$104);"";0;1))"

 

Also (G3) = (RESULTS!AA2)

Also (G3) = (RESULTS!AY2)

Also (G3) = (RESULTS!BW2)

 

File link below:

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

 

@Boulakgnobot 

 

I added those HSTACK as Named Range and use them in the XLOOKUP.

Screenshot 2023-12-25 at 3.46.09 PM.png

Screenshot 2023-12-25 at 3.46.26 PM.png

 

For "Class Average", you can use INDIRECT:

Screenshot 2023-12-25 at 3.48.05 PM.png

 

@rachel  Thanks !

 

1- Here, for the student report cards, I want to choose which "RANKs" to DISPLAY on (E24) and "MISSINGs" on (G24) by choosing from (F1) Drop down list

 

=IF($F$1="1st Test" then display
For "RANK" =(RESULTS!AA5:AA104) or (RESULTS!AY5:AY104) or (RESULTS!BW5:BW104)
For "MISSING" =(RESULTS!X5:X104) or (RESULTS!AV5:AV104) or (RESULTS!BT5:BT104)

IMAGE below:

CLASS HELP_HELP09-CHOOSE which RANK and MISSING  to DISPLAY from z DROP DOWN Menu_HELP 02.jpg

2- For '"RESULTS" sheet

Also GET RID of (AC) "#VALUE!" errors and (AB) EMPTY VALUES in YELLOW CELLS

IMAGE below:

CLASS HELP_HELP09-CHOOSE which TEST to DISPLAY from z DROP DOWN Menu_HELP 01.jpg

 

File link below:

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

 

 

@Boulakgnobot 

 

I use IFERROR to get rid of #VALUE for rank:

Screenshot 2023-12-26 at 2.50.10 PM.png

I also noticed you used nested if to convert number grade into "Appréciations", I think you can simplify that by using XLOOKUP with -1 as match mode:

https://support.microsoft.com/en-au/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Screenshot 2023-12-26 at 2.54.50 PM.png

Screenshot 2023-12-26 at 2.50.36 PM.png

 For RANK and MISSING in report card, I also use INDIRECT. (similar to class average):

Screenshot 2023-12-26 at 2.51.06 PM.png

@rachel  Thanks !

 

Have a problem with how to CALCULATE "Annual AVERAGE" (the 3 Tests AVERAGE)

 

IMAGE below:

CLASS HELP_HELP10-CALCULATE ANNUAL AVERAGE_HELP 01.jpg

 

File link below:

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

 

@Boulakgnobot 

 

I updated the formula to calculate annual average:

Screenshot 2023-12-27 at 5.50.13 PM.png