Answered by:
if, average
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.
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
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

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 nonzero 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?

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
