[mischanix]

I personally would prefer a spreadsheet that treated null as the identity for whatever function it was acting as an input to (0 for addition, 1 for multiplication, e for exponentiation, Identity matrix for matrix multiplication, etc.), only throwing an error when a null does not resolve to a single value (i.e. the same cell is used for both addition and multiplication). But that would apparently require me to smoke some stuff.

[cousin_it]

Actually, I wouldn't even throw an error. It's quite intuitive for the user when "sum" and "product" apply only to non-empty cells, and empty cells are ignored. If all cells are empty, the return value should be the identity of the function, i.e. 0 for sum and 1 for product. Also it's nice to have a function "count" that counts non-empty cells, with identity value 0.

    A1 = 1, A2 = blank, A3 = 3
    sum(A1:A3) = 4
    product(A1:A3) = 3
    count(A1:A3) = 2
    average(A1:A3) = sum(A1:A3)/count(A1:A3) = 2
    geometric_mean(A1:A3) = product(A1:A3)**(1/count(A1:A3)) = sqrt(3)

Note that the last line wouldn't work under your proposal, because the identity for "product" isn't the same as the identity for "count". I think the user would be massively confused by an error like "using conflicting identity values for cell A2", and would prefer a spreadsheet that just gave them the damn geometric mean.

[mcguire]

But what is the geometric mean of 2, 3, 4 and potato?

[cousin_it]

An error.