Statistical formulas across sheets?

[Don Wiss]

When you look up summing across sheets you find examples like
=SUM(Sheet1:Sheet4!A12:A40000). But this has the disadvantage of the sheets
having to be contiguous. Also works is the construct:
=MIN(Sheet1!A12:A40000, Sheet2!A12:A40000). This is fine, but then this
construct breaks down if you want to use a function with more than one
argument, like PERCENTILE. It sees the comma separating the ranges and
errors saying you have entered too many arguments. Is there another
construct that allows for non-contiguous sheets and multiple arguments?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[Tom Ogilvy]

Only a select few functions support 3D references:

You can use 3-D references to refer to cells on other sheets, to define
names, and to create formulas by using the following functions: SUM,
AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV,
STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.