Average If

[Steph]

Hi. Is there a way to create a formula that would essentially be an
averageif, similar to a sumif? Thanks!

 

[Guest]

=SUMIF / COUNT IF

or

=AVERAGE(IF(...)) entered as an array formula.

Dave

 

[Pete_UK]

You can set up an array formula to do this. Essentially, it would take
the form:

=AVERAGE(IF((condition_1)*(condition_2)*(condition_3),range_to_average))

Your conditions may be things like (A1:A10<>0), or (B1:B10>100) etc,
and your ranges should cover the same number of cells. The asterisks
act like AND to enable you to string a number of conditions together.

You need to commit the formula using CTRL-SHIFT-ENTER (CSE) instead of
the normal ENTER, and if you do this correctly then Excel will wrap the
formula within curly braces { } when viewed in the formula bar - do not
type these yourself.

Hope this helps.

Pete