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.