OFFSET

Why it works

Anchoring on the header means the line number and the number of rows to move are the same thing: line 1 is one row below the header. OFFSET only counts; it never checks which account is in the row, so the layout of the list is what makes the answer right.

More to watch out for

• The client sends a new version with a total line inserted between the header and account 1100. The accounts shift down while the anchor stays at C1, so every line number now returns the account before it. INDEX(C2:C7, E2) moves its range along with the data.

Why it works

rows decides where the window starts and height decides how long it is, so moving the last period moves the whole window without changing its size. OFFSET hands SUM a real range, which is why the unbooked period is skipped instead of breaking the total.

More to watch out for

• The unbooked period counts as one of the four, so this total covers three periods of payroll. The difference with the loonjournaal is then a missing booking, not an error in the formula.

Variations

=SUM(INDEX(B2:B9, E1 - 3):INDEX(B2:B9, E1))

=SUM(INDEX(B2:B9; E1 - 3):INDEX(B2:B9; E1))

Why it works

COUNT turns 'how far has the list grown?' into a number, and OFFSET turns that number into a position. Because the balances are entered top to bottom without gaps, the count of numbers equals the row of the last balance, which is the assumption the whole formula rests on.

More to watch out for

• A balance pasted from the client's export as text, for example 226900 with a trailing space, is not counted by COUNT and not averaged. The window shifts and the average changes without an error.
• With fewer than 3 balances, rows drops to 0 or below and the window includes the header or falls off the sheet.

Variations

=AVERAGE(INDEX(B2:B9, COUNT(B2:B9) - 2):INDEX(B2:B9, COUNT(B2:B9)))

=AVERAGE(INDEX(B2:B9; COUNT(B2:B9) - 2):INDEX(B2:B9; COUNT(B2:B9)))

=AVERAGE(TAKE(FILTER(B2:B9, B2:B9 <> ""), -3))

=AVERAGE(TAKE(FILTER(B2:B9; B2:B9 <> ""); -3))

Why it works

Anchoring on A1, the corner of the grid, makes both MATCH positions usable as offsets without any +1 or -1 corrections; only the window start needs the + 1. MIN caps the window at the periods that exist, an edge case OFFSET would otherwise turn into #REF! or, with a window one period too long, into a silent sum that includes the account column.

Do not use this construction in a large model with many such cells: OFFSET is volatile and recalculates after every edit in the workbook.

The alternative is SUM(INDEX(B2:F5, accRow, perCol - span + 1):INDEX(B2:F5, accRow, perCol)), which gives the same 56200 and recalculates only when its inputs change, at the cost of a longer formula.

More to watch out for

• Dropping the + 1 shifts the window one period back, to January through March: 18500 + 18500 + 18500 = 55500. Test with a window of 1 period, which must return the end period alone.
• A window of 0 periods makes span 0, and OFFSET with width 0 returns #REF!. Validate I3 with data validation if people type it by hand.

Variations

=SUM(OFFSET(A1, MATCH(I1, A2:A5, 0), MATCH(I2, B1:F1, 0) - I3 + 1, 1, I3))

=SUM(OFFSET(A1; MATCH(I1; A2:A5; 0); MATCH(I2; B1:F1; 0) - I3 + 1; 1; I3))

=LET(
  accRow, MATCH(I1, A2:A5, 0),
  perCol, MATCH(I2, B1:F1, 0),
  span, MIN(I3, perCol),
  SUM(INDEX(B2:F5, accRow, perCol - span + 1):INDEX(B2:F5, accRow, perCol))
)

=LET(
  accRow; MATCH(I1; A2:A5; 0);
  perCol; MATCH(I2; B1:F1; 0);
  span; MIN(I3; perCol);
  SUM(INDEX(B2:F5; accRow; perCol - span + 1):INDEX(B2:F5; accRow; perCol))
)