Forum Discussion

Boulakgnobot's avatar
Boulakgnobot
Brass Contributor
Dec 08, 2023

AVERAGE and GET RID Off ZEROs

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 c...
Formulas and Functions
rachel's avatar
rachel
Steel Contributor

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's avatar
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's avatar
    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).

    • Boulakgnobot's avatar
      Boulakgnobot
      Brass Contributor
      Dec 09, 2023

      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

       

      • rachel's avatar
        rachel
        Steel Contributor
        Dec 10, 2023

        Boulakgnobot 

         

        I updated the formula in I18 to

        =IF(AND(G18:H18=""),"",SUMIF(G18:H18,"<>"))

         

        the formula in X18 to
        =IF(AND(HSTACK(I18,L18,O18,R18,S18,T18,U18,V18)=""),"",SUMPRODUCT(HSTACK(I18,L18,O18,R18,S18,T18,U18,V18),{1,1,1,1,1,1,1,1}))

         

        (Updated spreadsheet attached).

Resources