none
if, average RRS feed

  • Question

  • Good evening all.

    I'm interested in averaging a column of cells, but some values are empty, giving a false average. Is there a function (even nested functions) which will allow me to get an accurate average of all the cells with values, in the specified column, while ignoring the cells UNTIL they have values, then include the newly filled cells with newly added materials.

    TYIA.

    Saturday, August 9, 2014 5:10 AM

Answers

  • Not sure what version the following was introduced but I think in xl2007.See Help for mire information on the them.

    =AVERAGEIF(A2:A20,"<>0")

    AverageIfs for multiple criteria. Following for zero and zero length string. Note extra set of double quotes around the zero length string double quotes.

    =AVERAGEIFS(A2:A20,A2:A20,"<>0",A2:A20,"<>""""")


    Regards, OssieMac

    • Marked as answer by SteveDB1 Wednesday, August 13, 2014 3:40 AM
    Tuesday, August 12, 2014 7:45 AM

All replies

  • I am not sure what your problem is. Are the cells really empty or do they contain zeros and your worksheet is set up to not display zero values?

    The following is out of Excel Help for the Average function.

    If a range or cell reference argument contains text, logical values, or empty
    cells, those values are ignored; however, cells with the value zero are
    included.  


    Regards, OssieMac

    Saturday, August 9, 2014 6:21 AM
  • I am not sure what your problem is. Are the cells really empty or do they contain zeros and your worksheet is set up to not display zero values?

    The following is out of Excel Help for the Average function.

    If a range or cell reference argument contains text, logical values, or empty
    cells, those values are ignored; however, cells with the value zero are
    included.  


    Regards, OssieMac

    Hi Ossie.

    Sorry it took me so long to respond. Been working on replacing a floor in my bathroom.

    The column has a series of sum equations in cells. When the column's cells, from which the sum is pulling, is empty, they sum to zero.

    As a result, an average of the entire column is reduced because the zero cells are included. Thus it gives a false average.

    I'd like to have an average which includes the entire column's values, but does not incorporate the zero values until they have some non-zero value.

    E.g.,

    sum(b1:b7) = 200

    sum(b8:b14) = 350

    sum(b15:b21) = 100

    sum(b22:b28) = 100

    sum(b29:b35) = 200

    sum(b36:b42) = 0

    sum(b43:b49) = 0

    average (c7, c14, c21, c28, c35, c42, c49) = 141 2/3

    Without the zeroes, it's average(c7, c14, c21, c28, c35) =212.5

    What I'd like is to incorporate the zeroes, and still get the 212.5 at the end.

    average (c7, c14, c21, c28, c35, c42, c49) = 212.5

    Is that possible?

    Tuesday, August 12, 2014 2:53 AM
  • Not sure what version the following was introduced but I think in xl2007.See Help for mire information on the them.

    =AVERAGEIF(A2:A20,"<>0")

    AverageIfs for multiple criteria. Following for zero and zero length string. Note extra set of double quotes around the zero length string double quotes.

    =AVERAGEIFS(A2:A20,A2:A20,"<>0",A2:A20,"<>""""")


    Regards, OssieMac

    • Marked as answer by SteveDB1 Wednesday, August 13, 2014 3:40 AM
    Tuesday, August 12, 2014 7:45 AM
  • Thank you Ossie.

    That's EXACTLY what I want.

    It works perfectly.

    B-)

    Wednesday, August 13, 2014 3:41 AM