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...
Forum Discussion
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 ???
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).
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 ZEROsLike in picture below
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
https://www.cjoint.com/c/MLkpLeFj1KF