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
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'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

         

Resources