**Tom’s Tutorials For Excel: Averaging Positive and Negative Numbers With Multiple Criteria**

When crunching numbers, there are many ways to slice and dice the Average onion, depending on what criteria you want to include, exclude, combine, or isolate.

The picture shows a list of positive and negative numbers, and an analysis calculation table with 12 formula examples of varying multiple criteria. Notice the subtle syntax differences in formulas between the `AVERAGEIF`

function, and the `AVERAGE`

function with nested `IF`

and `ABS`

arguments.

Some of these formulas are arrays, which I’ve noted below. An array formula is applied to a cell by simultaneously pressing the `Ctrl+Shift+Enter`

keys, not just `Enter`

. The curly braces are not typed by you; Excel places them automatically when the array formula is properly applied. If you are unfamiliar with array formulas, see __my video and explanation of arrays here__.

Calculating “Average yardage result of all plays”:

The formula in cell F13 is `=AVERAGE(C5:C24)`

Calculating “Average yardage change all plays (as if all positive)”:

The array formula in cell F14 is `=AVERAGE(ABS(C5:C24))`

Calculating “Average of positive plays (only for yards gained)”:

The formula in cell F15 is `=AVERAGEIF(C5:C24,">0")`

Calculating “Average of negative plays (only for yards lost)”:

The formula in cell F16 is `=AVERAGEIF(C5:C24,"<0")`

Calculating "Average of all Run plays only":

The formula in cell F17 is `=AVERAGEIF(B5:B24,"=Run",C5:C24)`

Calculating "Average of all Pass plays only":

The formula in cell F18 is `=AVERAGEIF(B5:B24,"=Pass",C5:C24)`

Calculating "Average Run yardage change (as if all positive)":

The array formula in cell F19 is `=AVERAGE(IF(B5:B24="Run",ABS(C5:C24)))`

Calculating "Average Pass yardage change (as if all positive)":

The array formula in cell F20 is `=AVERAGE(IF(B5:B24="Pass",ABS(C5:C24)))`

Calculating "Average of positive Run plays (that gained yards)":

The array formula in cell F21 is `=AVERAGE(IF(B5:B24="Run",IF(C5:C24>0,C5:C24)))`

Calculating "Average of negative Run plays (that lost yards)":

The array formula in cell F22 is `=AVERAGE(IF(B5:B24="Run",IF(C5:C24<0,C5:C24)))`

Calculating "Average of positive Pass plays (that gained yards)":

The array formula in cell F23 is `=AVERAGE(IF(B5:B24="Pass",IF(C5:C24>0,C5:C24)))`

Calculating "Average of negative Pass plays (that lost yards)":

The array formula in cell F24 is `=AVERAGE(IF(B5:B24="Pass",IF(C5:C24<0,C5:C24)))`