Forum Discussion

RShaw1972's avatar
RShaw1972
Copper Contributor
Sep 21, 2023
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,...
excel
Formulas and Functions
  • JosWoolley's avatar
    JosWoolley
    Sep 21, 2023

    RShaw1972 

     

    =MODE(TOCOL(Sheet1:Sheet20!D3))

     

    where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.

     

    Regards

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 21, 2023

RShaw1972 

Given a 3D range 'data', HSTACK will convert sheet data to columns.  Modeλ is then applied by row.  Finally intervening lines that give rise to errors are filtered out.

= LET(
    Modeλ,        LAMBDA(x, MODE(x)),
    consolidated, HSTACK(data),
    modes,        BYROW(consolidated, Modeλ),
    FILTER(modes, ISNUMBER(modes))
  )

Resources