5. Averageif and Averageifs


a) =IFERROR(2.ROUND(1.AVERAGEIF([CODE];"="&[@CODE];[Classification]);1);"");


b) =IFERROR(2.ROUND(1.AVERAGEIF([CODE];"="&[@CODE];[Classification]);1);"");


  1.  Average of column “Classification” if column “Code” is equal to the value in cell @code (for instance, O24);

  2.  Round the above result to one decimal place.


    These formulas are a nested IFERROR, ROUND, and AVERAGEIF functions that calculates the average of the values in the column Classification of the table where the value in the column CODE is equal to the value in the current row of the column CODE.

    The result is rounded to one decimal place. If there are no such cells, it returns an empty string.


    Here’s how the formula works in formula a) :

  1.  [@CODE] returns the value in the current row of the column CODE.

  2.  "="&[@CODE] concatenates the value in the current row of the column CODE with the = operator.

  3.  AVERAGEIF([CODE];...;[Classification]) returns the average of the values in the column Classification of the table where the value in the column CODE is equal to the value in the current row of the column CODE.

  4.  ROUND(...;1) rounds the result of step 3 to one decimal place.

  5.  IFERROR(...;"") handles errors that may occur in the ROUND and AVERAGEIF functions. If the ROUND and AVERAGEIF functions return an error, the IFERROR function returns an empty string.





    Here’s how the formula works in formula b) :

  1.  [@CODE] returns the value in the current row of the column CODE.

  2.  "="&[@CODE] concatenates the value in the current row of the column CODE with the = operator.

  3.  AVERAGEIFS([Classification];[CODE];"="&[@CODE]) returns the average of the values in the column Classification of the table where the value in the column CODE is equal to the value in the current row of the column CODE.

  4.  ROUND(...;1) rounds the result of step 3 to one decimal place.

  5.  IFERROR(...;"") handles errors that may occur in the ROUND and AVERAGEIFS functions. If the ROUND and AVERAGEIFS functions return an error, the IFERROR function returns an empty string.

AVERAGEIF

Averageif - Syntax

AVERAGEIF (range, criteria, average_range)

"Averageif" can be used with only one condition

At our table:

"Average by article" or "Average"
1,16
AVERAGEIFS

Averageifs - Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

"Averageifs" can be used with several conditions. In this case was just one.

At our table:

"Average by article" or "Average"
1,16
ROUND

Round - Syntax

ROUND(number, num_digits)

It rounds the result to one decimal place.

At our table:

"Average by article" or "Average"
1