You have a sales report. Each sales rep from A to J have sold a number of products except D and H.

We should count the average sales.

The standard average formula counts only numbers. For example, sales reps D and H are not included. Sum of numbers is 447. If you divide that with eight, that is roughly 56.

When sales reps D and H are on the payroll, they need to be included in the average counting. 56 is too high average as two sales reps have zero sales.

Data includes NULL values.

The more realistic average is around 45.

To make denominator as ten, we have few options available.

We can count cell with value in B1:B10 and then add cells without value B1:B10 to include all the cells. Also ROWS function counts rows in data range.

We count the sum in B1:B10 and then we divide by COUNT + COUNTBLANK or by ROWS -function. Then we have the wanted 45 as a result.

Another option is to populate NULL values with zero numbers, then standard average can be used.

Activate the B1:B10 range and execute the macro below.

Sub z_zero()

Dim cell As Range

For Each cell In Selection

If cell.Value = vbNullString Then

    cell.Value = 0

End If

Next cell

End Sub

NULL values are in Excel VBA called as vbNullString. If the macro finds NULL value in the range, then that is replaced by zero. If the value is zero, then it is NULL.

Activate the values, then execute the macro. Now NULL values have been replaced by zero, and standard AVERAGE behaves as we want.

We can count average in Access too.

The same values are in Access.

A query was created.

The SQL is:

SELECT Avg(IIf(IsNull(sal),0,sal)) AS Expr1

FROM tab;

The result is the same as in Excel.

We have nested formulas AVG, IIF and ISNULL. In other SQL dialects same thing can be done with AVG and IFNULL.

SQL sentence is short compared to do same in Excel. Excel is a better tool number crunching, but if you can do something with SQL, Access is worth trying out.