AverageIf

[Noz2k]

Need a little help putting a clause into my averageif statement.

The problem I'm encountering is that some cells in my average range have "-" in them rather than a number. What I would like to do is to not include these in my average.

The current formula is

=AVERAGEIF(Sheet1!$Q$2:$Q$2083,"="&$B45,Sheet1!$V$2:$V$2083)

where B45 = 0

 

[Steve=True]

I tried your formula on a small range and the "-" didn't affect it.

Steve=True
www.exceldashboardtemplates.com

 

[Scott Huish]

BTW, the "="& is unnecessary, if not specified, equal is the default.

 

[Noz2k]

Thanks for the replies.

The reason it didn't work initially was because was because there weren't any occurances of 0 in the data.

It's good to know that the "-" is not counted though, as it means I can sort the table in other ways without affecting the data.

and I wasn't aware that was the default, thanks for letting me know.