On 06/05/2015 22:20, ss wrote:
On 06/05/2015 22:01, Jim Newman 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.



If you change the formula to
=AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18,"0")
it'll disregard the cells containing zero

NB the format is different to AVERAGEIF because the "average range" is
the first parameter, followed by pairs of "condition_range","condition"
parameters.

It looks like you may get 'divide by zero' errors, but you can use an
IFERROR condition to control that
=IFERROR(AVERAGEIFS(C18:AL18,C3:AL3,"%",C18:AL18," 0"),"")

Thats it! spot on, problem solve.

Thank you all

Happy to help
The only suggestion I would make is that you amend it to 'fix' the check
against row 3, by using
=IFERROR(AVERAGEIFS(C18:AL18,$C$3:$AL$3,"%",C18:AL 18,"0"),"")
as then you could set it for the first row, and then drag down to
complete the rest of the column.