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- 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) outside; it's to say from other 2 sheets (RICHARDSON LIST'!$A$4:$F$23), (NARUTO LIST'!$A$4:$F$23) and (MICHELLE LIST'!$A$4:$F$23)

 

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 LISTS CONTAIN those DUPLICATES.
- Example: Here it's external duplicates so they come from DIFFERENT sheets lists; it's to say "RICHARDSON LIST" (sheets NAME or (RICHARDSON LIST'!B1), (NARUTO LIST'!B1) and (MICHELLE LIST'!B1))
b- FIND who are these COACHES from (RICHARDSON LIST'!$I$4:$I$23), (NARUTO LIST'!$I$4:$I$23) and (MICHELLE LIST'!$I$4:$I$23) who make DUPLICATES.

 

IMAGE below:

DATA_RACHEL_X9k 05a.jpg

 

FILE link below:

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

 

 

 

@Boulakgnobot 

 

Hi,

 

I added a "Sheet1" that consolidates all three lists to handle this:

 

Screenshot 2024-01-24 at 6.42.31 PM.png