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

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 14, 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

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

      meirtu 

      Sorry, I misunderstood you, though entire formula returns an error (that happens if all cells are blank).

       

      With errors inside the range there are several ways to avoid, couple of them:

      for the column G formula is

      =AVERAGEIFS(G2:G13,G2:G13,"<>0",G2:G13,"<>#DIV/0!")

      which ignores both zeroes and this concrete error. 

      But it will be much better if you clean your data within the range, in column H formulas are

      =IFERROR(IF(D2,D2/B2,""),"")

      which returns empty string if any of D2 or B2 is zero. Or, as variant in column I

      =IF((D2=0)+(B2=0),"",D2/B2)

      With that you may use simple AVERAGE, it ignores all texts

      =AVERAGE(H2:H13)

       

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

        SergeiBaklanThanks Again! the first option you gave works wonderfully! I checked several times. I Prefer it to the second option you gave. Can I just ask about the first option why the range is repeated twice ate beginning? (G2:G13,G2:G13........)