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

     

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

      rachelThanks !

       

      "AVERAGE" CRITERIAs are on sheet "DATA" at COLUMN (AD)

       

       

      Also HOW CAN I change/introduce this: =IF(ISERROR($AC18);"";$AC18&IF($AC18=1;"er(e)";"e")&IF($AC18>1;"Ex";""))

       

      IF(E18="M";"1er";"e");IF(E18="F";"ère";"e") and "Ex" if EQUAL)

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

        Boulakgnobot 

         

        I updated column Y for average. (spreadsheet attached).

         

        You can use below formula to filter out a table.

        First row is student scores.

        Second row is Maxi scores.

        Blank cells are excluded.

         

        =FILTER(VSTACK(HSTACK(I18,L18,O18,R18,S18,T18,U18,V18),HSTACK($I$16,$L$16,$O$16,$R$16,$S$16,$T$16,$U$16,$V$16)),HSTACK(I18,L18,O18,R18,S18,T18,U18,V18)<>"")

         

         

        Then I SUM the second row of the table.  then I divide "Total" in column X by this SUM.

         

        I don't speak French so I will need further clarification on RANK, please correct if wrong:

         

        If only one M ranks first:  1er

        if only one F ranks first: 1ère

        if more than one persons rank first: 1eEx ?

         

         

         

         

Share

Resources