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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 13, 2020

meirtu 

Perhaps

=AVERAGEIF(A1:A6,"<>0")
  • 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

Resources