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 

 

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).

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

      rachelThanks a lot ! That works fine !

       

      I have 2 other problems:

       

      1- COLUMN (Y) => The "AVERAGE" is not correct because it doen't take in account the empty NOTES of the ROW

       

      2- COLUMN (Z) => I want to ADD "ex" to every same RANK of students

       

       

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

       

       

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

        Boulakgnobot 

         

        Hi,

        I added two helper columns AB and AC to handle rank (spreadsheet attached):

         

        In column Y, I need to clarify on how to compute Average:

        Full Mark = 40, Student get 20, then percentage score is 20/40, and then we just take average of that?

         

Share