Forum Discussion

meirtu's avatar
meirtu
Copper Contributor
Oct 13, 2020
Solved

finding the everage from a column of numbers but ignoring the cells in which the value is zero

Hi, How do I find the average of a column of cells, while ignoring the cells in which the value is zero? Let's say 3 out of 6 cells which are under the average formula have a zero value, then I want ...
excel
meirtu's avatar
meirtu
Copper Contributor
Oct 13, 2020

SergeiBaklanThanks!! this works great!! why should there be a coma before the quotation marks in the formula?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 13, 2020

meirtu , you are welcome.

It separates second parameter which defines criteria, please see here AVERAGEIF function 

  • meirtu's avatar
    meirtu
    Copper Contributor
    Oct 14, 2020

    SergeiBaklanHi again. actually checking again, this formula does not work! it does not work because in some of the percentage cells in the column there is an #DIV/0! error. This error appears because the sums in some of the cells on which the percentage has to be calculated have not been fed yet, and will only be fed as the year proceeds (it's a monthly interest percentage calculation). Is there a possibility of calculating the percentage in the percentages column without dividing? Because division will allways result in division of zero until all the cells are fed. Or maybe some other solution

     

    Thanks in advance

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 14, 2020

      meirtu 

      You may wrap it by IFERROR returning in case of error empty text or any other value you prefer

      =IFERROR(AVERAGEIF(A1:A6,"<>0"),"")

       

      • meirtu's avatar
        meirtu
        Copper Contributor
        Oct 15, 2020

        SergeiBaklanThanks, but for some reason it still is not working. The formula yields no result.

        I am sending you the actual sheet after writing the formula as you stated. If you would be kind enough to look what is still preventing this from working, I would be so grateful. Please note that the sheet is laid from right to left, and the relevant formula is written in cell E14.

         

        Thanks again

  • meirtu's avatar
    meirtu
    Copper Contributor
    Oct 14, 2020

    SergeiBaklanThanks Sergei! I will definitely carefully read the tutorial link you sent me to better understand this wonderful formula, and also read about some other formulas which are not clear to me.

Resources