Forum Discussion
AVERAGE and GET RID Off ZEROs
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).
- BoulakgnobotDec 09, 2023Brass Contributor
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
- rachelDec 10, 2023Steel Contributor
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).
- BoulakgnobotDec 10, 2023Brass Contributor
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