AVERAGE

You are doing an analytical review at Veldhuis Installatie BV. The ledger shows account 4000 Personeelskosten for periods 1 to 6 of 2025. You want the average per month as a first expectation, to compare later with the payroll summary (loonjournaal).

Formula in D2

=AVERAGE(B2:B7)

=AVERAGE(B2:B7)

Result: €49,000.00€ 49.000,00

Watch out: Period 5 is 2,3502.350 above the average of 49,00049.000, but the average alone does not tell you whether that is a deviation worth investigating. It is an expectation, not a threshold; set the threshold from your performance materiality.

For journal entry testing you count the manual entries (journal MEM) the client posted per period. In period 3 there were genuinely 0. The export for period 5 has not been delivered yet, so that cell is blank.

You want the average per period, rounded to one decimal for your working paper.

Formula in E1

=ROUND(AVERAGE(B2:B8), 1)

=ROUND(AVERAGE(B2:B8); 1)

Result: 10.810,8

Watch out: If someone types a dash or 'n/a' in period 5, AVERAGE skips it like a blank, which is harmless here. If period 7 arrives as the text '12', it is skipped too and the result silently becomes 53 / 5 = 10.6.

=ROUND(AVERAGEIF(B2:B8, "<>0"), 1)

=ROUND(AVERAGEIF(B2:B8; "<>0"); 1)

From the XAF auditfile you have one row per entry with entry number, journal, period and amount. For your interim work you want the average amount of the entries in the journal you pick (here MEM) up to and including the period you pick.

You reuse the same two selector cells for other journals, and a journal without entries should show a message, not an error.

Formula in G3

=IFERROR(AVERAGEIFS(D2:D9, B2:B9, G1, C2:C9, "<="&G2), "No entries")

=IFERROR(AVERAGEIFS(D2:D9; B2:B9; G1; C2:C9; "<="&G2); "No entries")

Result: €5,100.00€ 5.100,00

Watch out: IFERROR also hides a broken formula: if the amount column is one row shorter than the others, AVERAGEIFS returns #VALUE! and the sheet shows No entries, which is false. Test the formula without IFERROR first.

=AVERAGE(FILTER(D2:D9, (B2:B9 = G1) * (C2:C9 <= G2)))

=AVERAGE(FILTER(D2:D9; (B2:B9 = G1) * (C2:C9 <= G2)))

The client's fixed asset register (activastaat) gives per category the acquisition cost and the depreciation rate. For your analytical review of depreciation you want one average rate for all assets together, to multiply with total cost as an expectation.

The categories differ a lot in size, so averaging the rates would let IT equipment count as much as buildings.

Formula in E2

=LET(
  cost, B2:B6,
  rate, C2:C6,
  total, SUM(cost),
  IF(total = 0, 0, ROUND(SUMPRODUCT(cost, rate) / total, 4))
)

=LET(
  cost; B2:B6;
  rate; C2:C6;
  total; SUM(cost);
  IF(total = 0; 0; ROUND(SUMPRODUCT(cost; rate) / total; 4))
)

Result: 7.46%7,46%

Watch out: The mirror-image mistake is AVERAGE(C2:C6), 15.5%: a plausible rate that is more than double the weighted rate of 7.46%, and your expected depreciation would be too high by about 161,500161.500 (15.5% of 2,010,0002.010.000 is 311,550311.550 against 150,000150.000).

=SUMPRODUCT(B2:B6, C2:C6) / SUM(B2:B6)

=SUMPRODUCT(B2:B6; C2:C6) / SUM(B2:B6)