**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)))`

well done! that is a good calculation

Hello,

I have two separate spreadsheets, the first lists a number of people, showing their payroll number and name, the second shows a report listing all persons paid the current financial year and the week they’ve been paid eg., Joe Smith, 10110, (there is a separate row entry for each week paid showing week number eg., 11, 12, 15, 21, 28), on the first sheet I have a separate column that needs to show which was the last week they were paid, in this case, week 28.

Please let me know if I need to send screen shots as examples.

Thanks Tom, exactly what I was looking for!

Thanks, Artur!

I have a column containing 30000+ entries. It has numbers. Both positive values and negative ones. Eg.

-12

6

6

These average to 0.

So I want to highlight these.

Another Eg.

12

-12

Since result is 0,i want it to be highlighted..

Please suggest a formula in this regard.

I loved the calculations, incredible your tips and information.

Thank you!

Thank’s a lot

It is well explained, Thank you.