AGGREGATE Options and Nested Gates in HotXLS for Delphi

Four cells are enough to make =AGGREGATE(9,7,A1:A4) come back as 20 in a hand-rolled evaluator while Excel shows 40. A1 holds 10, A2 holds 20 on a hidden row, A3 holds =1/0, and A4 holds =SUBTOTAL(9,A1:A2), a group subtotal worth 30. Option 7 means skip hidden rows, skip errors, and count nested subtotals as values, so Excel adds 10 and 30. The 20 appears when the outer call's "skip hidden rows" setting is still switched on while A4 computes its own value, which shrinks A4 to 10. Getting 40 takes two things: decoding the three option bits in the right positions, and making sure a referenced formula never sees the options of the formula that referenced it

What do the AGGREGATE options 0 through 7 actually select?

The options argument is a three-bit matrix, and the bits are independent of each other. Bit 0 (value 1) skips hidden rows, bit 1 (value 2) skips error values, and bit 2 (value 4) stops skipping cells whose own formula is a SUBTOTAL or AGGREGATE, because skipping them is the default for the low codes. Two parts of that are easy to read backwards. The hidden-row bit is the low bit, so AGGREGATE(9,1,...) is the filtered total and AGGREGATE(9,2,...) is the error-tolerant one. And the nested policy runs the opposite way from the other two: only codes 4 through 7 treat a nested aggregation as an ordinary number. ECMA-376 Part 1 §18.17.7 defines SUBTOTAL with the same include-or-exclude hidden-row split across codes 1–11 and 101–111; AGGREGATE, stored in OOXML under the _xlfn. prefix, folds that split into its options argument, and the table Microsoft publishes for the function is the contract an engine has to meet

Option	Hidden rows	Error values	Nested SUBTOTAL / AGGREGATE
0	included	propagated	ignored
1	ignored	propagated	ignored
2	included	ignored	ignored
3	ignored	ignored	ignored
4	included	propagated	included
5	ignored	propagated	included
6	included	ignored	included
7	ignored	ignored	included

Any code outside 0 to 7 is rejected with #VALUE!, which is also what Excel does

Why single-bit codes are where decodes go wrong

A decode written from a paraphrase of the table instead of the table itself tends to look like this: ignore errors for codes 4 and up, skip hidden rows for 2, 3, 6 and 7, and leave the nested policy out entirely. That version can survive a long time in production. The combination most people actually use, hidden rows plus errors, lands on codes 3 and 7 under both readings, so the swap only shows on a single-bit code. AGGREGATE(9,1,A1:A4) then returns the unfiltered sum, and AGGREGATE(9,2,...) skips hidden rows while still propagating #DIV/0!. Real workbooks rarely use codes 1 and 2 on their own, which is exactly why a review against the published table catches more than waiting for a customer file does

HotXLS decodes the options as three set-membership tests, one per column of the table, and assigns all three results when the AGGREGATE call starts, restoring whatever was in effect before when it ends. The assignment is unconditional on purpose. A decode written as "if the option asks for it, switch the flag on" lets an AGGREGATE with code 4 that sits inside a SUBTOTAL(109, ...) inherit the outer hidden-row setting instead of clearing it. The sketch below shows the same rule for anyone writing or reviewing their own evaluator

// Illustrative decode for your own evaluator: TAggPolicy and
// DecodeAggregateOptions are names you define, not HotXLS API
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // anything else is #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // bit 2 clear
end;

The SUBTOTAL side of the same hidden-row rules, including how filtered rows and manually hidden rows compare, is covered in the article on SUBTOTAL and AGGREGATE hidden rows

Why does an outer AGGREGATE reach into the formulas it references?

Because the natural way to pass "skip hidden rows" down to six or seven cell-walking loops is a field on the calculator object, and one calculator evaluates every formula in a recalculation pass. That design holds as long as everything that runs while the field is set belongs to the aggregation that set it. It stops holding at one specific moment: the cell fetch. When the walker asks for a cell value and that cell holds a formula with no cached result yet, the workbook compiles and evaluates that formula on the spot, on the same calculator, with the outer settings still in place

Walk the opening example through such an engine. AGGREGATE with code 7 switches on hidden-row skipping and starts at A1. A1 gives 10, A2 is hidden and skipped, A3 is an error and skipped. Then the walker reaches A4, which has not been calculated. A4's SUBTOTAL(9,A1:A2) runs, and a SUBTOTAL implementation that only switches hidden-row skipping on for codes 101 through 111 (a perfectly reasonable way to write one) never switches it off for code 9. It finds the setting already on, skips A2, and returns 10. The outer total becomes 10 + 10 = 20. Nothing in A4's formula mentions hidden rows

The nested-aggregate setting leaks in both directions. With codes 0 through 3 it is on, and a generic range walker that honours it would make a precedent like =SUM(B1:B3) drop B2 whenever B2 happens to contain a SUBTOTAL. In the other direction, a SUBTOTAL that turns nested skipping off when it finishes, rather than restoring the earlier state, disables the outer AGGREGATE's policy for the rest of its walk. Any evaluator that uses a transient field to carry context into a recursive walk has this shape; the field is correct for the frame that set it and wrong for every frame that inherits it

Cutting the policy at the fetch boundary

