On 06/05/2015 18:13, John Rumm wrote:
On 06/05/2015 15:44, ss wrote:
I know wrong newsgroup but trying to get something I can understand So
hoping one of you guys understands excel formulas.
Using this formula:
=AVERAGEIF(C3:AL3,"%",C18:AL18)

What thats doing for me is giving me the number of cells that have a
number in them under the column heading % and that works fine.

Problem is if the cells in C18:AL18 are zero it picks up the zero and
counts that in as well. Even if I change the cells to dont show a zero
show a blank it is effectively still there.

Can anyone help to adjust the formula for me.
This is the answer in english but how the heck do I do it.
".....If you use formulas to calculate the percentage column, return a
blank instead of a zero to have it ignored in the average...."


Normally when doing this kind of thing I would compute the cells you are
averaging conditionally:

=IF( expression 0, expression, " ")

That will fill any zero results with a blank cell rather than a zero.


Agreed.

Also, if the formulae get complicated it is often worth adding one or
more intermediate columns with simpler formulae in them. Easier to
understand and debug. You can always hide them when it's working properly.