Implement AGGREGATE function

[Jython1415]

Overview

Implement the AGGREGATE function for the named-functions library.

Description

Returns an aggregate in a list or database using one of 19 different functions with special handling for errors, hidden rows, and nested subtotals.

Value Proposition

• Very powerful for real-world data processing
• Can ignore errors that would break regular aggregation functions
• Can ignore hidden rows (useful for filtered data)
• Can ignore nested SUBTOTAL and AGGREGATE functions
• Single function interface for 19 different aggregation operations

Priority

HIGH

Category

Aggregation & Calculation

Implementation Notes

• Support function numbers 1-19 (AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, etc.)
• Options parameter: 0-7 for different ignore behaviors
• Complex function - will be larger implementation
• Consider breaking into helper functions for each aggregate type
• Error handling is critical feature
• Reference: Excel AGGREGATE function documentation

Related

Part of Excel function implementation initiative (#51)

[Jython1415]

Recommendation: Close or Defer

Fundamental Blocker

AGGREGATE has a critical limitation in Google Sheets: cannot detect hidden rows.

• Excel's AGGREGATE uses option parameter to selectively ignore hidden rows
• Example: =AGGREGATE(9, 5, A1:A10) means "SUM, but ignore hidden rows"
• Google Sheets has no formula-level way to detect if a row is hidden

What This Means

• Can implement ~60% of AGGREGATE functionality (19 different operations)
• Cannot implement the hidden row detection feature (a core value proposition)
• Estimated effort: 30-40 hours for incomplete implementation

Alternatives

• Users can achieve similar results with FILTER() + explicit criteria
• Google Sheets users typically use explicit filtering vs relying on "hidden state"

Recommendation

Close as "Won't Implement" or defer indefinitely. The effort-to-value ratio is poor given the platform limitation.

If partial implementation is desired, consider breaking into smaller utility functions for specific aggregation operations rather than trying to replicate full AGGREGATE behavior.