You have sales report covering sales for products A, B, C and D. We would like to calculate the average sales per product. This is something I have blogged earlier.

We can calculate the average sales for the product A by the sentence =AVERAGE(IF($B$3:$B$18=F3;$C$3:$C$18)).

After AVERAGE we have IF function, then we define that average C3:C18 is calculated when B3:B18 value is equal to A. As said, this is something I have blogged earlier.

Instead of AVERAGE and IF, we can nowadays use AVERAGEIF.

AVERAGEIF consists of three arguments:

• Range: criteria range, where is the criteria not values to be calculated.
• Criteria: what is the condition in the criteria range, that fulfills the criteria.
• Average_range: range with values to be calculated.

The sentence in G9 is =AVERAGEIF($B$3:$B$18;F9;$C$3:$C$18).

We need to define that product range B3:B18 is the range, where we are looking for F9 value. Then real values to be calculated are in C3:C18 range.

AVERAGEIF is bit easier and faster than AVERAGE and IF.

For multiple criterias, also a function AVERAGEIFS exists. We don’t need that for our case, but it is possible just to use one criteria.

The AVERAGEIFS differs from the AVERAGEIF, that first argument is the average_range, then criteria_range and criteria. The arguments are in different order.

The data was loaded into Access.

The average for product A can be calculated with SQL:

SELECT

    product,

    Avg(sales)

FROM

    AIF

WHERE

    product = ‘A’

GROUP BY

    product;

The result for the average sales for product A is the same as in Excel. It is not handy if we need to query one value at a time.

In Access you can also count all the averages at one go, that is comparable to Excel.

The SQL query is here:

SELECT

    AVG(IIF(product = ‘A’, sales, NULL)) AS A,

    AVG(IIF(product = ‘B’, sales, NULL)) AS B,

    AVG(IIF(product = ‘C’, sales, NULL)) AS C,

    AVG(IIF(product = ‘D’, sales, NULL)) AS D

FROM

    AIF;

AVG counts the average in Access SQL. We need to set IIF condition, with two I letters, if the product is A, then count average from sales column, if the product is not A, then have NULL value. Alias for this sentence is A. Then sentence is repeated for all the products.

Please, note that Access does not support CASE – WHEN – THEN – ELSE – END conditions.  In another SQL dialect, the same thing could have been done with above mentioned CASE condition.

Averages per product can be calculated with AVERAGE and IF already for some time ago. More recent functions AVERAGEIF and AVERAGEIFS make the process faster and easier. Also Access can crunch numbers, even though it is a database. SQL is a versatile tool for many purposes.