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 !

 

1- Here, I want to KEEP FORMULA in (D10:D21) but :
(E10) & (G10) and (E17) & (G17) to be EMPTY because (D10) and (D17) have NO NOTES

 

IMAGE below:

CLASS HELP_HELP10-CALCULATE ANNUAL AVERAGE_HELP 02 - MAXIs & EMPTYs.jpg

2- Because some of the student does not ALL the TEST (some missings), I want also "ToTal Maxi" (E23) to SUM(E10:E21) taking in compte NOTE = O Like in (D14) & (D20) and EMPTY NOTES like in (D10) & (D17)

 

FILE link below:

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

 

@Boulakgnobot 

 

I updated the formula to deal with Blanks in "Notes":

Screenshot 2023-12-28 at 4.52.10 PM.png

 Thanks ! Happy new year !

 

1- Here I want ot EXTRACT SUFFIXE from "PREPARATION" (K) Column to FEED "Class2" (L) and "DESTINATION" (M) Columns
For "Class2" (L), I want to EXTRACT from "_"

Example:
for (K5) "Pass_3eA" = "_3eA" at (L5) and = "Class_3eA" at (M5)
for (K5) "RED_3eA" = "_3eA" at (L5) and = "Class_3eA" at (M5)

 

for (K5) "ARRIVÉ_3eA24" = "_3eA" at (L5) and = "Class_3eA" at (M5)
for (K5) "ABAND_3eA24" = "_3eA24" at (L5) and = "Class_3eA24" at (M5)
for (K5) "EXCLU_3eA24" = "_3eA24" at (L5) and = "Class_3eA24" at (M5)

 

for (K5) "DÉPART_3eA24" = "_3eA" at (L5) and = "Class_3eA" at (M5)

 

for (K5) "PassBFEM_3eA24" = "_2nd24" at (L5) and = "Class_2nd24" at (M5)

 

2- I want a CONDITIONAL FORMATTING for ENTIRE ROW Depending on (K) Column CRITERIA:

