AVERAGE and GET RID Off ZEROs

Brass Contributor

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 below

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

 

Thanks for your help !

61 Replies

@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 "-":

 

Screenshot 2023-12-09 at 3.46.57 PM.png

 

 

 

 

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

Screenshot 2023-12-09 at 10.06.23 PM.png

@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

CLASS HELP.jpg

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

@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

 

CLASS HELP - RANK Ex.jpg

 

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

 

 

@Boulakgnobot 

 

Hi,

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

Screenshot 2023-12-11 at 5.06.17 PM.png

 

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?

 

@rachelThanks !

 

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

 

CLASS HELP _HELP02 - RANK Ex.jpg

 

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)

@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)<>"")

 

Screenshot 2023-12-12 at 4.40.16 PM.png

 

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 ?

 

 

 

 

@rachelThanks

 

CORRECT

 

If only one M (male) ranks first: 1er

if only one F (female) ranks first: 1ère

if more than one persons rank first: 1eEx or 1èreEx

@Boulakgnobot 

 

I think below in Z18 should work (updated spreadsheet attached)

 

=IF(ISERROR(AB18),"",AB18&IF(AB18=1,IF(E18="M","er","ère"),"ème")&IF(AC18>1,"Ex","")

@rachel  Thanks

 

OBSERVATION: I don't know WHY there's DIFFERENCE between "RESULTS" sheet "AVERAGES" and "COMPOSITION" sheet "AVERAGES" ???

 

Example: for z Third student:

 

in "RESULTS" sheet "AVERAGE" = 6,59 (Y20)

while in "COMPOSITION" sheet "AVERAGE" = 8,32 (D32)

 

@Boulakgnobot 

 

I didn't realise column Y in RESULTS should be computed in the same way as D32 in COMPOSITION.

I corrected the formula in column Y. (spreadsheet updated).

Below in RESULTS Y18 should work.

=IF(COUNT(X18)=1,$Y$16*AVERAGE(IFERROR(HSTACK(G18:H18,J18:K18,M18:N18,P18:Q18,S18:V18)/TRANSPOSE(DATA!$AC$19:$AC$30),"")),"-")

@rachel  Thanks

 

Is there any FORMULA for sheet "RESULTS" to lookup/fill at once (B19:C117) for "Prénoms" and "Noms" ? and then "D19:F117) for "Appel", "Genre" and "Classe" ?

 

I can do it with:

=INDEX(DATA!B19:$B$118;MATCH($A18;DATA!$A19:$A118;0)) here my problem is I can do it but for COLUMN by COLUMN

or

FILTER(DATA!$A$19:$C$118;Results!A18;"") => here also I can do it but this will break once I add new COLUMN between them

 

@Boulakgnobot 

 

XLOOKUP can return multiple columns in one go.

 

Below formula in B18 works:

=XLOOKUP($A18,DATA!$A$19:$A$118,HSTACK(DATA!$B$19:$B$118,DATA!$C$19:$C$118,DATA!$G$19:$G$118,DATA!$H$19:$H$118,DATA!$I$19:$I$118),"",0,1)

 

I updated column G and column H too. (updated spreadsheet attached).

 

@rachel  thanks !

 

1- Here I want SHARE/DIVIDE/SPLIT my student LIST in "DATA" sheet into 05 CLASSES that are in "SPLIT LIST" sheet

a- NUMBER of CLASSES = 05

b- NUMBER of STUDENT by CLASS =

in class 01 = 11

in class 02 = 15

in class 03 = 08

in class 04 = 12

in class 05 = 07 the LEFTED of STUDENTS

 

2- I want also make INVISIBLE when TABLES have NO STUDENT inside

in class 06 = empty and hiden

in class 07 = empty and hiden 

 

SPLIT One TABLE LIST into MULTIPLE.jpg

 

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

@Boulakgnobot 

 

I think you can use OFFSET to split student list:

Screenshot 2023-12-14 at 2.47.59 PM.png

 

I also added some conditional formatting to make the table highlighted in yellow if the number of students is zero:

Screenshot 2023-12-14 at 2.52.54 PM.png

(If you want to make the table invisible, I guess you can change the format. e.g, background colour = no fill, font colour = white).

spreadsheet attached.

@rachel  Thanks ! That works perfectly

 

1- Here I change it to 2 classes in ONE list in my "DATA" sheet ("DATA" sheet in COLUMN "I") that I want to "FILTER" before SHARE/DIVIDE/SPLIT when I CHOOSE a CLASS from my DROP DOWN LIST in CELL (E2)

 

I Don't UNDERSTAND why my FORMULA doesn't work correctly with "FILTER"

 

=IF(ISBLANK(OFFSET(FILTER(DATA!$B$19:$E$71;DATA!$H$19:$H$71='SPLIT LIST'!E2;"");SUM($C$26:$C$26);0;$C$27;4));"";OFFSET(FILTER(DATA!$B$19:$E$71;DATA!$H$19:$H$71='SPLIT LIST'!E2;"");SUM($C$26:$C$26);0;$C$27;4))

 

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

 

@Boulakgnobot 

 

FILTER() outputs a dynamic array,  but OFFSET doesn't work on dynamic array, only on a range of cells.

 

To work around it, you will have to use CHOOSEROWS instead:

Screenshot 2023-12-15 at 8.23.25 AM.png