Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
Mkae Mode function return "" instead of #N/A when there is no Mode | Excel Discussion (Misc queries) | |||
how do I end function mode in excel | Excel Discussion (Misc queries) | |||
MODE Function | Excel Worksheet Functions | |||
MODE function | Excel Worksheet Functions |