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 belowhttps://www.cjoint.com/c/MLiwD6WIdwc Thanks for your help !

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 LISTSEXAMPLE :=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 belowhttps://www.cjoint.com/c/NFBna5Sql31

Boulakgnobot Hi, In D32 of "COMPOSITION", you can use =AVERAGEIF(D18:D29,">0",D18:D29) In "RESULTS" sheet, if you don't like zeros, you can change the number format to "Accounting", it will display zero as "-":

rachelTHANKS for your help in the first point ! Question: WHAT else can I do if I WANT to keep my cutom number format (00,00) WHILE getting rid off zeros ???

Boulakgnobot I updated your spread sheet to remove zeros and errors. (spreadsheet attached).Basically use SUMPRODUCT instead of SUM to handle blank cells. (though I am not even sure this is the most elegant way to handle this).

rachel Thanks for your help ! Now I have another problem: How can I get SUM(G20:H20) = 0 if G20 and H20 are not empty ???It's to say the student does those TESTS but gets ZEROs Like in picture below

AVERAGE and GET RID Off ZEROs | Microsoft Community Hub

rachel
Steel Contributor
Dec 09, 2023
Boulakgnobot
Hi,

In D32 of "COMPOSITION", you can use =AVERAGEIF(D18:D29,">0",D18:D29)

In "RESULTS" sheet, if you don't like zeros, you can change the number format to "Accounting", it will display zero as "-":
- Boulakgnobot
  Brass Contributor
  Dec 09, 2023
  rachelTHANKS for your help in the first point !
  
  Question: WHAT else can I do if I WANT to keep my cutom number format (00,00) WHILE getting rid off zeros ???
  - rachel
    Steel Contributor
    Dec 09, 2023
    Boulakgnobot
    
    I updated your spread sheet to remove zeros and errors. (spreadsheet attached).
    Basically use SUMPRODUCT instead of SUM to handle blank cells. (though I am not even sure this is the most elegant way to handle this).
    MLiwD6WIdwc_CLASS-HELP_remove zeros.xlsx101 KB

Forum Discussion

AVERAGE and GET RID Off ZEROs

Share

Resources