Forum Discussion
Solved
Calculating MODE Across Several Worksheets
Hello. I have data in 11 different cells across several worksheets. For example, I have 20 worksheets in a single workbook and those worksheets all have data in cells D3, D5, D7, D9, D11, D13, D15,...
=MODE(TOCOL(Sheet1:Sheet20!D3))where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
Personally I'd prefer to avoid such a set-up, as it requires the volatile INDIRECT. Can you not simply ensure that you have two dummy worksheets, named Start and End for example, which are blank and between which will sit the relevant worksheets? You can then use:
=MODE(TOCOL(Start:End!D3))
Failing that, you could use:
=MODE(N(INDIRECT("'"&All_Worksheets&"'!"&CELL("address",D3))))
I really like the TOCOL function and will do the dummy worksheets after I double-check that the blank worksheets do not impact my stats. Thanks again!