AVERAGE
AVERAGE(list)
Gives the mean of a list of numbers, durations or dates, such as the average duration of the tasks in a phase. Blanks are left out of the count rather than treated as 0, and a list with no values is blank, not 0.
Arguments
| Argument | What to give it |
|---|---|
| list | A column read from a set of rows, like SAME([Phase])[Duration], or several single values. Numbers, durations or dates. It also takes one level over time (see below). |
Examples
Formula=AVERAGE(SAME([Phase])[Duration])
The average duration of the tasks in this task's phase. Set it beside [Duration] to spot the outliers.
=AVERAGE(FILTER(All, All[Phase] = "Civil")[Duration])
The average duration of the civil tasks, the same on every row.
Formula on WBS rows=AVERAGE(Tasks[% complete])
On a WBS row: the plain mean of its tasks' % complete. Every task counts the same, however long it is.
Formula=AVERAGE(HISTORY(INDAYS([Total float])))
The task's total float in days, averaged over the updates. A level over time is averaged over the task's working time from its first point to its last, so a value that held for longer weighs more.
Reach for something else when
| You want | Use |
|---|---|
| The middle value, not thrown off by a few long tasks | MEDIAN |
| The total, not the mean | SUM |
| A mean weighted by duration | SUM(…) / SUM(…): see SUM |
| The value a share of the list falls below | PERCENTILE |
Common questions
Is there an AVERAGEIF?
No. Filter the rows first, then average the column: AVERAGE(FILTER(All, All[Phase] = "Civil")[Duration]) is Excel's AVERAGEIF.
Why is AVERAGE blank?
There were no values: the set was empty, or the column was blank on every row of it. Total float, for one, is blank once a task is complete, so complete tasks drop out of an average of float.
Can I average text?
No: "AVERAGE can't use a list of Text".
The editor refuses this=AVERAGE(Predecessors[Phase])
To count the different values, use COUNTDISTINCT.
Can I average an amount over time?
No. An amount, such as hours spread over the task, has no mean at a moment; the editor says "An amount over time has no mean: add it up with TOTAL(…), or divide it by another amount for a rate (a level over time) and take its AVERAGE". See amounts and levels.
The editor refuses this=AVERAGE(SPREAD(INHOURS([Duration]), [Start date], [Finish date]))