3D References Mode() Function Excel
 

I am wondering if 3D references for the MODE function work? I tried to find
the mode of a bunch of numbers across multiple worksheets using this function
but keep getting a #REF! error.

3D References Mode() Function Excel
 

the-big-john wrote...
I am wondering if 3D references for the MODE function work? I tried to
find the mode of a bunch of numbers across multiple worksheets using
this function but keep getting a #REF! error.

MODE is the sole aggregation function (one that takes a variable
number of arguments each of which in turn could refer to thousands of
individual values and returns a single value) that doesn't accept 3D
references. You'll need to use work-arounds. If you don't have that
many worksheets, brute force would be best, e.g., replace

=MODE(Sheet1:Sheet4!A1:A100)

with

=MODE(Sheet1!A1:A100,Sheet2!A1:A100,Sheet3!A1:A100 ,Sheet4!A1:A100)

Otherwise, it gets complicated quickly and requires using a list of
worksheet names.

=MODE(N(OFFSET(INDIRECT({"Sheet1","Sheet2","Sheet3 ","Sheet4"}&
"!A1:A100"),ROW(1:100)-1,0,1,1)))

Another alternative would be downloading and installing Laurent
Longre's MOREFUNC.XLL add-in, from http://xcell05.free.fr/english/,
and using its THREED function.

=MODE(THREED(Sheet1:Sheet4!A1:A100))