Forum Discussion
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 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 !
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 "-":
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).