HotXLS puts a boundary around every point where AGGREGATE reads a value it did not compute itself. Before the fetch, both settings are saved and cleared; after it, they are restored in a finally block. The outer aggregation still applies its own policy to the cell it just fetched, because the hidden-row and nested-cell tests happen in the walker around the fetch. The referenced formula runs with no policy at all, which matches Excel. All three walker families go through that boundary: the SUM family (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), the closed-form reductions (PRODUCT, STDEV, VAR), and the forms that take a k argument (MEDIAN, LARGE, SMALL, the percentiles and quartiles)

// Illustrative walker for your own evaluator. FPolicy, FetchCell,
// IsRowHidden and IsSubtotalCell are members you define yourself
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // the referenced formula owns its own policy
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// inside the range walk: the outer policy is applied around the fetch
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... apply the error policy to Code / V, then accumulate ...
  end;
end;

Arguments that are not plain ranges need more than cleared flags. An argument such as A1:A4/(B1:B4-20) is a computed array, and its element shape has to survive so that option 6 can drop just the elements that divided by zero. HotXLS turns each referenced range into a two-dimensional array through the same boundary, stores a cell that failed as an error element instead of aborting, and applies the arithmetic and comparison operators element by element. A range that spans several sheets, or one whose corners are inverted, returns #VALUE!. A range too large for TXLSCalculator.FormulaArrayMemoryLimit (64 MB by default) returns a resource-limit result, and that result is deliberately outside the set of errors that options 2, 3, 6 and 7 may skip. Swallowing an engine's own out-of-memory signal because the formula asked to ignore #N/A would produce a confident, wrong number, so the formula fails instead

Which error does AGGREGATE return when it does not ignore errors?

The first one it meets, unchanged. Collapsing every error into #VALUE! is the easy shortcut, and it makes AGGREGATE(9,4,A1:A3) over a #DIV/0! cell disagree with Excel, which passes #DIV/0! through. HotXLS maps both genuine error variants and the seven error strings (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) back to their own codes, and each walker returns the first code it records. A cell whose formula was never calculated delivers its error as a return code from the fetch rather than as a cached value; it propagates exactly like a cached one

Two counting functions follow SUBTOTAL rather than SUM. For function 2, COUNT, an error cell is never counted and never propagated, whatever the options code, because COUNT only counts numbers. For function 3, COUNTA, an error cell is a non-empty value and counts as 1 unless the options code skips errors. That asymmetry is how Excel treats COUNT and COUNTA outside AGGREGATE too, and a blanket "if error then propagate" rule gets both of them wrong

Checking all eight codes on one worksheet

The opening four cells make a compact check across every code. Evaluate the SUM form and the MEDIAN form over A1:A4 for each code from 0 to 7 and compare against values worked out by hand. Codes 0, 1, 4 and 5 must propagate the #DIV/0! from A3, since none of them skips errors. Code 2 gives SUM 30 and MEDIAN 15, from 10 and 20 with the nested A4 skipped. Code 3 gives 10 and 10. Code 6 gives 60 and 20, because the 30 in A4 now counts. Code 7 gives 40 and 20, and it is the one an engine without the fetch boundary reports as 20. The check is only meaningful if A4 is uncached when the AGGREGATE runs, so evaluate each case once with precedents already calculated and once without

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';    // group subtotal = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  hidden skipped, error propagates
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       hidden, error and nested skipped
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       only errors skipped
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 means a setting leaked into A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN of 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Opening the saved file in Excel and pressing Ctrl+Alt+F9 gives a second opinion on the same cells. For a broader sweep, run every function number from 1 to 19 against all eight codes

Wiring the callbacks when you build a calculator yourself

Both settings depend on two callbacks. TXLSCalculator.Create takes a TXLSIsRowHidden and a TXLSIsSubtotalCell after the value getter, and both default to nil. With either one missing, the matching column of the table never applies, so every options code includes hidden rows, nested aggregations, or both. The XLSX and classic XLS workbook objects pass their own implementations in their constructors; code that creates a TXLSCalculator by hand over its own data source has to supply both. The workbooks recognise a nested aggregation by a formula that starts with SUBTOTAL(, AGGREGATE( or _xlfn.AGGREGATE(, with or without the leading equals sign, and a custom source should use the same rule

// TMySheetSource and FormulaAt are your own types; the three method
// signatures match TXLSGetValue, TXLSIsRowHidden and TXLSIsSubtotalCell
// (SheetIndex 1-based, Row and Col zero-based as the workbooks pass them)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern is the sheet-name table your host already keeps
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

The prefix rule has a consequence for generated workbooks. A generator that writes =ROUND(SUBTOTAL(9,B2:B9),2) produces a cell the prefix test does not recognise, so codes 0 through 3 count it and the group lands in the grand total twice. Keep the SUBTOTAL or AGGREGATE call outermost and put the rounding in the number format. A full Recalculate evaluates precedents before dependents, so aggregations normally read cached values, and the same ordering holds under incremental recalculation over the dependency graph. When a total still looks wrong and the formula text looks right, tracing the evaluation step by step shows whether a precedent ran on demand inside the walk or whether a callback was never attached

The option decoder, the fetch boundary and the callback hooks described here come with the HotXLS Delphi spreadsheet component, which reads, writes and recalculates XLS, XLSX and ODS workbooks from Delphi and C++Builder code with no Excel installation on the machine