AVERAGE()

Arithmetic average of list items

Returns the computed average of the values in a list.

Sample usage

AVERAGE(Products[Price]) : The average of the all values in the Price column of the Products table. Equivalent to AVERAGE(SELECT(Products[Price], TRUE)). See also: SELECT()

AVERAGE([Discounts]) : The average of the all items in the Discounts column value, where Discounts is of type List for some numeric type.

AVERAGE(LIST(1, 2, 3)) : 2.00

AVERAGE(LIST(0) - LIST(0)) (a valid constructed list with a type but no items): 0.00

Average of select rows

Compute average product rating from customer feedback within the past month, excluding feedback with no rating:

AVERAGE(
  SELECT(
    Feedback[Product Rating],
    AND(
      ([Product Rating] > 0),
      ([Submitted] >= (EOMONTH(TODAY(), -2) + DAY(TODAY())))
    )
  )
)
  • SELECT(Feedback[Product Rating], ...) gets a list of Product Rating values from select rows of the Feedback table.
  • AND(..., ...) limits the SELECT() results to only those rows that match all of the conditions.
  • ([Product Rating] > 0) limits the selection to only rows with a Product Rating value greater than zero, where 0 is the default value and indicates "no rating".
  • ([Submitted] >= ...) further limits the rows to those with a submission date on or after the computed date.
  • (EOMONTH(TODAY(), -2) + DAY(TODAY())) computes the date one month prior to today.
  • AVERAGE(...) computes the average of the selected values.

See also: AND(), DAY(), EOMONTH(), SELECT(), TODAY()

Common problems

AVERAGE(1, 2, 3) : the arguments are not in list form. To fix, wrap them in LIST() to construct a list: AVERAGE(LIST(1, 2, 3)).

Syntax

AVERAGE(list)

  • list - List of any numeric type.

See Also

COUNT()

MAX()

MIN()

STDEVP()

SUM()

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false