IF CONTAINS:
IF (K5="Pass_*" = Light green
IF (K5="RED_*" = Light pink
IF (K5="ARRIVÉ_*" = Light blue
IF (K5="EXCLU_*" = Light red
IF (K5="ABAND_*" = Light yellow
IF (K5="PassBFEM_*" = Light brown

 

IMAGE below:

CLASS HELP_HELP10-CALCULATE ANNUAL AVERAGE_HELP 04 - TEXT EXTRACTION.jpg

 

FILE link below:

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

 

@Boulakgnobot 

 

Happy new year!

I added some helper column to extract suffix and prefix. those helper columns are also used in Conditional Formatting.

Screenshot 2024-01-06 at 12.52.39 PM.png

 

Screenshot 2024-01-06 at 12.55.38 PM.png

@rachel  Thanks !

 

I was wondering if there might have been a MISTAKE in "AVERAGE" (Y) Column ???

Someone caugth my attention by doing this:

=X22/AA16 gives 07,19   instead of 07,72

=X23/AA16 gives 08,63   instead of 08,21

 

IMAGE below:

CLASS HELP_HELP15-AVERAGE FIXING_HELP 01 - RACHEL.jpg

 

REMEMBER "AVERAGE" Criterias below:

CLASS HELP_HELP15 - REVIEW AVERAGE to FIX ERROR_RACHEL - REFRESH.jpg

 

FILE below:

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

 

@Boulakgnobot 

 

Hi,

 

I added a step-by-step demonstration on how Y17 is calculated:

Screenshot 2024-01-10 at 9.06.53 PM.png

I see you compute Average as X17/32. Could you please let me know where this "32" comes from?

@rachel  Thanks !

 

32 is equal to "Coef" SUM(DATA!AD19:AD33)  at (AA11)

NB: here 32 was a mistake because "Arabe" and "Lecture" was not done !

 

IMAGE below:

CLASS HELP_HELP15-AVERAGE FIXING_HELP 01 - RACHEL - Suite 01.jpg

I'm not very good at maths and I want to be sure that there'll not be mistake in future so I ask:

CAN I CONCLUDE that this is the good and best method of calculating this AVERAGE ???

 

@Boulakgnobot 

Hi,

 

I think the question is HOW you want to calculate Average.

Method 1: Average = Sum Of Notes / Sum Of Coef.

That would be 230 / 32

Like below:

Screenshot 2024-01-11 at 4.14.36 PM.png

 

Method 2: Average = Average (Note1 / Coef1 + Note2 / Coef2 + )

Like this:

Screenshot 2024-01-11 at 4.10.48 PM.png

Those two methods will give two different results.

 

I don't have any preference for those two. 

Do you have any documentation on how students average should be computed?

 

@rachel  Thanks !

 

1- for "Registration" (G)
I want data validation to ACCEPT ONLY when 5 Digits (NO LETTER)

 

2- for "Secure" (H)
I want data validation to ACCEPT Only 9 Digits which start either with "1" for men ("M" at (F) Column) or "2" for female ("F" at (F) Column) and No LETTER

Example: "Sexe" is (M) will START with "1" example "125421503"
Example: "Sexe" is (F)   will START with "2" example "232030446"

 

3- for "Xpiration" (I)
I want data validation to ACCEPT ONLY when Date is between 02 February 2009 and 19 December 2012

 

IMAGE below:

DATA_RACHEL_DVAL - X9k 01a.jpg

 

FILE link below:

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

 

@Boulakgnobot 

 

You can add Customised data validation criteria for those:

Screenshot 2024-01-16 at 6.43.14 PM.png

 

I have added data validation in Column G, H, I.

@rachel  Thanks !

 

Here I want to make some changes to ENRICH the "DATA VALIDATIONS" and FILL this TABLE with strict CONDITIONS:

- I want to BEGAN by ENTERING "Registration" (D)

 

1- for "Registration" (D)
a- I want to MAINTAIN this data validation
b- BUT only IF this (D5) "Registration" Code EXISTS in "DATA CENTER"'s "Registration" Code ('DATA CENTER'!$G$5:$G$275)

 

2- for "Xpiration" (F)
a- I want to also MAINTAIN this data validation
b- BUT only IF (D5) "Registration" Code is already ACCEPTED first
c- and then IF this (F5) "Xpiration" Code MATHES "DATA CENTER"'s "Xpiration" Code ('DATA CENTER'!$I$5:$I$275)

 

3- for "Secure" (E)
a- I want to MAINTAIN this data validation also
b- But only IF (D5) and (F5) Are already ACCEPTED

 

4- for "First Name" (B), "Last Name" (C), "Class" (G) and "Shool" (H)
a- As soon as "Secure" (E) is ACCEPTED, the REST of the TABLE is FILLED CORRECTLY

 

IMAGE below:

DATA_RACHEL_X9k 02a.jpg

 

FILE link below:

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

 

@Boulakgnobot 

Hi,

 

I just added some cascading dropdown list for you.

Column D dropdown list will be 'DATA CENTER'!$G$5:$G$275.

 

Column F dropdown list will be based on the Registration code in column D.

 

Column E dropdown list will be based on Xpiration in column F.

 

Other fields will be populated automatically once those three columns are filled in.

@rachel  Thanks !

 

Maybe I explained wrong this time !
Example:
- I have 3 schools that want to enter in competition.
- The Organisation PUT ALL 3 schools students INFORMATION in "DATA CENTER" (sheet)
- Every school comes with its own students LIST Like here with "RICHARDSON"'s LIST (sheet) to COMPARE with "DATA CENTER" and ACCEPT only MATCHING lines

 

I want to make some changes to ENRICH the "DATA VALIDATIONS" and FILL this TABLE with strict CONDITIONS:

- VERIFICATION will BEGAN with "Registration" (D)

 

1- for "Registration" (D)
a- I want to MAINTAIN this data validation
b- BUT only IF this (D4) "Registration" Code EXISTS in "DATA CENTER"'s "Registration" Code ('DATA CENTER'!$G$4:$G$275)

 

2- for "Xpiration" (F)
a- I want to also MAINTAIN this data validation
b- BUT only IF (D4) "Registration" Code is already ACCEPTED first
c- and then IF this (F4) "Xpiration" Code MATHES "DATA CENTER"'s "Xpiration" Code ('DATA CENTER'!$I$4:$I$275)

 

3- for "Secure" (E)
a- I want to MAINTAIN this data validation also
b- But only IF (D4) and (F4) Are already ACCEPTED

 

IMAGE below:

DATA_RACHEL_X9k 02a 02.jpg

 

FILE link below:

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

 

 

@Boulakgnobot 

Hi,

 

Thanks for the explanation.

I added some extra criteria in the Data Validation:

Screenshot 2024-01-18 at 12.06.53 PM.png

@rachelThanks !

 

PS: Here I have 2 types of MISTAKE in NOT MATCHING names : LENGTHEN names and/or SHORTEN names
Is there any chance to PUT:

- SURPLUS Letters in RED ???
Example: "MAGGIE"A"" because the RIGHT name was = "MAGGIE"
Example: "ADAM"O" GEISHA" because the RIGHT name was = "ADAMA GEISHA"
Example: "DAVID"S"" and "PALMER"E"" because the RIGHT name was = "DAVID" and "PALMER"

- and SHORTEN names in PURPLE ???
Example: "JONE" because the RIGHT name was "JONES"

 

1- for "First Name" (K) and "Last Name" (L)
a- FILTER the "First Names" (B) and "Last Names" (C) that DON't MATCH/EXIST with "DATA CENTER"'s names ('DATA CENTER'!$B$4:$B$274) and ('DATA CENTER'!$C$4:$C$274)

 

2- BRING these manes that DON't MATCHES "N°" Code (A), "Registration" Code (D), "Secure" Code (E) and "Xpiration" Code (F) (this sheet)

 

3- for "N° in DC" (P), "First Name in DC" (Q) and "Last Name in DC" (R)
a- FIND their RIGHT "N°" from ('DATA CENTER'!$A$4:$A$274), "First Name" from ('DATA CENTER'!$B$4:$B$274) and "Last Name" from ('DATA CENTER'!$C$4:$C$274)

 

IMAGE below:

DATA_RACHEL_X9k 02a 03.jpg

 

FILE link below:

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

 

 

 

@Boulakgnobot 

 

Hi,

I managed to find "MISTAKES" in name using FILTER and FIND function.

Screenshot 2024-01-20 at 4.15.01 PM.png

 

But I haven't figured out an easy way to hight light SURPLUS letter in RED or PURPLE yet. probably have to do that by hand.

@rachel  Thanks !

 

I don't know WHY there's nothing for (P7:R7) and (P8:R8) BUT I think that's because of :

- (B8) mistake spelling is in the 1st word's last letter "ADAMO GEISHA" for "ADAMA GEISHA"

- and (B10) mistake is the in spelling "Q" instead of "CK" ("RIQ" for "RICK")

 

The only VALID CRITERAS here is this TABLE must get :

a- ALL NAMES that don't APPEAR in "DATA CENTER"'s

b- But those names "Registration" Code (D), "Secure" Code (E) and "Xpiration" Code (F) MUST MATCH EXACTLY "DATA CENTER"'s "Registration" Code ('DATA CENTER'!$G$4:$G$275), "Secure" ('DATA CENTER'!$H$4:$H$275) and "Xpiration" Code ('DATA CENTER'!$I$4:$I$275) first

 

IMAGE below:

DATA_RACHEL_X9k 02a 03a.jpg

@Boulakgnobot 

 

Hi,

 

Thanks for further clarification!

 

Yes, I realised FIND function has limitations for cases such as  "ADAMO" "RIQ".

 

But I think below criterion actually make things easier:

a- ALL NAMES that don't APPEAR in "DATA CENTER"'s

b- But those names "Registration" Code (D), "Secure" Code (E) and "Xpiration" Code (F) MUST MATCH EXACTLY "DATA CENTER"'s "Registration" Code ('DATA CENTER'!$G$4:$G$275), "Secure" ('DATA CENTER'!$H$4:$H$275) and "Xpiration" Code ('DATA CENTER'!$I$4:$I$275) first

 

It is better to use XLOOKUP to search for First Name in DC and Last Name in DC by ColumnD + ColumnE + ColumnF first, and then check whether or not First Name and Last Name match the "First Name in DC" and "Last Name in DC".

Screenshot 2024-01-21 at 2.54.05 PM.png

@rachel  Thanks !

 

1- for "N°" (K), "First Name" (L), "Last Name" (M), "Registration" (N), "Secure" (O) and "Xpiration" (P)
a- I want to FIND the students who are recruited SEVERAL TIMES (DUPLICATES) inside a same LIST ($A$4:$F$23) (left TABLE)

 

2- for "N° in DC" (Q), "First Name in DC" (R) and "Last Name in DC" (S)
a- FIND their RIGHT "N°" from ('DATA CENTER'!$A$4:$A$274), "First Name" from ('DATA CENTER'!$B$4:$B$274) and "Last Name" from ('DATA CENTER'!$C$4:$C$274)

 

3- for "which LISTS" (T) and "Coach" (U)
a- FIND which LIST bring those DUPLICATES.
- Example: Here it's internal duplicates so they come from this sheet list; it's to say "RICHARDSON LIST" (sheet NAME or (B1))
b- FIND who are these COACHES from ($I$4:$I$23) who make DUPLICATES.

 

IMAGE below:

DATA_RACHEL_X9k 04a.jpg

 

FILE link below:

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

 

 

@Boulakgnobot 

 

Hi, 

I use COUNTIFS to find duplicates. and then XLOOKUP to find FirstName in DC and LastName in DC.